Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

I've been attempting to use MySQL 8's JSON_TABLE to extract the root keys and then their nested values. The problem is the root keys are dynamic and the nested key/value pairs might not exist.

JSON:

"Foo": { "A": 3 "Bar": { "A": 1, "B": 368 "Biz": { "C": 2, "D": 10

In this JSON the root keys "Foo", "Bar", and "Biz" are dynamic and for each of their objects I want to extract the "A" key's value, which may or may not exist. For example, the above code would return this result set:

I've been something along these lines but no luck (just returns one row of nulls):

select * from json_table('{"Foo": {"A": 3}, "Bar": {"A": 1, "B": 368}, "Biz": {"C": 2}}', 
    '$' COLUMNS(
        json_key varchar(255) path '$.*',
        sub_value integer path '$.*.A'
) as i;
In the worst case I can try to restructure the JSON but it's already in the database so I'm hoping to leverage MySQL's JSON capability. Any ideas?
SELECT json_key,
       JSON_EXTRACT(@json_value, CONCAT('$.', json_key, '.A')) a_value
FROM JSON_TABLE(JSON_KEYS(@json_value),
                '$[*]' COLUMNS (json_key VARCHAR(255) PATH '$')) keystable
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=dd9c01e77d57206d587dd2d17340bc02
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.