CODEWITHSUNDEEP
sqlarraysgetvarcharsnowflake-cloud-data-platform
Akshay Agarwal
Akshay Agarwal

Reputation: 33

How to select element from Varchar "JSON Array" in snowflake database?

I have a VARCHAR(16777216) column in a snowflake database table, that is structured as an array with JSON in it.

An example of a row in the column: [ {"Name":"John", "Last Name": "Doe"}, {"Name":"Frank", "Last Name": "Doe"}]

How do I use sql to select all of the Last Names in each row?

Please note, this is a VARCHAR COLUMN.

Upvotes: 3

Views: 5373

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33765

You can flatten the JSON array and then extract the Last Name field like this:

WITH SampleData AS (
  SELECT '[ {"Name":"John", "Last Name": "Doe"}, {"Name":"Frank", "Last Name": "Doe"}]' AS text
)
SELECT json_object.value:"Last Name" AS last_name
FROM SampleData, LATERAL FLATTEN (input => PARSE_JSON(text)) json_object;

This returns:

LAST_NAME
"Doe"
"Doe"

In the query, the LATERAL FLATTEN part is what indicates to return a row for each entry in the text after parsing it as JSON, and then in the SELECT list, json_object.value returns the value for this row, and :"Last Name" returns the field named Last Name from it. The WITH SampleData (...) part just creates some inline data with a VARCHAR column named text.

If you want a single row for each input row, where the last names are in an array, you can use a query of this form:

WITH SampleData AS (
  SELECT '[ {"Name":"John", "Last Name": "Doe"}, {"Name":"Frank", "Last Name": "Doe"}]' AS text
)
SELECT ARRAY_AGG(json_object.value:"Last Name") AS last_names
FROM SampleData, LATERAL FLATTEN (input => PARSE_JSON(text)) json_object
GROUP BY text;

This returns:

LAST_NAMES
[    "Doe",    "Doe"  ]

Upvotes: 3

Related Questions