Building JSON in a query in SQL

The other direction: rows in, one JSON document out.

SELECT
    JSON_OBJECT(
        'id', id,
        'sku', sku,
        'price', price,
        'labels', JSON_ARRAY('catalog', 'active'),
        'nested', JSON_OBJECT('currency', 'USD')
    ) AS document
FROM products
ORDER BY id;

How it works

1. json_object takes alternating keys and values.
2. json_array takes a list; both nest inside each other freely.
3. json_quote wraps a single SQL value as valid JSON.

Keywords and builtins used here

• AS
• BY
• FROM
• ORDER
• SELECT

The run, in numbers

Lines

10

Characters to type

182

Tokens

41

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 26 seconds.

Type this snippet

Step 1 of 5 in Writing JSON, step 4 of 14 in JSON & search.

← Previous Next →