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.