Getting all the values in json array in snowflake

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

Answer

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              |
+----+----------------+

Advertisement

Answer