CODEWITHSUNDEEP
jsonsnowflake-cloud-data-platformjson-extract
fguillen
fguillen

Reputation: 38888

Snowflake, JSON_EXTRACT_PATH_TEXT select all elements attribute in an array of objects

I have this data in a json column

#mycolumn
{
  "key": "KEY",
  "elements": [
    { "name": "NAME_1" },
    { "name": "NAME_2" }
    { "name": "NAME_3" }
  ]
}

I want to select all the elements[*].name.

I have tried:

select JSON_EXTRACT_PATH_TEXT(mycolumn, 'elements[].name')
select JSON_EXTRACT_PATH_TEXT(mycolumn, 'elements[*].name')
select JSON_EXTRACT_PATH_TEXT(mycolumn, 'elements[0,1,2].name')

Nothing works.

This works:

select JSON_EXTRACT_PATH_TEXT(mycolumn, 'elements[0].name')

But this returns only the first element.name

How can I get all the names?

Upvotes: 0

Views: 126

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

Using a higher order function TRANSFORM:

SELECT TRANSFORM(mycolumn:elements, x -> x:name) FROM t;

Output:

enter image description here

Upvotes: 1

NickW
NickW

Reputation: 9818

You need to use LATERAL FLATTEN, like this:

with jdata as (
SELECT try_PARSE_JSON(column1) AS mycolumn
FROM VALUES
('{
  "key": "KEY",
  "elements": [
    { "name": "NAME_1" },
    { "name": "NAME_2" },
    { "name": "NAME_3" }
  ]
}'
)
)
select e.value:name::string
from jdata j, LATERAL FLATTEN(INPUT => mycolumn:elements) e;

Upvotes: 1

Related Questions