Turning an array into rows in SQL

json_each is a table: one row per element, which puts JSON back inside SQL's reach.

SELECT
    w.id,
    tag.value AS tag,
    tag.type AS json_type
FROM webhooks AS w
JOIN JSON_EACH(w.payload, '$.tags') AS tag
ORDER BY w.id, tag.id;

SELECT
    fullkey,
    type
FROM JSON_TREE('{"a":{"b":[1,2]}}');

How it works

1. json_each yields key, value, type and fullkey per element.
2. Joining it to the table expands each row into one row per element.
3. json_tree does the same thing recursively, all the way down.

Keywords and builtins used here

• AS
• BY
• FROM
• JOIN
• ORDER
• SELECT
• type

The run, in numbers

Lines

12

Characters to type

196

Tokens

51

Three-star pace

90 tpm

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

Type this snippet

Step 3 of 3 in Reading JSON, step 3 of 14 in JSON & search.

← Previous Next →