Getting all the values in json array in snowflake

Question

I have a JSON array as follows:

This Json data is in the field called col1 in my_table

{
"JSON_DATA": {
         "ID": 1,
         "j_array": ["A","B"]
   }
}

Here is my SQL to parse the JSON array:

SELECT col1:"JSON_DATA"."ID" as id,
       v.value as j_array_values       
from my_table, lateral flatten(input => col1."JSON_DATA"."j_array") v

My output is

|-------------|-----------------|
|     ID      | j_array_values  |
|-------------|-----------------|
|     1       |  ["A","B"]      |
|-------------|-----------------|
|     1       |  ["A","B"]      |
|-------------------------------|

But I want the output to be like

|-------------|-----------------|
|     ID      | j_array_values  |
|-------------|-----------------|
|     1       |       "A"       |
|-------------|-----------------|
|     1       |       "B"       |
|-------------------------------|

I have used used the lateral flatten but it didn't work

Lukasz Szozda · Accepted Answer · 2021-05-01 15:03:54Z

1

Using FLATTEN:

CREATE OR REPLACE TABLE my_table AS
SELECT PARSE_JSON('{"JSON_DATA": {
         "ID": 1,
         "j_array": ["A","B"]
   }
}') AS col1;


SELECT col1:JSON_DATA:ID::INT AS id,
       v.value::STRING AS j_array_values
FROM my_table, lateral flatten(input => col1:JSON_DATA:j_array) v;

Output:

+----+----------------+
| ID | j_array_values |
+----+----------------+
|  1 | A              |
|  1 | B              |
+----+----------------+

answered May 1, 2021 at 15:03

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

danidiaz Over a year ago

We could also use the path argument of flatten, like this flatten(input => col1, path => 'JSON_DATA.j_array').

Collectives™ on Stack Overflow

Getting all the values in json array in snowflake

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related