read JSON with repeating structure

Moritz
SQL-Fighter

Hi,

consider a table with the column Forename. The entries look like this:

{names:[{"forename":"Adam"},{"forename":"Otto"},{"forename":"Klaus"}]}

I wonder how to extract all the forenames into one column having a result like this: Adam, Otto, Klaus.

The JSON_EXTRACT function may be the right choice here, but I do not know how to specify the JSON path correctly.

Do you guys have an idea how to solve this? Any help would be much appreciated!

Best

Moritz

2 ACCEPTED SOLUTIONS

Accepted Solutions

exa-Carsten
Moderator
Moderator

Hi @Moritz ,

the statement

SELECT json_extract('{"names":[{"forename":"Adam"},{"forename":"Otto"},{"forename":"Klaus"}]}', '$.names.forename#') EMITS ("forename" VARCHAR(100));

will return 

forename
Adam
Otto
Klaus

Is this what you're looking for?

View solution in original post

exa-Carsten
Moderator
Moderator
SELECT json_extract('{"names":[{"forename":"Adam", "lastname":"Jabra"},{"forename":"Otto", "lastname":"Minawa"},{"forename":"Klaus","lastname":"Lindt"}]}', '$.names#.forename','$.names#.lastname' ) EMITS ("forename" VARCHAR(100), "lastname" VARCHAR(100));

does the trick. Actually, you have to add the '#' to the array "names". It worked above as there was only one attribute per object in the array.

View solution in original post

6 REPLIES 6

exa-Carsten
Moderator
Moderator

Hi @Moritz ,

the statement

SELECT json_extract('{"names":[{"forename":"Adam"},{"forename":"Otto"},{"forename":"Klaus"}]}', '$.names.forename#') EMITS ("forename" VARCHAR(100));

will return 

forename
Adam
Otto
Klaus

Is this what you're looking for?

View solution in original post

Moritz
SQL-Fighter

Hi Carsten,

Thank you very much for your quick reply, it is much appreciated.
This helps a lot, I was missing the '#'.

Best 

Moritz

exa-Carsten
Moderator
Moderator

The '#' also works, if you have an object with multiple identical keys:

SELECT json_extract('{"names":{"forename":"Adam", "forename":"Otto", "forename":"Klaus"}}', '$.names.forename#') EMITS ("forename" VARCHAR(100));

 

Moritz
SQL-Fighter

Hi,

In the following case, I added a further attribute "lastname" into the JSON file.
Emitting either  "forename" or "lastname" works, however I cannot emit both of them using the pathing from above.
It says "invalid JSON path: All nested path prefixes ending with a '#' character must be a prefix of the longest nested path."

Please find the underlying select statement here:

SELECT json_extract('{"names":[{"forename":"Adam", "lastname":"Jabra"},{"forename":"Otto", "lastname":"Minawa"},{"forename":"Klaus","lastname":"Lindt"}]}',
'$.names.forename#','$.names.lastname#' )
EMITS ("forename" VARCHAR(100), "lastname" VARCHAR(100));

Any ideas how to emit both "forename" and "lastname"?

Best 

Moritz

exa-Carsten
Moderator
Moderator
SELECT json_extract('{"names":[{"forename":"Adam", "lastname":"Jabra"},{"forename":"Otto", "lastname":"Minawa"},{"forename":"Klaus","lastname":"Lindt"}]}', '$.names#.forename','$.names#.lastname' ) EMITS ("forename" VARCHAR(100), "lastname" VARCHAR(100));

does the trick. Actually, you have to add the '#' to the array "names". It worked above as there was only one attribute per object in the array.

View solution in original post

Moritz
SQL-Fighter

Great, thank you very much!