JSON & JSONB

Some data does not fit neatly into fixed columns. Product specifications differ between a keyboard and a speaker, payment gateways send callbacks with dozens of fields, and app settings change every release. PostgreSQL can store such data as JSON (JavaScript Object Notation, the text format used by most web APIs) and query inside it with SQL, while the rest of your data stays in normal columns.

In this lesson you will store JSON in a jsonb column, read values out of it, search it, change it, turn it into rows and back, and index it.

json vs jsonb

PostgreSQL has two JSON types:

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

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

With jsonb, the last duplicate key wins and keys are reordered. That is fine for data, and it is why the rest of this lesson uses jsonb. Both types reject invalid JSON with an error.

Reading values: -> and ->>

The two most important operators differ by one character:

CREATE TABLE product_specs (
  product_id int PRIMARY KEY,
  specs jsonb NOT NULL
);

INSERT INTO product_specs VALUES
  (1, '{"colour": "black", "wireless": true, "battery": {"type": "AA", "hours": 400}, "dpi": [800, 1600]}'),
  (6, '{"colour": "silver", "wireless": true, "battery": {"type": "Li-ion", "hours": 30}}'),
  (3, '{"colour": "black", "wireless": false, "size": {"inches": 27}, "ports": ["HDMI", "DP"]}');

SELECT product_id,
       specs ->> 'colour' AS colour,
       specs -> 'battery' ->> 'hours' AS battery_hours,
       (specs #>> '{battery,hours}')::int * 2 AS doubled,
       specs -> 'dpi' -> 1 AS second_dpi,
       specs['size']['inches'] AS inches
FROM product_specs
ORDER BY product_id;

Query OK
Query OK, 3 rows affected
product_id | colour | battery_hours | doubled | second_dpi | inches
-----------+--------+---------------+---------+------------+-------
1          | black  | 400           | 800     | 1600       | NULL
3          | black  | NULL          | NULL    | NULL       | 27
6          | silver | 30            | 60      | NULL       | NULL
(3 rows)

->> gives text, so cast it (::int, ::numeric, ::boolean) before doing maths. JSON arrays are indexed from 0, unlike PostgreSQL arrays. A missing key simply returns NULL. Since PostgreSQL 14 you can also use subscripts, specs['size']['inches'], which return jsonb.

Searching JSON

The containment operator @> asks "does the left document contain this smaller document?" and the ? operator asks "does this key exist?":

CREATE TABLE product_specs (product_id int, specs jsonb);
INSERT INTO product_specs VALUES
  (1, '{"colour": "black", "wireless": true, "battery": {"hours": 400}}'),
  (6, '{"colour": "silver", "wireless": true, "battery": {"hours": 30}}'),
  (3, '{"colour": "black", "wireless": false, "ports": ["HDMI", "DP"]}');

SELECT product_id FROM product_specs WHERE specs @> '{"colour": "black", "wireless": true}';

SELECT product_id FROM product_specs WHERE specs ? 'ports';

SELECT product_id FROM product_specs WHERE specs @> '{"ports": ["HDMI"]}';

SELECT product_id FROM product_specs
WHERE jsonb_path_exists(specs, '$.battery.hours ? (@ > 100)');

Query OK
Query OK, 3 rows affected
product_id
----------
1
(1 row)

product_id
----------
3
(1 row)

product_id
----------
3
(1 row)

product_id
----------
1
(1 row)

Containment works at any depth and for arrays too: {"ports": ["HDMI"]} matches a document whose ports array includes "HDMI". The last query uses SQL/JSON path, a small query language inside a string: $ is the document, .battery.hours walks down, and ? (@ > 100) is a filter.

Changing JSON

jsonb values are replaced, not edited in place, but these functions and operators build the new value for you:

SELECT jsonb_set('{"colour": "black", "stock": 5}', '{stock}', '12') AS set_value,
       '{"colour": "black"}'::jsonb || '{"warranty_years": 2}' AS merged,
       '{"colour": "black", "temp": 1}'::jsonb - 'temp' AS removed_key,
       jsonb_build_object('id', 7, 'name', 'Bluetooth Speaker', 'tags', ARRAY['party']) AS built;

set_value                        | merged                                   | removed_key         | built
---------------------------------+------------------------------------------+---------------------+----------------------------------------------------------
{"stock": 12, "colour": "black"} | {"colour": "black", "warranty_years": 2} | {"colour": "black"} | {"id": 7, "name": "Bluetooth Speaker", "tags": ["party"]}
(1 row)

jsonb_set(doc, path, new_value) replaces one value, || merges two objects (keys on the right win), and - removes a key. In an UPDATE it looks like SET specs = specs || '{"warranty_years": 2}'.

Rows to JSON and JSON to rows

APIs often need JSON built from tables. jsonb_build_object and jsonb_agg do that in SQL, and JSON_TABLE (PostgreSQL 17) goes the other way, turning a JSON array into a table:

SELECT jsonb_build_object(
         'category', category,
         'products', jsonb_agg(jsonb_build_object('name', product_name, 'price', price) ORDER BY price)
       ) AS api_response
FROM products
WHERE category = 'Storage'
GROUP BY category;

SELECT *
FROM JSON_TABLE(
  '[{"sku": "MS-01", "qty": 3}, {"sku": "KB-02", "qty": 1}]'::jsonb,
  '$[*]' COLUMNS (sku text PATH '$.sku', qty int PATH '$.qty')
) AS cart;

api_response
----------------------------------------------------------------------------------------------------------------------------------
{"category": "Storage", "products": [{"name": "64GB Pen Drive", "price": 549.00}, {"name": "1TB External SSD", "price": 6499.00}]}
(1 row)

sku   | qty
------+----
MS-01 | 3
KB-02 | 1
(2 rows)

The first query returns one ready-made JSON document per category. The second turns a cart sent by a mobile app into rows you can join with products. Before PostgreSQL 17, the same job was done with jsonb_to_recordset or jsonb_array_elements, which you will still see in older code.

Indexing jsonb

A GIN index on a jsonb column makes @>, ? and JSON path searches fast:

CREATE INDEX product_specs_gin ON product_specs USING gin (specs);
CREATE INDEX product_specs_path ON product_specs USING gin (specs jsonb_path_ops);
CREATE INDEX product_specs_colour ON product_specs ((specs ->> 'colour'));

The default operator class supports @>, ? and more. jsonb_path_ops supports fewer operators (mainly @> and path queries) but makes a smaller, faster index. When you always filter on one key, such as WHERE specs ->> 'colour' = 'black', a plain B-tree expression index on that key (the third line) is the best choice.

Common mistakes

• Using -> when you need text. specs -> 'colour' = 'black' compares jsonb with text and fails. Use ->>.
• Doing maths on ->> without a cast. It returns text; cast to int or numeric first.
• Choosing json by default. Use jsonb unless you must keep the exact original text.
• Storing everything in one JSON column. You lose types, constraints and foreign keys. Keep core fields as columns.

Key points

• jsonb stores parsed JSON that is fast to query and can be indexed; json keeps the exact text.
• -> returns jsonb, ->> returns text; #>/#>> follow a path; JSON arrays start at 0.
• @> tests containment, ? tests a key, and jsonb_path_exists runs SQL/JSON path queries.
• jsonb_set, || and - build changed documents.
• jsonb_build_object and jsonb_agg create JSON from rows; JSON_TABLE turns JSON into rows.
• GIN indexes speed up containment searches; expression indexes speed up one key.

CREATE TABLE payments (payment_id int, response jsonb);
INSERT INTO payments VALUES
  (1, '{"status": "success", "amount": 799, "method": "upi"}'),
  (2, '{"status": "failed", "amount": 3499, "method": "upi"}'),
  (3, '{"status": "success", "amount": 1299, "method": "upi"}');

SELECT sum((response ->> 'amount')::numeric) AS upi_success_total
FROM payments
WHERE response @> '{"status": "success", "method": "upi"}';