JSON in PostgreSQL
Draftjsonb 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.
Loaded Database:json-in-postgresql.database-init
SELECT email, profile ->> 'plan' AS plan
FROM accounts
ORDER BY email;Loading plan...Filter nested JSON
JSON operators can reach nested values inside a jsonb column.
Loaded Database:json-in-postgresql.database-init
SELECT email
FROM accounts
WHERE profile -> 'settings' ->> 'email' = 'true';Loading plan...Containment
The @> operator asks whether jsonb contains another jsonb document.
Loaded Database:json-in-postgresql.database-init
SELECT email
FROM accounts
WHERE profile @> '{"plan": "pro"}';Loading plan...