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

Question

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?

Answer 1

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;