Create Nested JSON from Tables and Views with SQL in Snowflake

Snowflake’s native handling of JSON in both READ and WRITE operations is by far and away my favorite feature. For reading JSON I love:

• The dot notation for addressing JSON elements
  JSONDoc:Schema:Element::Cast
• The dot notation for addressing arrays
  JSONDoc:Schema[0]:”Element”::Cast
• Dot notation for nested JSON elements
  JSONDoc:Schema:NestedSchema:Element::Cast
• Lateral flattening of unbounded nested arrays
  Lateral Flatten(Input => Parse_JSON(JSONDoc:Schema:Array)
• Native storage as the Variant file type in a table, as a column
• Hybrid tables with traditional columns for common query predicates
  https://medium.com/snowflake/hybrid-relational-json-tables-in-snowflake-ec1d7676c67e
• Storing the JSON in a column in the same table with traditional columns for schema evolution
• Storing the JSON in a column in the same table with traditional columns the long tail of fields people never query

Snowflake can read and query JSON better than any SQL Language on the planet, and it’s got me hooked. I have started playing around with deeper topics on JSON write at a massive scale.

• You can turn any row or result set into an Array with
  ARRAY_CONSTRUCT(*)
• Even better you can turn any row or result set into a JSON Document with
  OBJECT_CONSTRUCT(*)
• Then there is this little gem of function for handling one to many nestings
  ARRAY_AGG(*)
• You can copy data into cloud storage of your choice using
  COPY INTO as UNLOAD
• Doing all this over billions of rows, in seconds, in AWS, GCP, or Azure all over the globe

Let’s Write Some Basic JSON
First, we will introduce the building-block functions we need to create JSON objects from Snowflake Tables & Views.

--https://docs.snowflake.com/en/sql-reference/functions/array_construct.html
SELECT ARRAY_CONSTRUCT('Foo','Bar',42);
--https://docs.snowflake.com/en/sql-reference/functions/object_construct.html
SELECT OBJECT_CONSTRUCT('Foo','Foo','Bar','Bar','Secret',42);
--https://docs.snowflake.com/en/sql-reference/functions/array_agg.html
SELECT ARRAY_AGG('Foo');

I am going to use SNOWFLAKE.ACCOUNT_USAGE.TABLES and SNOWFLAKE.ACCOUNT_USAGE.COLUMNS for this example. Snowflake Account and SysAdmins have access to this data by default. Now that we have our functions let’s write some JSON with them.

SELECT ARRAY_CONSTRUCT(*)
FROM SNOWFLAKE.ACCOUNT_USAGE.COLUMNS
WHERE TABLE_NAME IN(SELECT TABLE_NAME FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES
WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA' AND DELETED IS NULL);

Results are not JSON, NULLs are undefined, ugly.

Dave, the JSON!

SELECT OBJECT_CONSTRUCT(*)
FROM SNOWFLAKE.ACCOUNT_USAGE.COLUMNS
WHERE TABLE_NAME IN(SELECT TABLE_NAME FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES
WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA' AND DELETED IS NULL);

Results are JSON, but just for 1 Column.

Ok, Time for Nested JSON of {TABLES:[{COLUMNS}]}
I struggled with this for a couple of hours, then decided to ask SQL experts at Snowflake for some advice. Then the amazing architect Michael Rainey pointed me to his blog on Medium: https://medium.com/snowflake/generating-a-json-dataset-using-relational-data-in-snowflake-eaf3a94b7ffc This is where ARRAY_AGG() came into my life. We can feed the full list of columns for a Table into ARRAY_AGG(OBJECT_CONSTRUCT(*)).

Results in this Nested JSON.

Nesting Tables
Now we can create the full list of columns for a table along with the table attributes by querying both TABLES and COLUMNS with a CTE. It does require some manual adding of fields to the object and the group by clause, but it scales during load.

Results in this beautiful JSON.

Conclusion

With Snowflake’s unique set of JSON operators and functions, we can READ and WRITE and nest JSON in the Data Cloud for any set of tables and views you can think of. I cannot imagine doing this with XML like we had to in the early 2000s, sorry about that tech world. To learn more about querying JSON in Snowflake there is a tutorial here: https://docs.snowflake.com/en/user-guide/json-basics-tutorial.html. To understand JSON in Snowflake at a deeper level I recommend this whitepaper: https://resources.snowflake.com/ebooks/how-to-analyze-json-with-sql Happy (Nested JSON) Querying!

Links & Resources

To learn more about this and other topics, check out a few helpful resources:

• guides.snowflake.com: End-to-end tutorials about many different Snowflake topics
• developers.snowflake.com: downloadable resources for Snowflake

Originally Published on BigDataDave.com https://bigdatadave.com/2020/10/31/snowflake-sql-for-nested-json-from-tables-and-views/