JSON and JSONB integration

JSON and JSONB Integration

PostgreSQL rewrote the rules of databases by combining the reassuring, rock-solid ACID nature of SQL with the dynamic, fluid flexibility typical of NoSQL. It did so by offering columns natively dedicated to storing JSON data.

Heads up: in Postgres it's almost always recommended to use JSONB. The "B" stands for Binary: JSON documents stored in these columns lose their purely textual formatting, but they gain the ability to be validated, queried with very high performance, manipulated, and even indexed with the classic B-tree or GIN INDEX.

Navigating JSON: the "Arrow" operator

Unfortunately the ecommerce database currently has no predefined pure JSON columns. We can, however, create temporary JSON values and "cast" them from text!

When working with JSON, the operators that drill into objects and arrays are -> (keeps the data in its pure jsonb/json form) or its "extended" version ->> (converts the encountered JSON data to text).

Academic example, as if a "metadata" JSON column existed:

-- Look up the value associated with the info key
SELECT metadata->'info' FROM users;

-- Drill into info, and grab city as text
SELECT metadata->'info'->>'city' FROM users;