CODEWITHSUNDEEP
mysqlarraysjsonselect
meallhour
meallhour

Reputation: 15581

How to use json array to extract different values from JSON document?

I am using 8.0.18-commercial and written following MySQL query to form cross join with JSON documents .

Please note that the number of entries in servers[] is same as number of entries in objectIds[] There is meant to be one entry in servers[] corresponding to an entry in objectIds[]

SELECT t1.networkInfo->>"$.*" AS network, 
t1.objectIds,
j.server
FROM table1 t1
CROSS JOIN JSON_TABLE(t1.servers, 
                      "$[*]" COLUMNS (
                        server VARCHAR(20) PATH '$'
                        )
                      ) j
WHERE t1.id = 56

Output

network                             server                  objectIds
["abc.com", "Linux123", "RHEL"]     Server123       ["objectId123", "objectId1231", "objectId1232"]
["abc.com", "Linux123", "RHEL"]     Server1231      ["objectId123", "objectId1231", "objectId1232"]
["abc.com", "Linux123", "RHEL"]     Server1232      ["objectId123", "objectId1231", "objectId1232"]

I want to add another join in the query so that the output looks as below:

Expected Output

network                             server           objectIds
["abc.com", "Linux123", "RHEL"]     Server123        objectId123
["abc.com", "Linux123", "RHEL"]     Server1231       objectId1231
["abc.com", "Linux123", "RHEL"]     Server1232       objectId1232

Upvotes: 0

Views: 31

Answers (1)

Nick
Nick

Reputation: 147166

You can get the result you want by unpacking the objectIds column in the same way as the servers column, adding a row number column to each JSON_TABLE using FOR ORDINALITY and using that row number to JOIN the servers and objectIds tables:

SELECT t1.networkInfo->>"$.*" AS network, s.server, o.objectId
FROM table1 t1
CROSS JOIN JSON_TABLE(t1.servers, 
                      "$[*]" COLUMNS (
                        rownum FOR ORDINALITY,
                        server VARCHAR(20) PATH '$'
                        )
                      ) s
JOIN JSON_TABLE(t1.objectIds, 
                "$[*]" COLUMNS (
                  rownum FOR ORDINALITY,
                  objectId VARCHAR(20) PATH '$'
                  )
                ) o ON o.rownum = s.rownum
WHERE t1.id = 56

Output:

network                             server      objectId
["abc.com", "Linux123", "RHEL"]     Server123   objectId123
["abc.com", "Linux123", "RHEL"]     Server1231  objectId1231
["abc.com", "Linux123", "RHEL"]     Server1232  objectId1232

Demo on dbfiddle

Upvotes: 1

Related Questions