JSON in SQL

PostgreSQL’s JSONB type stores parsed JSON documents that you can query, index, and update with SQL. The examples use an event log whose shape varies from row to row.

-- setup.sql
CREATE TABLE events (
    id      INT PRIMARY KEY,
    kind    TEXT NOT NULL,
    payload JSONB NOT NULL
);
INSERT INTO events VALUES
    (1, 'order', '{"user": {"name": "Ana", "tier": "gold"}, "qty": 9,  "tags": ["gift"]}'),
    (2, 'order', '{"user": {"name": "Ben"},                "qty": 10, "tags": []}'),
    (3, 'order', '{"user": {"name": "Cai", "tier": "gold"}, "qty": 2,  "tags": ["gift", "rush"]}'),
    (4, 'login', '{"user": {"name": "Ana"}, "device": "mobile"}');

$ psql -d sqldemo -f setup.sql
CREATE TABLE
INSERT 0 4

Extracting Values: -> and ->>

-> returns JSON; ->> returns text.

-- extract.sql
SELECT payload -> 'user'  AS user_json,
       payload -> 'user' ->> 'name' AS name_text,
       pg_typeof(payload -> 'qty')  AS arrow_type,
       pg_typeof(payload ->> 'qty') AS double_arrow_type
FROM events WHERE id = 1;

$ psql -d sqldemo -f extract.sql
            user_json            | name_text | arrow_type | double_arrow_type 
---------------------------------+-----------+------------+-------------------
 {"name": "Ana", "tier": "gold"} | Ana       | jsonb      | text
(1 row)

payload -> 'user' is still a JSON object, so you can keep going with another arrow. The last step uses ->> to get plain text. pg_typeof confirms the difference: the same qty is jsonb through -> and text through ->>.

The Text Comparison Trap

Because ->> returns text, comparisons on it are text comparisons.

-- text_compare_trap.sql
SELECT id, payload ->> 'qty' AS qty
FROM events WHERE payload ->> 'qty' > '5' ORDER BY id;
SELECT id, (payload ->> 'qty')::int AS qty
FROM events WHERE (payload ->> 'qty')::int > 5 ORDER BY id;

$ psql -d sqldemo -f text_compare_trap.sql
 id | qty 
----+-----
  1 | 9
(1 row)

 id | qty 
----+-----
  1 |   9
  2 |  10
(2 rows)

The first query lost Ben’s quantity of 10. As text, '10' sorts before '5', because the comparison stops at the first character and '1' comes before '5'. Casting to int gives the numeric answer. The login event has no qty, so its ->> is NULL and it drops out of both queries.

Nested Paths With #>>

#> and #>> take a path array, which is shorter than chaining arrows.

-- paths.sql
SELECT id,
       payload #>> '{user,name}' AS name,
       payload #>> '{user,tier}' AS tier,
       payload #>> '{tags,0}'    AS first_tag
FROM events ORDER BY id;

$ psql -d sqldemo -f paths.sql
 id | name | tier | first_tag 
----+------+------+-----------
  1 | Ana  | gold | gift
  2 | Ben  |      | 
  3 | Cai  | gold | gift
  4 | Ana  |      | 
(4 rows)

A missing key is not an error; it is NULL. Ben has no tier and an empty tag list, and the login event has no tags at all. Array positions in a JSON path start at 0, unlike SQL arrays.

Building JSON

jsonb_build_object makes a document from values, and jsonb_agg gathers rows into an array.

-- build.sql
SELECT jsonb_build_object('id', id, 'kind', kind, 'qty', payload -> 'qty') AS summary
FROM events WHERE kind = 'order' ORDER BY id;
SELECT jsonb_agg(payload #>> '{user,name}' ORDER BY id) AS buyers
FROM events WHERE kind = 'order';

$ psql -d sqldemo -f build.sql
                summary                
---------------------------------------
 {"id": 1, "qty": 9, "kind": "order"}
 {"id": 2, "qty": 10, "kind": "order"}
 {"id": 3, "qty": 2, "kind": "order"}
(3 rows)

        buyers         
-----------------------
 ["Ana", "Ben", "Cai"]
(1 row)

The keys came out as id, qty, kind, not in the order written. JSONB stores keys sorted by length and then alphabetically, so never rely on key order in a JSONB value. jsonb_agg is how an API endpoint returns a list straight from SQL.

Updating JSONB

jsonb_set replaces or adds a path, || merges keys in, and - removes a key.

-- update.sql
UPDATE events SET payload = jsonb_set(payload, '{user,tier}', '"silver"') WHERE id = 2;
UPDATE events SET payload = payload || '{"coupon": "SAVE10"}' WHERE id = 3;
UPDATE events SET payload = payload - 'device' WHERE id = 4;
SELECT id, payload FROM events WHERE id IN (2, 3, 4) ORDER BY id;

$ psql -d sqldemo -f update.sql
UPDATE 1
UPDATE 1
UPDATE 1
 id |                                              payload                                              
----+---------------------------------------------------------------------------------------------------
  2 | {"qty": 10, "tags": [], "user": {"name": "Ben", "tier": "silver"}}
  3 | {"qty": 2, "tags": ["gift", "rush"], "user": {"name": "Cai", "tier": "gold"}, "coupon": "SAVE10"}
  4 | {"user": {"name": "Ana"}}
(3 rows)

Ben had no tier, so jsonb_set added it. The new value must itself be JSON, which is why '"silver"' has double quotes inside the single quotes. Every one of these statements writes a whole new document for the row, even to change one field.

Containment and Key Existence

@> asks “does this document contain that one?”; ? asks “does this key or array element exist?”

-- containment.sql
SELECT id FROM events WHERE payload @> '{"user": {"tier": "gold"}}' ORDER BY id;
SELECT id FROM events WHERE payload ? 'coupon';
SELECT id FROM events WHERE payload -> 'tags' ? 'rush';

$ psql -d sqldemo -f containment.sql
 id 
----
  1
  3
(2 rows)

 id 
----
  3
(1 row)

 id 
----
  3
(1 row)

@> matched on a nested fragment without spelling out a path. Applied to an array, ? checks for a string element, which is how the last query found the rush order.

Expanding JSON Into Rows

Set-returning functions turn arrays and objects back into rows you can join and group.

-- expand.sql
SELECT e.id, t.tag
FROM events e, jsonb_array_elements_text(e.payload -> 'tags') AS t(tag)
ORDER BY e.id, t.tag;
SELECT key, value FROM events, jsonb_each_text(payload -> 'user') WHERE id = 1;

$ psql -d sqldemo -f expand.sql
 id | tag  
----+------
  1 | gift
  3 | gift
  3 | rush
(3 rows)

 key  | value 
------+-------
 name | Ana
 tier | gold
(2 rows)

Each tag became its own row, paired with its event id. Events with no tags produce no rows, the way an inner join would. jsonb_each_text does the same for an object’s keys, which is handy for exploring documents whose shape you do not know.

Indexing JSONB

A GIN index makes containment queries fast on large tables. First add 100,000 generated events.

-- gin_index.sql
INSERT INTO events
SELECT g, 'order', jsonb_build_object('user', jsonb_build_object('name', 'u' || g,
       'tier', CASE WHEN g % 100 = 0 THEN 'gold' ELSE 'basic' END), 'qty', g % 20)
FROM generate_series(5, 100000) AS g;
CREATE INDEX events_payload_idx ON events USING gin (payload jsonb_path_ops);
ANALYZE events;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE payload @> '{"user": {"tier": "gold"}}';

$ psql -d sqldemo -f gin_index.sql
INSERT 0 99996
CREATE INDEX
ANALYZE
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Bitmap Heap Scan on events (actual rows=1002 loops=1)
         Recheck Cond: (payload @> '{"user": {"tier": "gold"}}'::jsonb)
         Heap Blocks: exact=1001
         ->  Bitmap Index Scan on events_payload_idx (actual rows=1002 loops=1)
               Index Cond: (payload @> '{"user": {"tier": "gold"}}'::jsonb)
(6 rows)

The index found the 1,002 gold events (1,000 generated, plus Ana and Cai) without scanning the other 99,000 documents. jsonb_path_ops builds a smaller index that supports only @>; the default GIN operator class also supports ?. If you always filter on one key, a plain B-tree on (payload ->> 'key') is smaller still.

Practice

1. List the names of every user who ordered more than 5 items, using a numeric comparison.

-- practice_big_qty.sql
SELECT payload #>> '{user,name}' AS name, (payload ->> 'qty')::int AS qty
FROM events
WHERE id <= 4 AND (payload ->> 'qty')::int > 5
ORDER BY qty DESC;

$ psql -d sqldemo -f practice_big_qty.sql
 name | qty 
------+-----
 Ben  |  10
 Ana  |   9
(2 rows)

2. Count how many of the four original events carry each tag.

-- practice_tag_counts.sql
SELECT t.tag, count(*) AS events
FROM events e, jsonb_array_elements_text(e.payload -> 'tags') AS t(tag)
WHERE e.id <= 4
GROUP BY t.tag ORDER BY events DESC, t.tag;

$ psql -d sqldemo -f practice_tag_counts.sql
 tag  | events 
------+--------
 gift |      2
 rush |      1
(2 rows)

3. Add a "vip": true flag to every event whose user is gold, then count them.

-- practice_vip.sql
UPDATE events SET payload = payload || '{"vip": true}'
WHERE payload @> '{"user": {"tier": "gold"}}';
SELECT count(*) AS vips FROM events WHERE payload @> '{"vip": true}';

$ psql -d sqldemo -f practice_vip.sql
UPDATE 1002
 vips 
------
 1002
(1 row)