CODEWITHSUNDEEP
snowflake-cloud-data-platform
Christopher Turnbull
Christopher Turnbull

Reputation: 1003

Snowflake Querying Nested JSON stored as an Array

I’ve run the following code to produce an array in a table

CREATE OR REPLACE TABLE PROVIDER_TABLE_V8 AS 
SELECT "rmpostcode", 
array_agg(object_delete(object_construct(f.*),'rmpostcode') ) within group (order by "name") AS "providers"
FROM FASTEST_DOWN_STR_V8 f
GROUP BY "rmpostcode";

I’m now trying to check the resulting table has done what I think it has... but I’m not sure how to query the resulting object. Trying lateral flatten and so on fails because it’s not JSON anymore, is it?

EDIT: Thanks @Rob Silva, I've changed the datatype to VARIANT which helped a little in the creation of this table.

The object I have is

[ { "fastest_down": "0.00", "name": "B4RN", "present": "0" }, { "fastest_down": "0.00", "name": "Gigaclear", "present": "0" }, { "fastest_down": "0.00", "name": "OFNL (IFNL)", "present": "0" }, { "fastest_down": "0.00", "name": "airband_fibre", "present": "0" }, { "fastest_down": "0.00", "name": "balquhidder", "present": "0" }, { "fastest_down": "0.00", "name": "blackfibre", "present": "0" }, ...]

That is one row.

What I'm trying to do is find all rows for which present = 1 for a specified name, but I'm struggling to query the nested JSON object.

Upvotes: 0

Views: 2191

Answers (2)

Greg Pavlik
Greg Pavlik

Reputation: 11086

Trying lateral flatten and so on fails because it’s not JSON anymore, is it?

It is. You can take it over to jsonlint.com and it validates.

Here's a breakdown of how to get this working step by step:

create table foo (v variant);

-- Insert your data
insert into foo select parse_json('<your json>');

-- See how it looks raw.
select * from foo;

-- Now flatten the array with a lateral join.
-- Note the addition of metadata columns from the
-- output of the flatten table function
select * from foo, lateral flatten(v);

-- Filter rows to where present = 1
-- Parse using the pattern COLUMN_NAME:json_property::cast_type
select * from foo, lateral flatten(v) where VALUE:present::int = 1;

-- Clean up and alias the names, etc.
select   VALUE:fastest_down::float      as FASTEST_DOWN
        ,VALUE:name::string             as "NAME"
        ,VALUE:present::int             as PRESENT
from   foo, lateral flatten(v)
-- Optionally add your where clause on PRESENT
;

Upvotes: 1

Rob Silva
Rob Silva

Reputation: 76

Chris - try using parse_json(providers) and see if the resulting object can be flattened the way you expect.

Upvotes: 0

Related Questions