Is there a way to search for an element inside a JSON array using sql in snowflake?-CodePudding

I have a column in a table which contains, for each row, a JSONarray. I need to extract certain the same elements from it for each row, however, as it is an array, the order of the elements inside the array is not always the same and I can't call these elements by their names.Is there a way for me to do a for loop or something similar that goes through every index of the array and when it doesn't return null it breaks?

CodePudding user response：

There is no need to know the size of array:

CREATE OR REPLACE TABLE tab_name
AS
SELECT 1 AS id, PARSE_JSON('[1,2,3]') AS col_array
UNION ALL
SELECT 2 AS id, PARSE_JSON('[1]') AS col_array;

Query:

SELECT t.id
   ,f.INDEX
   ,f.VALUE
FROM tab_name t
, LATERAL FLATTEN(INPUT => t.col_array) f
-- WHERE f.VALUE::INT = 1;

Output:

CodePudding user response：

Lateral flatten can help extract the fields of a JSON object and is a very good alternative to extracting them one by one using the respective names. However, sometimes the JSON object can be nested and normally extracting those nested objects requires knowing their names.

Here is an article that might help you to DYNAMICALLY EXTRACT THE FIELDS OF A MULTI-LEVEL JSON OBJECT USING LATERAL FLATTEN

CodePudding user response：

An extension to Lukasz's great answer:

With a CTE with a couple of rows of "id, json" we can see how FLATTEN pulls it apart:

WITH fake_data(id, json) as (
    SELECT column1, parse_json(column2) FROM VALUES
        (1, '[1,2,3]'),
        (2, '{"4":4, "5":5}')
)
SELECT t.*
   ,f.*
FROM fake_data AS t
    ,LATERAL FLATTEN(INPUT => t.json) f

ID	JSON	SEQ	KEY	PATH	INDEX	VALUE	THIS
1	[ 1, 2, 3 ]	1		[0]	0	1	[ 1, 2, 3 ]
1	[ 1, 2, 3 ]	1		[1]	1	2	[ 1, 2, 3 ]
1	[ 1, 2, 3 ]	1		[2]	2	3	[ 1, 2, 3 ]
2	{ "4": 4, "5": 5 }	2	4	['4']		4	{ "4": 4, "5": 5 }
2	{ "4": 4, "5": 5 }	2	5	['5']		5	{ "4": 4, "5": 5 }

The Flatten gives seq, key, path, index, value and this

Seq : is the row of the input, which is super useful if you are pulling rows apart and want to merge them back together, but not mix up different rows.

Key : is the name of the property if the thing being FLATTEN'ed was an object, which is the case for the second row.

Path : is how that value could be accessed. aka t.json[2] would with you 3

Index : is the step into the object if it's an array

Value: is the value

This: is the thing that getting looped, useful for get things like the next one, etc.