Best Practices / JSON / Snowflake / SnowSQL

Snowflake: Create Nested JSON from Tables and Views with SQL

Posted by

Snowflake’s native handling of JSON in both READ and WRITE operations is by far and away my favourite 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 variant file type in a table, as a column
  • Hybrid tables with traditional columns for common query predicates
  • 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 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.

[
  4186182,
  "QUEUED_OVERLOAD_TIME",
  507422,
  "TRANSIENT_IS_QUERY_HISTORY",
  29,
  "PUBLIC",
  27,
  "SALES",
  26,
  undefined,
  "YES",
  "NUMBER",
  undefined,
  undefined,
  38,
  10,
  0,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  "NO",
  "NO",
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  undefined,
  "2020-10-31 02:52:18.188 -0700"
]

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.

{
  "COLUMN_ID": 3956141,
  "COLUMN_NAME": "BYTES_SCANNED",
  "DATA_TYPE": "NUMBER",
  "DELETED": "2020-10-19 23:12:15.898 -0700",
  "IS_IDENTITY": "NO",
  "IS_NULLABLE": "YES",
  "IS_SELF_REFERENCING": "NO",
  "NUMERIC_PRECISION": 38,
  "NUMERIC_PRECISION_RADIX": 10,
  "NUMERIC_SCALE": 0,
  "ORDINAL_POSITION": 20,
  "TABLE_CATALOG": "SALES",
  "TABLE_CATALOG_ID": 27,
  "TABLE_ID": 485434,
  "TABLE_NAME": "TRANSIENT_INSERTS",
  "TABLE_SCHEMA": "PUBLIC",
  "TABLE_SCHEMA_ID": 29
}

Ok, Time for Nested JSON of {TABLES:[{COLUMNS}]}
I struggled with this for a couple 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(*)).

--Single Table Version
WITH 
    COLUMNS_JSON AS (
        SELECT
            TABLE_CATALOG,
            TABLE_SCHEMA,
            TABLE_NAME,
            COLUMN_NAME,
            TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || TABLE_NAME AS TABLE_PATH,
            TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || TABLE_NAME || '.' || COLUMN_NAME AS COLUMN_PATH,
            DATA_TYPE,
            IS_NULLABLE,
            IS_IDENTITY,
            CHARACTER_MAXIMUM_LENGTH,
            NUMERIC_PRECISION,
            NUMERIC_SCALE,
            ORDINAL_POSITION,
            COMMENT
        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) 
            AND DELETED IS NULL
            ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION)
SELECT OBJECT_CONSTRUCT(
            'TABLE',COLUMNS_JSON.TABLE_PATH,
            'COLUMNS',ARRAY_AGG(OBJECT_CONSTRUCT(*)))
FROM COLUMNS_JSON
GROUP BY COLUMNS_JSON.TABLE_PATH
ORDER BY COLUMNS_JSON.TABLE_PATH;

Results in this Nested JSON.

{
  "COLUMNS": [
    {
      "COLUMN_NAME": "CAL_DATE",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.CAL_DATE",
      "DATA_TYPE": "DATE",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 1,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "YEAR",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.YEAR",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 2,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "MONTH",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.MONTH",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 3,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "CHARACTER_MAXIMUM_LENGTH": 16777216,
      "COLUMN_NAME": "MONTH_NAME",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.MONTH_NAME",
      "DATA_TYPE": "TEXT",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 4,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "QUARTER",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.QUARTER",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 5,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "CHARACTER_MAXIMUM_LENGTH": 16777216,
      "COLUMN_NAME": "QUARTER_NAME",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.QUARTER_NAME",
      "DATA_TYPE": "TEXT",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 6,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "CHARACTER_MAXIMUM_LENGTH": 16777216,
      "COLUMN_NAME": "QUARTER_NAME_FULL",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.QUARTER_NAME_FULL",
      "DATA_TYPE": "TEXT",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 7,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "DAY_OF_MON",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.DAY_OF_MON",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 8,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "CHARACTER_MAXIMUM_LENGTH": 16777216,
      "COLUMN_NAME": "DAY_OF_WEEK",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.DAY_OF_WEEK",
      "DATA_TYPE": "TEXT",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 9,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "WEEK_OF_YEAR",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.WEEK_OF_YEAR",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 10,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "DAY_OF_YEAR",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.DAY_OF_YEAR",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 11,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "CHARACTER_MAXIMUM_LENGTH": 16777216,
      "COLUMN_NAME": "CREATE_PROCESS",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.CREATE_PROCESS",
      "DATA_TYPE": "TEXT",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 12,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "CREATE_TS",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.CREATE_TS",
      "DATA_TYPE": "TIMESTAMP_NTZ",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 13,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    }
  ],
  "TABLE": "ANALYTICS.PUBLIC.CALENDAR_DATE"
}

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 performance.

--2 Tables, Nested, BAM!
WITH 
    TABLES_JSON AS (
        SELECT TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || TABLE_NAME AS TABLE_PATH,
            TABLE_CATALOG,
            TABLE_SCHEMA,
            TABLE_NAME,
            TABLE_OWNER,
            TABLE_TYPE,
            CLUSTERING_KEY,
            ROW_COUNT,
            BYTES,
            CREATED,
            AUTO_CLUSTERING_ON,
            COMMENT
        FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES
        WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA' AND DELETED IS NULL),
    COLUMNS_JSON AS (
        SELECT
            TABLE_CATALOG,
            TABLE_SCHEMA,
            TABLE_NAME,
            COLUMN_NAME,
            TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || TABLE_NAME AS TABLE_PATH,
            TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || TABLE_NAME || '.' || COLUMN_NAME AS COLUMN_PATH,
            DATA_TYPE,
            IS_NULLABLE,
            IS_IDENTITY,
            CHARACTER_MAXIMUM_LENGTH,
            NUMERIC_PRECISION,
            NUMERIC_SCALE,
            ORDINAL_POSITION,
            COMMENT
        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) 
            AND DELETED IS NULL
        ORDER BY
            TABLE_SCHEMA,
            TABLE_NAME,
            ORDINAL_POSITION)
SELECT OBJECT_CONSTRUCT(
            'TABLE',TABLES_JSON.TABLE_PATH,
            'TABLE_CATALOG', TABLES_JSON.TABLE_CATALOG,
            'TABLE_SCHEMA', TABLES_JSON.TABLE_SCHEMA,
            'TABLE_NAME', TABLES_JSON.TABLE_NAME,
            'TABLE_OWNER',TABLES_JSON.TABLE_OWNER,
            'TABLE_TYPE',TABLES_JSON.TABLE_TYPE,
            'CLUSTERING_KEY',TABLES_JSON.CLUSTERING_KEY,
            'ROW_COUNT',TABLES_JSON.ROW_COUNT,
            'BYTES',TABLES_JSON.BYTES,
            'CREATED',TABLES_JSON.CREATED,
            'AUTO_CLUSTERING_ON',TABLES_JSON.AUTO_CLUSTERING_ON,
            'COMMENT',TABLES_JSON.COMMENT,
            'COLUMNS',ARRAY_AGG(OBJECT_CONSTRUCT(COLUMNS_JSON.*)))
FROM COLUMNS_JSON
JOIN TABLES_JSON ON TABLES_JSON.TABLE_PATH = COLUMNS_JSON.TABLE_PATH
GROUP BY
    TABLES_JSON.TABLE_PATH,
    TABLES_JSON.TABLE_CATALOG,
    TABLES_JSON.TABLE_SCHEMA,
    TABLES_JSON.TABLE_NAME,
    TABLES_JSON.TABLE_OWNER,
    TABLES_JSON.TABLE_TYPE,
    TABLES_JSON.CLUSTERING_KEY,
    TABLES_JSON.ROW_COUNT,
    TABLES_JSON.BYTES,
    TABLES_JSON.CREATED,
    TABLES_JSON.AUTO_CLUSTERING_ON,
    TABLES_JSON.COMMENT
ORDER BY TABLES_JSON.TABLE_PATH
;

Results in this beautiful JSON.

{
  "AUTO_CLUSTERING_ON": "NO",
  "BYTES": 105472,
  "COLUMNS": [
    {
      "COLUMN_NAME": "CAL_DATE",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.CAL_DATE",
      "DATA_TYPE": "DATE",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 1,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "YEAR",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.YEAR",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 2,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "MONTH",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.MONTH",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 3,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "CHARACTER_MAXIMUM_LENGTH": 16777216,
      "COLUMN_NAME": "MONTH_NAME",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.MONTH_NAME",
      "DATA_TYPE": "TEXT",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 4,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "QUARTER",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.QUARTER",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 5,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "CHARACTER_MAXIMUM_LENGTH": 16777216,
      "COLUMN_NAME": "QUARTER_NAME",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.QUARTER_NAME",
      "DATA_TYPE": "TEXT",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 6,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "CHARACTER_MAXIMUM_LENGTH": 16777216,
      "COLUMN_NAME": "QUARTER_NAME_FULL",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.QUARTER_NAME_FULL",
      "DATA_TYPE": "TEXT",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 7,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "DAY_OF_MON",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.DAY_OF_MON",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 8,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "CHARACTER_MAXIMUM_LENGTH": 16777216,
      "COLUMN_NAME": "DAY_OF_WEEK",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.DAY_OF_WEEK",
      "DATA_TYPE": "TEXT",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 9,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "WEEK_OF_YEAR",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.WEEK_OF_YEAR",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 10,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "DAY_OF_YEAR",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.DAY_OF_YEAR",
      "DATA_TYPE": "NUMBER",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "NUMERIC_PRECISION": 38,
      "NUMERIC_SCALE": 0,
      "ORDINAL_POSITION": 11,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "CHARACTER_MAXIMUM_LENGTH": 16777216,
      "COLUMN_NAME": "CREATE_PROCESS",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.CREATE_PROCESS",
      "DATA_TYPE": "TEXT",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 12,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    },
    {
      "COLUMN_NAME": "CREATE_TS",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.CREATE_TS",
      "DATA_TYPE": "TIMESTAMP_NTZ",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 13,
      "TABLE_CATALOG": "ANALYTICS",
      "TABLE_NAME": "CALENDAR_DATE",
      "TABLE_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE",
      "TABLE_SCHEMA": "PUBLIC"
    }
  ],
  "CREATED": "2020-02-14 11:35:38.463 -0800",
  "ROW_COUNT": 14000,
  "TABLE": "ANALYTICS.PUBLIC.CALENDAR_DATE",
  "TABLE_CATALOG": "ANALYTICS",
  "TABLE_NAME": "CALENDAR_DATE",
  "TABLE_OWNER": "ETL",
  "TABLE_SCHEMA": "PUBLIC",
  "TABLE_TYPE": "BASE TABLE"
}

Conclusion
With Snowflake’s unique set of 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. Happy (Nested JSON) Querying!

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s