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.
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" ]
VARIANT column, as it sounds like the OP does, you would just select from it directly rather than using PARSE_JSON.
VARCHAR column (basic string) not a VARIANT column. But with your provided solution as long as he can do an inner select within the PARSE_JSON function or find a way to do the select and pass the value as a variable to the PARSE_JSON() function it will work.