mySQL JSON_TABLE query to return nested data

Question

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.

please make a ddl from your table, so that we can test it

nbk
– nbk

2021-02-18 22:29:52 +00:00
Commented Feb 18, 2021 at 22:29 — nbk
– nbk, Commented Feb 18, 2021 at 22:29

Bill Karwin · Accepted Answer · 2021-02-18 22:54:08Z

3

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 |
+---------------------------------+--------------+

answered Feb 18, 2021 at 22:54

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1258530 Over a year ago

That's it! This is perfect. It works like a charm.

user1258530 Over a year ago

Can you tell me what the query would look like if I wanted to 'insert' a new name/type into the 'parties' node?

Bill Karwin Over a year ago

Please ask a new question.

user1258530 Over a year ago

stackoverflow.com/questions/66288314/…. New question asked

Collectives™ on Stack Overflow

mySQL JSON_TABLE query to return nested data

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related