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?

AFAIK, there isn't a function to convert a json array to row in mysql because mysql does not have an ARRAY data type unlike postgresql. — e4c5
– e4c5, Commented Aug 5, 2016 at 9:57
Yes, I know. I could create SP with loop to iterate over all items, but I hope there is easier way to do this. — PiotrCh
– PiotrCh, Commented Aug 5, 2016 at 9:59

Kevin Stephenson · Accepted Answer · 2019-02-25 08:48:17Z

9

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);

answered Feb 25, 2019 at 8:48

Kevin Stephenson

3253 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sebastian Over a year ago

How can we fetch this from a column of a table? Instead of SET @j = as a json variable Can we read the JSON column from a table?

Lucio Mollinedo Over a year ago

@Sebastian That would have made this an actually useful answer.

Lucio Mollinedo · Accepted Answer · 2022-10-04 19:34:07Z

Kevin's answer would have been useful to me had it included an example converting an actual JSON column, instead of the contents of a variable.

For a table composed of:

mysql> select * from departmentTable;
+----+-----------------------+
| id | departments           |
+----+-----------------------+
|  1 | ["300", "310", "320"] |
|  2 | ["400", "405", "740"] |
+----+-----------------------+
2 rows in set (0.01 sec)

mysql>

The following SQL will work (tested with MySQL 8.0.30, though it might work with earlier minor versions as well):

select x.singleDepartment
from departmentTable dt
cross join json_table(
    dt.departments,
    '$[*]' 
    columns (
        singleDepartment varchar(50) PATH '$'
    )
) x
WHERE dt.id = 1 ;

Output:

mysql> select x.singleDepartment
    -> from departmentTable dt
    -> cross join json_table(
    ->     js.departments,
    ->     '$[*]'
    ->     columns (
    ->         singleDepartment varchar(50) PATH '$'
    ->   )
    -> ) x
    -> WHERE dt.id = 1 ;
+------------------+
| singleDepartment |
+------------------+
| 300              |
| 310              |
| 320              |
+------------------+
3 rows in set (0.01 sec)

mysql>

If you use the output of the query as input for another query (my case was a SELECT * FROM table WHERE doc IN (the_query_mentioned_earlier) ) and you get the following error :

Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

Convert explicitly the collation of the column in the SELECT (following the MySQL docs):

select x.singleDepartment COLLATE utf8mb4_0900_ai_ci as singleDepartment

Collectives™ on Stack Overflow

MySQL and JSON - transform array to rows

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related