How to combine 'NESTED PATH` values using JSON_TABLE to fetch a nested JSON array?

Question

I have a Json array while looks as below

[{"keyId": "aded5b0107bb5a936604bcb6f", "keyNames": ["abc1, abc2"], "keyDesc": "test"}]

I am using JSON_TABLE to fetch all the values and I have written the following query:

SELECT j.keyId, j.keyNames, j.keyDesc
FROM asknow a, JSON_TABLE(value, '$[*]' 
    COLUMNS(
      keyId TEXT PATH '$.keyId',
      NESTED PATH '$.keyNames[*]' COLUMNS (keyNames TEXT PATH '$'),
      keyDesc TEXT PATH '$.keyDesc')
    ) AS j;

I am getting following output:

keyId                        keyNames   keyDesc
aded5b0107bb5a936604bcb6f    abc1       test
aded5b0107bb5a936604bcb6f    abc2       test

How to modify the query so as to combine the values into a single string "abc1, abc2"?

Desired Output:

keyId                        keyNames   keyDesc
aded5b0107bb5a936604bcb6f    abc1, abc2       test

score 2 · Accepted Answer · answered Aug 25 '21 at 16:56

You can combine json_table columns with group_concat() to group multiple rows extracted from the inner array.

SELECT j.keyId, group_concat(distinct j.keyNames) as keyNames, j.keyDesc
FROM JSON_TABLE(@temp, '$[*]' 
    COLUMNS(
      keyId TEXT PATH '$.keyId',
      NESTED PATH '$.keyNames[*]' COLUMNS (keyNames TEXT PATH '$'),
      keyDesc TEXT PATH '$.keyDesc')
    ) AS j
group by j.keyId, j.keyDesc;

I am not sure why you have used asknow a table in your query, so ignoring it for now. I have used MySQL 8.0.

db<>fiddle

How to combine 'NESTED PATH` values using JSON_TABLE to fetch a nested JSON array?

1 Answers1