How to parse Json in snowflake database for adobe campaign classic v8?

[Debasmita17]

I want to parse the JSON in snowflake database, can you please provide the steps and code?

[Marcel_Szimonisz · Accepted solution]

Hello @Debasmita17 ,

I just did a bit of googling. To parse JSON data in Snowflake, you can use Snowflake's built-in JSON functions.

-- Assuming you have a table named "your_table" with a JSON column named "json_column"

-- Sample JSON data in the table
INSERT INTO your_table (json_column) VALUES
('{"name": "John", "age": 30, "city": "New York"}'),
('{"name": "Alice", "age": 25, "city": "San Francisco"}');

-- Query to parse the JSON array
SELECT
  json_data.value:age::integer AS age,
  json_data.value:name::string AS name,
  json_data.value:city::string AS city
FROM
  your_table,
  LATERAL FLATTEN(input => parse_json(json_column)) AS json_data;

Marcel