JSON in a column

Two JSON types: use jsonb

PostgreSQL has two JSON types. Feed both the same text and they keep different things:

SELECT '{"b": 1,   "a": 2, "a": 3}'::json  AS as_json,
       '{"b": 1,   "a": 2, "a": 3}'::jsonb AS as_jsonb;

          as_json           |     as_jsonb
----------------------------+------------------
 {"b": 1,   "a": 2, "a": 3} | {"a": 3, "b": 1}

json keeps the text exactly as written, spaces and duplicate keys included, and parses it again every time you query it. jsonb parses once and stores a structured binary form: no extra whitespace, keys reordered, and only the last of any duplicate key. It is faster to query and it can be indexed. Unless you need the exact original text back, use jsonb.

Add some documents

Products in shop have different specifications: a keyboard has a layout, a monitor has a size and ports. Rather than a column for every possible attribute, give them a document:

ALTER TABLE products ADD COLUMN attributes jsonb NOT NULL DEFAULT '{}';

UPDATE products SET attributes = '{"wireless": false, "layout": "TKL", "switches": "brown"}' WHERE id = 1;
UPDATE products SET attributes = '{"wireless": true, "dpi": 1600, "colors": ["black", "white"]}' WHERE id = 2;
UPDATE products SET attributes = '{"size_in": 27, "resolution": "2560x1440", "ports": ["HDMI", "DisplayPort", "USB-C"]}' WHERE id = 3;
UPDATE products SET attributes = '{"wireless": true, "anc": true, "battery_h": 30}' WHERE id = 6;
UPDATE products SET attributes = '{"wireless": null}' WHERE id = 7;

Getting values out

SELECT name, attributes -> 'dpi' AS arrow, attributes ->> 'dpi' AS double_arrow,
       pg_typeof(attributes -> 'dpi') AS t1, pg_typeof(attributes ->> 'dpi') AS t2
FROM products WHERE id = 2;

      name      | arrow | double_arrow |  t1   |  t2
----------------+-------+--------------+-------+------
 Wireless mouse | 1600  | 1600         | jsonb | text

They print the same, but -> gives you JSON you can keep navigating, and ->> gives you text you can compare and cast. Containment asks “does the document include this piece”:

SELECT name FROM products WHERE attributes @> '{"wireless": true}' ORDER BY id;

            name
-----------------------------
 Wireless mouse
 Noise-cancelling headphones

Arrays expand into rows with jsonb_array_elements_text, joined laterally:

SELECT p.name, port FROM products p, jsonb_array_elements_text(p.attributes -> 'ports') AS port;

      name       |    port
-----------------+-------------
 27-inch monitor | HDMI
 27-inch monitor | DisplayPort
 27-inch monitor | USB-C

Missing is not the same as null

SELECT id, name, attributes ->> 'wireless' AS wireless, attributes ? 'wireless' AS has_key
FROM products WHERE id IN (5, 7) ORDER BY id;

 id |     name     | wireless | has_key
----+--------------+----------+---------
  5 | Laptop stand |          | f
  7 | Webcam       |          | t

Changing one value

jsonb_set returns a new document with one path replaced:

UPDATE products SET attributes = jsonb_set(attributes, '{dpi}', '3200') WHERE id = 2;
SELECT attributes FROM products WHERE id = 2;

                          attributes
---------------------------------------------------------------
 {"dpi": 3200, "colors": ["black", "white"], "wireless": true}

It reads like an edit to one key, but it builds and stores a whole new document. For small documents that does not matter. The review on the last page of this topic measures when it does.

Check yourself

1. Why should most applications use jsonb rather than json?

   Show answer

   jsonb is parsed once and stored in a binary form, so queries do not reparse the text, and it supports indexes. json only makes sense when you must keep the exact original text, including whitespace, key order and duplicate keys.

   I could explain this without looking

2. What is the difference between attributes -> 'dpi' and attributes ->> 'dpi'?

   Show answer

   -> returns the value as jsonb, which you can keep navigating into. ->> returns it as text, ready to compare or cast.

   I could explain this without looking

3. How do you find products where the wireless key is present but null?

   Show answer

   WHERE attributes ? 'wireless' AND attributes ->> 'wireless' IS NULL. The ? separates “present but null” from “missing”, which ->> alone cannot.

   I could explain this without looking