CODEWITHSUNDEEP
mysqljson
PiotrCh
PiotrCh

Reputation: 103

MySQL and JSON - transform array to rows

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?

Upvotes: 7

Views: 10296

Answers (2)

Lucio Mollinedo
Lucio Mollinedo

Reputation: 2444

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

Upvotes: 3

Kevin Stephenson
Kevin Stephenson

Reputation: 326

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

Upvotes: 9

Related Questions