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

ps- · Accepted Answer · 2021-08-25 16:56:24Z

2

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

answered Aug 25, 2021 at 16:56

ps-

2591 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related