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 Feb 18 '21 at 22:29 — nbk, Feb 18 '21 at 22:29

score 2 · Accepted Answer · answered Feb 18 '21 at 22:54

2

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 '21 at 22:54

Bill Karwin

538,548
86
673
828

That's it! This is perfect. It works like a charm. – user1258530 Feb 19 '21 at 03:09
Can you tell me what the query would look like if I wanted to 'insert' a new name/type into the 'parties' node? – user1258530 Feb 19 '21 at 15:46
Please ask a new question. – Bill Karwin Feb 19 '21 at 15:47
https://stackoverflow.com/questions/66288314/mysql-json-document-store-method-for-inserting-data-into-node-3-levels-deep. New question asked – user1258530 Feb 20 '21 at 05:25

mySQL JSON_TABLE query to return nested data

1 Answers1