CODEWITHSUNDEEP
mysqljson
user1258530
user1258530

Reputation: 109

mySQL JSON_TABLE query to return nested data

I want to take the data from here: https://raw.githubusercontent.com/usnistgov/oscal-content/master/examples/ssp/json/ssp-example.json

which I've pulled into a mySQL database called "ssp_models" into a JSON column called 'json_data', and I need to retrieve the 'name' and 'type' values from the 'parties' node which is nested 3 levels deep.

I've been trying to follow this blog-post about how to retrieve nested data: https://mysqlserverteam.com/json_table-the-best-of-both-worlds/

and I am struggling with the nesting selection process. Obviously this is not the right way:

SELECT "system-security-plan.*" 
FROM ssp_models, 
     JSON_TABLE(json_data, '$.metadata[*]' COLUMNS (
          NESTED PATH '$.parties[*]' COLUMNS (
                name VARCHAR(140)  PATH '$.name',
                type VARCHAR(140)  PATH '$.type')
     )) parties;

Presently, I get nothing back:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0024 seconds.)

It seems like it should be close to working, as I am pulling everything from the "system-security-plan" node and querying based on the metadata -> parties nodes to retrieve 'name' and 'type'. What am I missing?

Any assistance is greatly appreciated.

Upvotes: 3

Views: 3609

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562921

You don't need NESTED PATH because the JSON only contains objects-within-objects from the top level down to the array you want to become rows of your json table:

SELECT parties.name, parties.type
FROM ssp_models,
     JSON_TABLE(json_data, '$."system-security-plan".metadata.parties[*]' COLUMNS (
                name VARCHAR(140)  PATH '$.name',
                type VARCHAR(140)  PATH '$.type')
     ) parties

Output:

+---------------------------------+--------------+
| name                            | type         |
+---------------------------------+--------------+
| Enterprise Asset Owners         | organization |
| Enterprise Asset Administrators | organization |
| Legal Department                | organization |
| IT Department                   | organization |
| Acme Corp                       | organization |
+---------------------------------+--------------+

Upvotes: 3

Related Questions