JSON in PostgreSQL

Draft

jsonb lets PostgreSQL store structured documents while still querying fields inside them. It is useful, but it is still data inside a table.

Extract fields

The ->> operator extracts a JSON value as text.

SELECT email, profile ->> 'plan' AS plan
FROM accounts
ORDER BY email;

Sort  (cost=61.98..64.11 rows=850 width=64)
  Sort Key: email
  ->  Seq Scan on accounts  (cost=0.00..20.62 rows=850 width=64)

JSON operators can reach nested values inside a jsonb column.

SELECT email
FROM accounts
WHERE profile -> 'settings' ->> 'email' = 'true';

email
ada@example.com

Seq Scan on accounts  (cost=0.00..24.88 rows=4 width=32)
  Filter: (((profile -> 'settings'::text) ->> 'email'::text) = 'true'::text)

The @> operator asks whether jsonb contains another jsonb document.

SELECT email
FROM accounts
WHERE profile @> '{"plan": "pro"}';

email
ada@example.com
linus@example.com

Seq Scan on accounts  (cost=0.00..20.62 rows=8 width=32)
  Filter: (profile @> '{"plan": "pro"}'::jsonb)