MySQL and JSON - transform array to rows

Question

Let's assume we have following variable:

SET @j = '[10, 20, {"a": "bbb"}]';

The question is how to extract items as rows?

Basic query, like this:

SELECT JSON_EXTRACT(@j, '$');

Returns the same values as input, but I would like to have something like this:

10
20
{"a", "bbb"}

How to do this?

Answer 1

Here are three ways to convert your array into rows. Using the same assumed JSON value:

SET @j = '[10, 20, {"a": "bbb"}]';

Using in-line table of numbers. MySQL and MariaDB compatible:

WITH sequenceGenerator (sequenceNumber) AS (
    SELECT 0 AS sequenceNumber
    UNION ALL
    SELECT 1
    UNION ALL
    SELECT 2
)
SELECT
    JSON_EXTRACT(@j, CONCAT('$[', sequenceNumber, ']')) AS arrayValue
FROM
    sequenceGenerator;

Using MySQL 8.0.4+ JSON_TABLE():

SELECT
    arrayValue
FROM
    JSON_TABLE(
        @j,
         '$[*]'
        COLUMNS(
            arrayValue JSON PATH '$')
    ) AS tt;

Using MariaDB SEQUENCE Engine to get rid of in-line sequence table:

SELECT
    JSON_EXTRACT(@j, CONCAT('$[', seq, ']')) AS arrayValue
FROM
    seq_0_to_2;

To make more generic in MariaDB, use a "best guess" max for the array length then limit sequence to JSON length. This example assumes the largest array will have 1024 or fewer elements:

SELECT
    JSON_EXTRACT(@j, CONCAT('$[', seq, ']')) AS arrayValue
FROM
    seq_0_to_1024 AS sequenceTable
WHERE
    sequenceTable.seq < JSON_LENGTH(@j);