How to select from MySQL JSON array as rows?

Question

Using MySQL 5.7, how can I select array elements from a JSON column, as rows?

Table Person

id      data
1       [{"name":"one"},{"name":"two"}]
2       [{"name":"three"},{"name":"four"}]

I want to "pivot" the elements in the JSON array to rows. Non-working SQL below...

SELECT
    p.id AS personId,
    d->'$.name' AS name
FROM
    Person p
    JSON_EXTRACT(p.data) d  # <-- not valid SQL
WHERE
    d->'$.name' <> 'three'

Expected Output

personId  name
1         one
1         two
2         four

The only reasonable way to do this in to use JSON_TABLE() in MySQL 8.0, so you should upgrade. The alternative is to store your data in normal rows and columns. I would recommend avoiding JSON in your case anyway, since you need to search for a specific value in a JSON field. I recommend to reference JSON fields only in the select-list, and no other clause of your query. — Bill Karwin, Oct 28 '20 at 15:01
This is an existing DB (not as pictured here). We don't use the JSON data in SQL queries, this is for data recovery purposes where it'd be helpful to narrow our fix based on some value(s) in the JSON blob. Other than that it's not touched by the DB. Yes, upgrading to MySQL 8 would be nice... — Josh M., Oct 29 '20 at 03:11

score 3 · Answer 1 · answered Oct 28 '20 at 14:20

3

If you are running MySQL 8.0, you can use json_table():

select p.id, x.name
from person p
cross join json_table(
    p.data,
    '$[*]' columns (name varchar(50) path '$.name')
) x
where x.name <> 'three'

In earlier versions, one alternative is to use a derived table of numbers to unnest the array:

select *
from (
    select p.id, json_unquote(json_extract(p.data, concat('$[', n.num, '].name'))) name
    from person p
    inner join (select 0 num union all select 1 union all select 2) n
        on n.num < json_length(p.data)
) t
where name <> 'three'

The union all subquery should contain at least as many elements as the maximum number of elements in any JSON array of your table.

Demos on DB Fiddle:

MySQL 8.0
MySQL 5.7

answered Oct 28 '20 at 14:20

GMB

216,147
25
84
135

What if he wants to handle a JSON array with four elements? You have to change the query? Forgive me, but this is not a reasonable solution. Which I guess is the point of showing it. – Bill Karwin Oct 28 '20 at 14:39
@BillKarwin: the idea is to have a table of numbers that has largely enough elements, so there is no need to worry about modifying the query later on. As you are aware, there are relatively easy ways to generate very large numbers table. That said, I do agree with you that the data would be best stored in a normalized structure (why did you delete your answer by the way? I find it is very complementary to mine). – GMB Oct 28 '20 at 14:46
1

I realized this question was a duplicate, and voted to close it. I felt it would be bad form to post an answer myself and then vote to close the question, blocking others from answering. But I'll add a comment to the same effect. – Bill Karwin Oct 28 '20 at 14:57

How to select from MySQL JSON array as rows?

1 Answers1