How to JOIN multiple MySQL JSON tables to produce JSON output for collapsing/expanding nav-tree?

Question

I've successfully completed a number of MySQL JSON Document Store-esque type queries, but now I am at a point where I have a very complex query that is beyond my scope of expertise. Any help is greatly appreciated.

Goal: To create a json feed from MySQL that will allow this navigation-tree to be rendered:

Steps to produce:

Install this SQL script: https://implerus.com/st_overflow/profile.sql

And then install this SQL script: https://implerus.com/st_overflow/oscal_rev5.sql

This will create 2 tables: "profiles", and "oscal_rev5" with json data being placed in the "json_data" column of each table.

Now if you want a quick look at what json data is being pulled in to those json_data columns, here's the data:

https://www.implerus.com/st_overflow/profile_data.json

https://www.implerus.com/st_overflow/OSCAL_Rev5_Latest.json

So, here's the goal:

1. Write a mySQL JSON query that pulls in the "control-id"'s from the profile (json_data) table. It should return "ac-1", "ac-2", "ac-3", "ac-6.7", "ac-6.9", "ac-7", etc.

I can show you a query that I'd think should work, but doesn't:

SELECT control.controlid FROM profiles, JSON_TABLE(json_data, '$.profile.imports.include."id-selectors[*]"' COLUMNS (controlid VARCHAR(10) PATH '$."control-id"')) control

I get no results back from that and I don't know why. I suspect it has something to do with the pathing and the double-quotes with the hyphens, but from what I've read, that is how you are supposed to support queries with hyphens.

2. Then, somehow left-join those entries with the oscal_rev5 json_data to retrieve all the titles for the "ac-1", "ac-2", "ac-3", "ac-6.7" etc values and return data as json so that (in this case ReactJS) will be able to draw that nav-tree above as a Bootstrap Accordion. So the query should return the level 1 parts of the nav ("Access Control", "Awareness and Training", "Audit and Accountibility", etc, along with their "child" nodes that originate from step 1 above.

I do have a working query that retrieves the "parent node" list:

SELECT grouplist.id, grouplist.title FROM oscal_rev5, JSON_TABLE(json_data, '$.catalog.groups[*]' COLUMNS (id VARCHAR(140) PATH '$.id',class VARCHAR(20) PATH '$.class',title VARCHAR(80) PATH '$.title')) grouplist

And I have a failed attempt at returning the sub-parent controls list:

SELECT grouplist.id, grouplist.title FROM oscal_rev5, JSON_TABLE(json_data, '$.catalog.groups.controls[*]' COLUMNS (id VARCHAR(140) PATH '$.id',class VARCHAR(20) PATH '$.class',title VARCHAR(80) PATH '$.title')) grouplist

Returns nothing, and I don't know why. You'd think that just bumping down a node in the path would work to get id, class, title data with "$.catalog.groups.controls[*]" but it fails with no error.

Notice in the OSCAL data that "groups"->"controls"->"id": "ac-1" has a title of "Policy and Procedures". And then notice that "groups"->"controls"->"id": "ac-2" has a title of "Account Management". And notice that "groups"->"controls"->"id": "ac-6.7" has a title of "Review of User Privilages". That's all good data to return for level 2 of the nav-tree.

And since all those "ac-1", "ac-2", "ac-6.7" etc entries all start with "ac", then they all need to wrap under the "parent" "ac" node - - - and that node, according to the same oscal json data has a title called "Access Control". Likewise, all the returned values from step one that begin with "at" would need to fall under the "Awareness and Training" parent in the results.

3. Then, somehow count() all the subnav elements in the nav-tree that fall under "Access Control", "Awareness and Training", "Audit and Accountibility", etc and provide that data within the mySQL results.

So, the big question is - - - is there a way to combine all 3 steps into one mySQL JSON query and get 1 set of results back that distinguishes between the parent-nav-nodes and their children so that it can later be output as JSON and used to drive the nav-tree UI.