I don't have MySql readily available, but from what I understand, the best way to achieve what I want in MySql is to make use of JSON_TABLE. Also, from what I understand, JSON_TABLE is being very actively worked on right now, which reduces the desire to switch to MySql just for this.

I have a work around query, which gives me what I want, but want to know if it is the most efficient way.

create table natural_numbers (primary key (n))
with recursive nn as (
    select 0 as n
    select n + 1
    from nn
    where nn.n < 100
select *
from nn;

with all_values as (
    select json_value(pr.record_data, concat('$.*.A.B.C[',nn.n,'].D.*')) x 
    from xxx pr 
        cross join natural_numbers nn 
select *
from all_values a
where a.x is not null

My concerns with this are that I am building more rows than I need and then filtering out nulls. In this specific case, I think it will be very unlikely to have more than 6 elements, so I might be able to get away with limiting the process to 6 rows at most, but what is the recommended approach in general without making assumptions on the max number of elements.


I was playing around with my query and came up with this. It is much quicker than what I had before. So much so, that I think this might be the best way to do stuff like this.

with recursive array_values as (
        json_extract(pr.record_data, '$.*.A.B.C[*].D.*') val
        , pr.id
        , json_length(json_extract(pr.record_data, '$.*.A.B.C[*].D.*')) n
    from xxx pr 
), rec_elems as (
    select av.id, 0 as i, json_value(av.val, '$[0]') val
    from array_values av
    where av.n > 0 is not null
    union all
    select av.id, e.i + 1, json_value(av.val, concat('$[', e.i + 1, ']'))
    from array_values av
        inner join rec_elems e on av.id = e.id
    where (i + 1) < av.n
select * from rec_elems

I will leave the answer unaccepted for a while, in case someone else can provide an alternative.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.