'How to parse the following JSON to retrieve the name of the item in snowflake sql?

 [
  {
    "itemId": "HWKDVCXKU5",
    "name": "A",
    "quantity": 1.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  },
  {
    "itemId": "FPK81M587X",
    "name": "b",
    "quantity": 1.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  },
  {
    "itemId": "L04WBQON3C",
    "name": "C",
    "quantity": 2.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  },
  {
    "itemId": "ULZFPY2UJN",
    "name": "D",
    "quantity": 2.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  },

I want all the names and quantity in 2 column, I don't know how to write query from the same.

sqljsonsnowflake-cloud-data-platform


Solution 1:[1]

So using a sub-select to "make the data" and then using PARSE_JSON and FLATTEN we get:

SELECT 
  f.value:name::text as name,
  f.value:quantity::float as quanitiy
  FROM (
      SELECT PARSE_JSON('[
  {
    "itemId": "HWKDVCXKU5",
    "name": "A",
    "quantity": 1.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  },
  {
    "itemId": "FPK81M587X",
    "name": "b",
    "quantity": 1.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  },
  {
    "itemId": "L04WBQON3C",
    "name": "C",
    "quantity": 2.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  },
  {
    "itemId": "ULZFPY2UJN",
    "name": "D",
    "quantity": 2.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  }]') as json
       ) j
  ,TABLE(FLATTEN(input=>json)) f;

which gives:

NAME QUANITIY
A 1
b 1
C 2
D 2

another way to see the exact same code but with the JSON moved to a CTE (so it looks like a normal table)


WITH data_cte AS (
    SELECT PARSE_JSON('[
  {
    "itemId": "HWKDVCXKU5",
    "name": "A",
    "quantity": 1.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  },
  {
    "itemId": "FPK81M587X",
    "name": "b",
    "quantity": 1.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  },
  {
    "itemId": "L04WBQON3C",
    "name": "C",
    "quantity": 2.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  },
  {
    "itemId": "ULZFPY2UJN",
    "name": "D",
    "quantity": 2.000000000000000e+00,
    "storeId": 1.150192000000000e+06,
    "type": "structure"
  }]') as json
)
SELECT 
  f.value:name::text as name,
  f.value:quantity::float as quanitiy
  FROM data_cte j
  ,TABLE(FLATTEN(input=>json)) f;

Solution 2:[2]

Here is my approach for your problem:

create or replace stage my_json_stage;
PUT file:///Users/athakur/Downloads/Chrome Downloads/test.json @my_json_stage auto_compress=false;

create or replace table test_json
( my_value variant);

copy into test_json from @my_json_stage/test.json
file_format = (type = JSON strip_outer_array = true)
ON_Error = Continue;

select * from test_json;
select $1:name::string, $1:quantity::string from test_json;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Simeon Pilgrim
Solution 2 Dharman

Related Questions