Turning JSON into rows and rows into JSON

Turning JSON into rows means giving each element of an array, or each key of an object, a row of its own, so that WHERE, JOIN and GROUP BY can work on them like on any table. SQLite does it with the table-valued function json_each(), PostgreSQL with jsonb_array_elements() and its relatives, and PostgreSQL 17 or later and MySQL 8 with the SQL standard’s JSON_TABLE, which also names and types the new columns. Turning rows into JSON is the reverse: json_object() builds one object from the values of a row, and aggregate functions such as json_group_array() in SQLite, json_agg() in PostgreSQL and JSON_ARRAYAGG() in MySQL collect many rows into one array. That is how one query returns an order with its items nested inside, ready for an app or an API. This lesson turns JSON into rows and rows into JSON on an order and its items, in all three engines.

Every example on this page runs in the browser with its Run button. It runs on SQLite 3.49.1, the version that sql.js 1.14.2 bundles; for PostgreSQL and MySQL, the functions named here follow their manuals.

JSON into rows with json_each()

-- Orders with their tags as a JSON array: json_each() gives each tag a row of its own.
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, tags TEXT);
INSERT INTO orders VALUES
  (101, 'Asha',   '["gift", "express"]'),
  (102, 'Ravi',   '[]'),
  (103, 'Meera',  '["express", "fragile"]'),
  (104, 'Tenzin', '["express"]');

SELECT orders.id, tag.key AS position, tag.value AS tag, tag.type
FROM orders, json_each(orders.tags) AS tag;

-- Once the tags are rows, GROUP BY can count them.
SELECT tag.value AS tag, count(*) AS orders
FROM orders, json_each(orders.tags) AS tag
GROUP BY tag.value
ORDER BY orders DESC, tag;

┌─────┬──────────┬─────────┬──────┐
│ id  │ position │   tag   │ type │
├─────┼──────────┼─────────┼──────┤
│ 101 │ 0        │ gift    │ text │
│ 101 │ 1        │ express │ text │
│ 103 │ 0        │ express │ text │
│ 103 │ 1        │ fragile │ text │
│ 104 │ 0        │ express │ text │
└─────┴──────────┴─────────┴──────┘
┌─────────┬────────┐
│   tag   │ orders │
├─────────┼────────┤
│ express │ 3      │
│ fragile │ 1      │
│ gift    │ 1      │
└─────────┴────────┘

json_each(orders.tags) sits in the FROM clause like a table, and it can read a column of the row beside it. For an array it returns one row per element: key is the position, counted from 0, value the element and type its JSON type. For an object, key holds each member’s name. Turning JSON into rows this way lets plain SQL count the tags: express appears on three orders. Order 102, whose list is empty, has no row at all, because the comma joins each order only to the rows the function returns. json_tree() works the same way but walks every level of a document, nested objects and arrays included, and its fullkey column gives each value’s full path.

The same job in PostgreSQL and MySQL

In SQLite, json_each() covers all of these jobs, with key for the position and value ->> 'name' for a column. The other two engines split them up:

• One row per array element: jsonb_array_elements(tags) in PostgreSQL; JSON_TABLE(tags, '$[*]' COLUMNS (...)) in MySQL.
• Elements as plain text: jsonb_array_elements_text(tags) in PostgreSQL; a PATH '$' column with a type in MySQL.
• One row per key of an object: jsonb_each(doc) in PostgreSQL; JSON_TABLE over JSON_KEYS(doc) in MySQL.
• The position of each element: WITH ORDINALITY in PostgreSQL and a FOR ORDINALITY column in MySQL, both counted from 1.
• Named, typed columns: JSON_TABLE, in PostgreSQL 17 and later and in MySQL; PostgreSQL also has jsonb_to_recordset().

JSON_TABLE is the standard’s answer: one call names the array to expand with a path such as '$.items[*]', then lists the columns, each with a type and a path inside the element, as in COLUMNS (sku text PATH '$.sku', qty int PATH '$.qty'). In PostgreSQL, a set-returning function such as jsonb_array_elements() joins to its table with a comma or CROSS JOIN LATERAL, which reads the row on its left.

Rows into JSON: one object per row, one array per order

-- Rows into JSON: one object per row, then one document per order with its items nested inside.
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, paid INTEGER);
CREATE TABLE items (order_id INTEGER, sku TEXT, qty INTEGER, price_paise INTEGER);
INSERT INTO orders VALUES (101, 'Asha', 1), (102, 'Ravi', 0);
INSERT INTO items VALUES (101, 'RICE-5KG', 1, 42000), (101, 'CHAI-250G', 3, 15000);

-- json_object() takes names and values in pairs.
SELECT json_object('sku', sku, 'qty', qty, 'paise', qty * price_paise) AS item
FROM items;

-- A subquery collects each order's items into an array, sorted by SKU.
SELECT json_object(
         'id', o.id,
         'customer', o.customer,
         'paid', json(CASE WHEN o.paid THEN 'true' ELSE 'false' END),
         'items', (SELECT json_group_array(json_object('sku', i.sku, 'qty', i.qty) ORDER BY i.sku)
                   FROM items AS i
                   WHERE i.order_id = o.id)
       ) AS doc
FROM orders AS o;

┌───────────────────────────────────────────┐
│                   item                    │
├───────────────────────────────────────────┤
│ {"sku":"RICE-5KG","qty":1,"paise":42000}  │
│ {"sku":"CHAI-250G","qty":3,"paise":45000} │
└───────────────────────────────────────────┘
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                    doc                                                    │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ {"id":101,"customer":"Asha","paid":true,"items":[{"sku":"CHAI-250G","qty":3},{"sku":"RICE-5KG","qty":1}]} │
│ {"id":102,"customer":"Ravi","paid":false,"items":[]}                                                      │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

json_object() takes names and values in pairs and writes them in that order. Each SQL value becomes the matching JSON value: text a string, numbers numbers, NULL null. SQLite has no boolean type, so paid would come out as 1 or 0; json('true') writes JSON’s true instead. The subquery runs once per order and collects that order’s items with json_group_array(), and ORDER BY i.sku inside the aggregate fixes the order of the array. Order 102 has no items, and the aggregate over no rows gives [], so the document still has its items key. Turning rows into JSON in the other engines uses these functions:

• PostgreSQL 18: json_build_object() and json_build_array() build an object and an array, as do the standard’s JSON_OBJECT() and JSON_ARRAY() from version 16. Over rows, json_agg() (or jsonb_agg() and JSON_ARRAYAGG()) builds an array and json_object_agg() (or JSON_OBJECTAGG()) an object, and ORDER BY may follow the aggregate’s argument.
• MySQL 8.4: JSON_OBJECT() and JSON_ARRAY(), and over rows JSON_ARRAYAGG() and JSON_OBJECTAGG(), which take no ORDER BY.
• SQLite 3.49: json_object() and json_array(), and over rows json_group_array() and json_group_object(), which accept ORDER BY from version 3.44.

Where these queries go wrong

Every one of these runs without an error and returns the wrong JSON or the wrong rows:

• Vanishing rows: an inner join to an empty or missing array drops the whole row.
• Null items: a LEFT JOIN followed by an aggregate turns “no items” into one item made of nulls.
• Quoted arrays: JSON text that loses its JSON marking is written into a document as a string.
• Unstable order: an aggregate without ORDER BY follows the plan, and an index can change it.
• Repeated keys: an object built from rows can name the same key twice.
• Bad values in typed columns: JSON_TABLE turns a value it cannot convert into NULL unless told otherwise.

Your turn

-- One row per customer: customer_id and doc, a JSON object with the keys name and orders.
SELECT c.id AS customer_id,
       json_object('name', c.name) AS doc
FROM customers AS c;

# Sample tests: each one builds small customers and orders tables, runs your query on them and compares the rows it
# returns (in any order). The JSON documents are compared as text.
tests:
  - name: nests each customer's orders, sorted by id
    setup: |
      CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
      CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total_paise INTEGER);
      INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Ravi Menon');
      INSERT INTO orders VALUES (105, 1, 21500), (103, 1, 30800), (104, 2, 9900);
    expected:
      columns: [customer_id, doc]
      rows:
        - [1, '{"name":"Asha Kulkarni","orders":[{"id":103,"total_paise":30800},{"id":105,"total_paise":21500}]}']
        - [2, '{"name":"Ravi Menon","orders":[{"id":104,"total_paise":9900}]}']
  - name: gives a customer without orders an empty array
    setup: |
      CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
      CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total_paise INTEGER);
      INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (5, 'Tenzin Dorjee');
      INSERT INTO orders VALUES (103, 1, 30800);
    expected:
      columns: [customer_id, doc]
      rows:
        - [1, '{"name":"Asha Kulkarni","orders":[{"id":103,"total_paise":30800}]}']
        - [5, '{"name":"Tenzin Dorjee","orders":[]}']
  - name: keeps names in any script and quotes inside names
    setup: |
      CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
      CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total_paise INTEGER);
      INSERT INTO customers VALUES (7, 'मीरा "Meera" Iyer');
      INSERT INTO orders VALUES (110, 7, 120000), (108, 7, 0);
    expected:
      columns: [customer_id, doc]
      rows:
        - [7, '{"name":"मीरा \"Meera\" Iyer","orders":[{"id":108,"total_paise":0},{"id":110,"total_paise":120000}]}']

JSON into rows without losing any, engine by engine Pro: opens with a Pro, Premium or Ultimate pass

Building JSON that survives: empty lists, nesting, order and keys Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• Turning JSON into rows uses a function in FROM: json_each() in SQLite, jsonb_array_elements() or JSON_TABLE in PostgreSQL, and JSON_TABLE in MySQL.
• An inner join to an expanded array drops the rows whose array is empty or missing.
• json_object() and an aggregate such as json_group_array() turn rows into JSON; a subquery per list gives each parent its own array.
• State the order of an array with ORDER BY inside the aggregate where the engine allows it.
• Plain text is written as a JSON string; wrap text that holds JSON in json() to embed it.