Breaking News: Grepper is joining You.com. Read the official announcement!

snowflake json

Innocent Iguana answered on October 31, 2022 Popularity 3/10 Helpfulness 1/10

answer snowflake json

related to_json snowflake

snowflake json

Comment

CREATE TABLE cust_json_table(
customer_id INT,
customer_info VARIANT -- VARIANT data type which handles the json data
);

-- parse string to json format
SELECT PARSE_JSON(json_col) AS customer_info_json

-- construct JSON object
SELECT OBJECT_CONSTRUCT(
key1,val1,
key2,val2
)

-- Separate the JSON data using colon to access data from column
SELECT
customer_info:field1, 
customer_info:field2,
customer_info:field3,
FROM cust_json_table;

-- Separate the nested JSON data using consecutive colon to access data from column
SELECT customer_info:address:street AS street_name
FROM cust_json_table

-- Alternative : Separate the nested JSON data using first colon then consecutive dots to access data from column
SELECT customer_info:address.street AS street_name
FROM cust_json_table

-- Example
WITH dogs_allowed AS (
  SELECT * 
  FROM yelp_business_data
  WHERE attributes:DogsAllowed::STRING  NOT ILIKE '%None%'
),
touristy_places AS (
  SELECT *
  FROM yelp_business_data
  WHERE attributes:Ambience NOT ILIKE '%None%'
    AND attributes:Ambience NOT ILIKE '%u''%'
  	-- Convert Ambience attribute in the attributes columns into valid JSON using PARSE_JSON.
    -- From Valid JSON, fetch the touristy attribute and check if it is true when casted to BOOLEAN.
    AND PARSE_JSON(attributes:Ambience):touristy::BOOLEAN = TRUE
)

SELECT
	d.business_id,
    d.name
FROM dogs_allowed d
JOIN touristy_places t
	ON d.business_id = t.business_id

Popularity 3/10 Helpfulness 1/10 Language javascript

Source: Grepper

Tags: javascript json

Link to this answer
Share Copy Link

Contributed on Aug 13 2024

Innocent Iguana

0 Answers Avg Quality 2/10

Closely Related Answers

to_json snowflake

Comment

select v, v:food, to_json(v) from jdemo1;
+------------------+--------+-----------------+
| V                | V:FOOD | TO_JSON(V)      |
|------------------+--------+-----------------|
| {                | "bard" | {"food":"bard"} |
|   "food": "bard" |        |                 |
| }                |        |                 |
+------------------+--------+-----------------+

Popularity 8/10 Helpfulness 4/10 Language javascript

Source: docs.snowflake.com

Tags: javascript j

Link to this answer
Share Copy Link

Contributed on Oct 31 2022

Chad Liu

0 Answers Avg Quality 2/10

snowflake json

Contents

More Related Answers

snowflake json

Closely Related Answers

to_json snowflake

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.