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 table t, JSON_TABLE(value, '$[*]' 
    COLUMNS(
      keyId TEXT PATH '$.keyId' 
      keyNames TEXT PATH '$.keyNames',
      keyDesc TEXT PATH '$.keyDesc')
    ) AS j;

I am getting following output:

keyId                        keyNames   keyDesc
aded5b0107bb5a936604bcb6f    NULL       test

How to modify the query so as to get the values "abc1, abc2" for keyNames instead of NULL?

score 1 · Accepted Answer · answered Aug 25 '21 at 15:06

1

You need NEStED PATH for that as it is a new array

CREATE TABLE t
    ( `value` TEXT)
;
    
INSERT INTO t
    ( `value`)
VALUES
    ('[{"keyId": "aded5b0107bb5a936604bcb6f", "keyNames": ["abc1, abc2"], "keyDesc": "test"}]')

SELECT  
j.keyId, j.keyNames,  j.keyDesc

FROM t, JSON_TABLE(value, '$[*]' 
    COLUMNS(
      keyId TEXT PATH '$.keyId' 
      ,NESTED PATH '$.keyNames[*]' COLUMNS (keyNames varchar(10) PATH '$')
     , keyDesc TEXT PATH '$.keyDesc'
)
    ) AS j;

keyId                     | keyNames   | keyDesc
:------------------------ | :--------- | :------
aded5b0107bb5a936604bcb6f | abc1, abc2 | test

db<>fiddle here

answered Aug 25 '21 at 15:06

nbk

45,398
8
30
47

I am still getting `NULL` for `keyNames`, This may be because `NESTED PATH` `keyNames` is not having any column `keyNames`. can you please check? – meallhour Aug 25 '21 at 15:09
as you see in the fiddle i copied your json correctly so you can't get NULL else you have an error, please make a fiddle with your result – nbk Aug 25 '21 at 15:11
My data is exactly the same as your fiddle but I am still getting `NULL`. This is strange and not sure what is missing here – meallhour Aug 25 '21 at 15:16
Changing `varchar(10)` to `varchar(50)` has resolved the issue. However, I am two rows here. One for `abc1` and other for `abc2` – meallhour Aug 25 '21 at 15:22
then you don't have the same data see the double quotes around the hokle which makes a single string. Still the question is resoved – nbk Aug 25 '21 at 15:25
I believe it should be a small change to combine the two rows into a single row. can you please suggest what change is required here? – meallhour Aug 25 '21 at 15:38

Using JSON_TABLE to fetch a nested JSON array

1 Answers1