LEFT JOIN between two mySQL JSON Datastore queries

Question

I have two queries that work to retrieve data from a mySQL JSON Datastore fieldtype. The trick is I need to somehow LEFT JOIN them, and the typical relational-database method doesn't seem to work.

The two queries I need to somehow join are:

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

which returns:

id  

ac-1

ac-2

ac-3

ac-7

ac-8

ac-14

ac-17

ac-18

ac-19

ac-20

ac-22

at-1

at-2

at-3

at-4

au-1

au-2

au-3

au-4

au-5

au-6

au-8

au-9

au-11

au-12

ca-1

ca-2

ca-3

ca-5

... etc

and

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

which returns:

ac-1    Policy and Procedures

ac-2    Account Management

ac-3    Access Enforcement

ac-4    Information Flow Enforcement

ac-5    Separation of Duties

ac-6    Least Privilege

ac-7    Unsuccessful Logon Attempts

ac-8    System Use Notification

ac-9    Previous Logon Notification

ac-10   Concurrent Session Control

ac-11   Device Lock

ac-12   Session Termination

ac-13   Supervision and Review — Access Control

ac-14   Permitted Actions Without Identification or Authen...

ac-15   Automated Marking

ac-16   Security and Privacy Attributes

ac-17   Remote Access

ac-18   Wireless Access

ac-19   Access Control for Mobile Devices

ac-20   Use of External Systems

ac-21   Information Sharing

ac-22   Publicly Accessible Content

ac-23   Data Mining Protection

ac-24   Access Control Decisions

ac-25   Reference Monitor

...etc

I basically want to map the id's that come from the first query, with the titles that come from the second query.

something like

SELECT controllist.id, grouplist.title FROM profiles, JSON_TABLE(json_data, 
    '$.profile.imports[0].include."id-selectors"[*]' COLUMNS (
        id VARCHAR(140) PATH '$."control-id"')
    ) controllist 
LEFT JOIN on controllist.id = (
    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)

It's not clear to me if it's a requirement that I used "JSON_TABLE" vs. just "EXTRACT" to accomplish this type of joining. But I suspect I'm way off-base with the joining process for JSON-field-types.

If you wish to install the data via SQL to see what I mean or to help experiment, it can be installed via:

This SQL script: https://implerus.com/st_overflow/profile.sql

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

I don't want to download stuff. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Mar 03 '21 at 08:25

score 0 · Answer 1 · answered Mar 03 '21 at 22:39

select t1.id, t2.title from (SELECT controllist.id FROM profiles, JSON_TABLE(json_data, '$.profile.imports[0].include.\"id-selectors\"[*]' COLUMNS (id VARCHAR(140) PATH '$.\"control-id\"')) controllist) t1, (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) t2 WHERE t1.id = t2.id

Found the answer! The trick was to not use a JOIN at all, but instead just use mySQL's native WHERE clause to identify which id's in the 2nd query to use based on the id's from the first query.

LEFT JOIN between two mySQL JSON Datastore queries

1 Answers1