MariaDB – How to Efficiently Emulate JSON_TABLE

jsonmariadbquery-performance

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
    union
    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.

Best Answer

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 (
    select 
        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.

Related Question