JSONB: Query Semi-Structured Data Without Regretting It

Some data does not fit a fixed set of columns. A product has a screen size, or a weight, or a list of tags, or none of them, and the set differs from row to row. Data that arrives from an outside system as a document is the same story. PostgreSQL has a type for it, jsonb, which stores a JSON document in a form that can be searched, indexed and changed. It is good at that. It is also the type people reach for when they have not decided what their data looks like, and that is the case they regret. This lesson shows how to read, search and index it, then what you lose.

jsonb is the binary form of JSON, and it is the one to use. The json type keeps the text exactly as typed and has to parse it again on every read.

A catalog with one document per product

The shop now sells items with attributes that differ per product. The table keeps the fixed facts in columns and the rest in one jsonb column, and it refuses anything that is not a JSON object.

CREATE TABLE catalog (
  product_id integer PRIMARY KEY,
  name       text NOT NULL,
  specs      jsonb NOT NULL CHECK (jsonb_typeof(specs) = 'object')
);
INSERT INTO catalog
SELECT g, 'Item ' || g,
       jsonb_build_object(
         'brand',    (ARRAY['Acme', 'Borneo', 'Cebu Co', 'Delta'])[(g % 4) + 1],
         'color',    (ARRAY['red', 'blue', 'black', 'white', 'green'])[(g % 5) + 1],
         'weight_g', 100 + (g % 900),
         'wireless', (g % 3 = 0),
         'tags',     CASE WHEN g % 50 = 0 THEN jsonb_build_array('gift', 'sale')
                          WHEN g % 10 = 0 THEN jsonb_build_array('sale')
                          ELSE jsonb_build_array('regular') END)
FROM generate_series(1, 20000) AS g;
ANALYZE catalog;
SET max_parallel_workers_per_gather = 0;
SELECT specs FROM catalog WHERE product_id = 50;

CREATE TABLE
INSERT 0 20000
ANALYZE
SET
                                               specs
----------------------------------------------------------------------------------------------------
 {"tags": ["gift", "sale"], "brand": "Cebu Co", "color": "red", "weight_g": 150, "wireless": false}
(1 row)

Reading a document

-> returns a part of the document as jsonb. ->> returns it as text, without the quotes. A number inside the document is still a JSON number until you cast the text.

SELECT specs -> 'brand'                    AS as_json,
       specs ->> 'brand'                   AS as_text,
       (specs ->> 'weight_g')::int + 1     AS weight_plus_one,
       specs -> 'tags' ->> 0               AS first_tag
FROM catalog WHERE product_id = 50;
SELECT specs ->> 'brand' = 'Cebu Co' AS text_compare FROM catalog WHERE product_id = 50;
SELECT specs -> 'brand' = 'Cebu Co' AS json_compare FROM catalog WHERE product_id = 50;

  as_json  | as_text | weight_plus_one | first_tag
-----------+---------+-----------------+-----------
 "Cebu Co" | Cebu Co |             151 | gift
(1 row)

 text_compare
--------------
 t
(1 row)

ERROR:  invalid input syntax for type json
LINE 1: SELECT specs -> 'brand' = 'Cebu Co' AS json_compare FROM cat...
                                  ^
DETAIL:  Token "Cebu" is invalid.
CONTEXT:  JSON data, line 1: Cebu...

The two forms matter when you compare. ->> gives text, so it compares with a text value. -> gives jsonb, so PostgreSQL tries to read Cebu Co as a JSON value, and that is not valid JSON. To compare the jsonb form, the right side has to be JSON too, with its quotes.

Searching: contains and exists

The operator to remember is @>, "contains". It asks whether the left document includes the right one, so you can test several keys at once. ? asks whether a key exists.

SELECT count(*) AS wireless_red_acme
FROM catalog WHERE specs @> '{"brand": "Acme", "color": "red", "wireless": true}';
SELECT specs ? 'color' AS has_color, specs ? 'size' AS has_size
FROM catalog WHERE product_id = 1;

 wireless_red_acme
-------------------
               333
(1 row)

 has_color | has_size
-----------+----------
 t         | f
(1 row)

Indexing a jsonb column

Without an index, @> reads every document. A GIN index, as in the last section, indexes every key and value in the document.

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT product_id FROM catalog WHERE specs @> '{"brand": "Acme", "color": "red", "wireless": true}';
CREATE INDEX catalog_specs_gin ON catalog USING gin (specs);
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT product_id FROM catalog WHERE specs @> '{"brand": "Acme", "color": "red", "wireless": true}';

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Seq Scan on catalog (actual rows=333.00 loops=1)
   Filter: (specs @> '{"brand": "Acme", "color": "red", "wireless": true}'::jsonb)
   Rows Removed by Filter: 19667
(3 rows)

CREATE INDEX
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Bitmap Heap Scan on catalog (actual rows=333.00 loops=1)
   Recheck Cond: (specs @> '{"brand": "Acme", "color": "red", "wireless": true}'::jsonb)
   Heap Blocks: exact=333
   ->  Bitmap Index Scan on catalog_specs_gin (actual rows=333.00 loops=1)
         Index Cond: (specs @> '{"brand": "Acme", "color": "red", "wireless": true}'::jsonb)
         Index Searches: 1
(6 rows)

The plan changes from a scan of 20,000 rows to a Bitmap Index Scan. There is a second GIN operator class, jsonb_path_ops, that is smaller and faster for @>, but it does not support ?.

CREATE INDEX catalog_specs_path ON catalog USING gin (specs jsonb_path_ops);
SELECT pg_size_pretty(pg_relation_size('catalog'))             AS table_size,
       pg_size_pretty(pg_relation_size('catalog_specs_gin'))  AS default_ops,
       pg_size_pretty(pg_relation_size('catalog_specs_path')) AS path_ops;

CREATE INDEX
 table_size | default_ops | path_ops
------------+-------------+----------
 3080 kB    | 480 kB      | 320 kB
(1 row)

Pick jsonb_path_ops if you only use @>, and the default if you also test for keys.

One key that you filter on with a range or an order is better served by an ordinary B-tree on an expression of that key. It also gives the planner statistics. Compare the estimates before and after, with costs left on.

EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)
SELECT product_id FROM catalog WHERE (specs ->> 'weight_g')::int > 990;
CREATE INDEX catalog_weight_idx ON catalog (((specs ->> 'weight_g')::int));
ANALYZE catalog;
EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)
SELECT product_id FROM catalog WHERE (specs ->> 'weight_g')::int > 990;

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Seq Scan on catalog  (cost=0.00..785.00 rows=6667 width=4) (actual rows=198.00 loops=1)
   Filter: (((specs ->> 'weight_g'::text))::integer > 990)
   Rows Removed by Filter: 19802
(3 rows)

CREATE INDEX
ANALYZE
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on catalog  (cost=5.83..339.27 rows=199 width=4) (actual rows=198.00 loops=1)
   Recheck Cond: (((specs ->> 'weight_g'::text))::integer > 990)
   Heap Blocks: exact=25
   ->  Bitmap Index Scan on catalog_weight_idx  (cost=0.00..5.78 rows=199 width=0) (actual rows=198.00 loops=1)
         Index Cond: (((specs ->> 'weight_g'::text))::integer > 990)
         Index Searches: 1
(6 rows)

Before the index, PostgreSQL had no statistics for what is inside the document, guessed that a third of the rows would match, and expected 6,667 against 198 real rows. After it, the estimate is 199. Statistics for a key inside a document exist only if you index that expression.

Changing a document

|| merges keys into a document, jsonb_set replaces one path, and - removes a key.

SELECT specs || '{"color": "purple"}'                  AS merged FROM catalog WHERE product_id = 50;
SELECT jsonb_set(specs, '{tags}', '["archived"]')      AS replaced FROM catalog WHERE product_id = 50;
SELECT specs - 'tags'                                  AS without_tags FROM catalog WHERE product_id = 50;

                                                merged
-------------------------------------------------------------------------------------------------------
 {"tags": ["gift", "sale"], "brand": "Cebu Co", "color": "purple", "weight_g": 150, "wireless": false}
(1 row)

                                            replaced
------------------------------------------------------------------------------------------------
 {"tags": ["archived"], "brand": "Cebu Co", "color": "red", "weight_g": 150, "wireless": false}
(1 row)

                               without_tags
--------------------------------------------------------------------------
 {"brand": "Cebu Co", "color": "red", "weight_g": 150, "wireless": false}
(1 row)

These return a new document. They do not change the row until you use them in an UPDATE. And an UPDATE of one key writes the whole document again, as a new row version. If two application sessions each read the document, change a different key, and write the whole document back, the second write wipes out the first key. Lock the row when you read it, or make the change inside one UPDATE statement with ||.

What you give up

DROP INDEX catalog_weight_idx;
INSERT INTO catalog VALUES (20001, 'Odd item', '{"brand": "Acme", "weight_g": "n/a"}');
SELECT sum((specs ->> 'weight_g')::int) AS total_weight FROM catalog;

DROP INDEX
INSERT 0 1
ERROR:  invalid input syntax for type integer: "n/a"

I dropped the expression index first. With it in place, the cast would have failed at the INSERT itself, which is a lucky side effect and not a design. Without it, jsonb accepted a text weight, because it has no types for the keys inside. The error comes later, at the report, from a row somebody inserted weeks ago. The same is true for a foreign key, a NOT NULL or a UNIQUE on a key inside the document. None of them exist there, except through a CHECK that you write yourself.

A workable rule: keep what you join, filter, sort or constrain on in real columns, and keep the leftovers in jsonb. If a key starts to appear in every WHERE, give it a column. A generated column makes that cheap: ADD COLUMN brand text GENERATED ALWAYS AS (specs ->> 'brand') STORED.

Common mistakes

• Using jsonb for data whose shape you already know.
• Comparing -> with a text value, or ->> text with a number, without a cast.
• Using @> and expecting a plain B-tree to help. It needs GIN.
• Indexing the whole document when only one key is searched.
• Updating one key of a large document many times a second.

If you skip this, you will keep a table of documents that nobody can query fast and nobody dares to change. Which key in your documents appears in every query you run against them?