Welcome To TechBrothersIT: Snowflake SQL Tutorial 93 - OBJECT Functions - CONSTRUCT, KEYS, INSERT, DELETE, PICK, AGG

Snowflake SQL Tutorial 93 - OBJECT Functions - CONSTRUCT, KEYS, INSERT, DELETE, PICK, AGG

Snowflake SQL · Episode 93

In this post we are going to cover the OBJECT functions in Snowflake: OBJECT_CONSTRUCT, OBJECT_KEYS, OBJECT_INSERT, OBJECT_DELETE, OBJECT_PICK and OBJECT_AGG.

Everything in this level so far has been about reading JSON that somebody else produced. These functions go the other way. They build documents and change documents, and they are how you write JSON out of Snowflake rather than pull it apart.

We will start with the objects we already have and build it up step by step. The first thing we find is that one NULL column in a perfectly square table is enough to produce documents of two different shapes.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 93 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's look at the objects we already have
Let's start by building an object
Now let's put a NULL in as a value
Now let's watch that rule do real damage
Let's do the same build with KEEP_NULL
OBJECT_KEYS comes back sorted, so it is a shape
And which keys are missing somewhere?
Now let's add a key with OBJECT_INSERT
Now let's take one away with OBJECT_DELETE
Let's keep only what we want with OBJECT_PICK
And the capital letter from Episode 88
Now let's build one object out of many rows
So let's aggregate first, then build
One last thing: OBJECT_AGG is quiet about a NULL

Let's look at the objects we already have

Two tables on this page. EVENTS_JSON is the four JSON documents from the last three posts, and CUSTOMERS is an ordinary relational table we are going to turn into JSON. Copy this into a worksheet and run it once.

Sample data

-- Two tables on this page. EVENTS_JSON is the four JSON documents this level
-- has been using. CUSTOMERS is a plain relational table, and it is square
-- except for one missing country, which turns out to matter a great deal.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EVENTS_JSON (
    EVENT_ID  INT,
    PAYLOAD   VARIANT
);

INSERT INTO EVENTS_JSON (EVENT_ID, PAYLOAD)
SELECT 1, PARSE_JSON('{
  "event": "purchase",
  "amount": 4200,
  "device": { "os": "windows", "type": "desktop" },
  "user":   { "id": 101, "name": "Northwind Traders" },
  "items":  [ { "sku": "A-100", "qty": 8, "price": 300 },
              { "sku": "B-220", "qty": 4, "price": 450 } ],
  "tags":   [ "priority", "repeat" ]
}')
UNION ALL SELECT 2, PARSE_JSON('{
  "event": "purchase",
  "amount": 890.5,
  "device": { "os": "ios", "type": "mobile" },
  "user":   { "id": 102, "name": "Blue Ridge Supply" },
  "items":  [ { "sku": "C-310", "qty": 3, "price": 296.83 } ],
  "tags":   [ "new" ]
}')
UNION ALL SELECT 3, PARSE_JSON('{
  "event": "refund",
  "amount": "1500.00",
  "user":   { "id": 103, "name": "Kestrel Analytics" },
  "items":  [ { "sku": "A-100", "qty": 5, "price": 300 } ],
  "tags":   []
}')
UNION ALL SELECT 4, PARSE_JSON('{
  "event": "purchase",
  "amount": 9250,
  "device": { "os": "android", "type": "tablet" },
  "user":   { "id": 107, "name": "Meridian Health" },
  "items":  [ { "sku": "D-400", "qty": 12, "price": 500 },
              { "sku": "A-100", "qty": 10, "price": 300 },
              { "sku": "E-505", "qty": 8,  "price": 156.25 } ],
  "tags":   [ "priority", "enterprise", "repeat" ]
}');

CREATE OR REPLACE TABLE CUSTOMERS (
    CUSTOMER_ID    INT,
    CUSTOMER_NAME  VARCHAR,
    CITY           VARCHAR,
    COUNTRY        VARCHAR,
    SEGMENT        VARCHAR
);

INSERT INTO CUSTOMERS VALUES
    (101, 'Northwind Traders', 'Charlotte',  'USA',    'Enterprise'),
    (102, 'Blue Ridge Supply', 'Asheville',  'USA',    'SMB'),
    (103, 'Kestrel Analytics', 'Toronto',    'Canada', 'Enterprise'),
    (104, 'Harbour Foods',     'Vancouver',  'Canada', 'SMB'),
    (105, 'Alpine Logistics',  'Zurich',      NULL,    'Enterprise'),
    (106, 'Delta Print Co',    'Raleigh',    'USA',    'SMB'),
    (107, 'Meridian Health',   'Boston',     'USA',    'Enterprise'),
    (108, 'Copper Kettle Ltd', 'Manchester', 'UK',     'SMB'),
    (109, 'Vantage Media',     'London',     'UK',     'Enterprise'),
    (110, 'Sable Interiors',   'Austin',     'USA',    'SMB');

Here are the objects inside those four documents, with the top-level keys beside them.

Query

SELECT EVENT_ID,
       PAYLOAD:user                                AS THE_USER,
       PAYLOAD:device                              AS THE_DEVICE,
       OBJECT_KEYS(PAYLOAD)                        AS TOP_LEVEL_KEYS,
       ARRAY_SIZE(OBJECT_KEYS(PAYLOAD))            AS HOW_MANY_KEYS
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 5 columns

EVENT_ID	THE_USER	THE_DEVICE	TOP_LEVEL_KEYS	HOW_MANY_KEYS
1	{ "id": 101, "name": "Northwind Traders" }	{ "os": "windows", "type": "desktop" }	[ "amount", "device", "event", "items", "tags", "user" ]	6
2	{ "id": 102, "name": "Blue Ridge Supply" }	{ "os": "ios", "type": "mobile" }	[ "amount", "device", "event", "items", "tags", "user" ]	6
3	{ "id": 103, "name": "Kestrel Analytics" }	NULL	[ "amount", "event", "items", "tags", "user" ]	5
4	{ "id": 107, "name": "Meridian Health" }	{ "os": "android", "type": "tablet" }	[ "amount", "device", "event", "items", "tags", "user" ]	6

HOW_MANY_KEYS reads 6, 6, 5, 6. Event 3 is a key short, and THE_DEVICE on that row is NULL.

And here is the table we will be building documents out of.

Query

SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY, COUNTRY, SEGMENT
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 5 columns

CUSTOMER_ID	CUSTOMER_NAME	CITY	COUNTRY	SEGMENT
101	Northwind Traders	Charlotte	USA	Enterprise
102	Blue Ridge Supply	Asheville	USA	SMB
103	Kestrel Analytics	Toronto	Canada	Enterprise
104	Harbour Foods	Vancouver	Canada	SMB
105	Alpine Logistics	Zurich	NULL	Enterprise
106	Delta Print Co	Raleigh	USA	SMB
107	Meridian Health	Boston	USA	Enterprise
108	Copper Kettle Ltd	Manchester	UK	SMB
109	Vantage Media	London	UK	Enterprise
110	Sable Interiors	Austin	USA	SMB

Ten rows and five columns, and every cell is filled in except one. Customer 105, Alpine Logistics, has no country. Keep an eye on that row.

Let's start by building an object

OBJECT_CONSTRUCT takes key, value, key, value, and so on. Let's build a few shapes.

Query

SELECT OBJECT_CONSTRUCT('sku', 'A-100', 'qty', 8)          AS TWO_PAIRS,
       OBJECT_CONSTRUCT()                                   AS EMPTY_OBJECT,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_CONSTRUCT()))          AS EMPTY_KEY_COUNT,
       OBJECT_CONSTRUCT('a', 1, 'b', OBJECT_CONSTRUCT('c', 2)) AS NESTED,
       OBJECT_CONSTRUCT('a', ARRAY_CONSTRUCT(1, 2))         AS WITH_AN_ARRAY;

Result1 row · 5 columns

TWO_PAIRS	EMPTY_OBJECT	EMPTY_KEY_COUNT	NESTED	WITH_AN_ARRAY
{ "qty": 8, "sku": "A-100" }	{}	0	{ "a": 1, "b": { "c": 2 } }	{ "a": [ 1, 2 ] }

We wrote 'sku' first and 'qty' second, and TWO_PAIRS came back with qty first. The keys are in order, not in the order we typed them. That is going to turn out to be useful.

OBJECT_CONSTRUCT() with no arguments gives an empty object, and its key count is 0. Objects nest, and an array goes inside an object quite happily.

Now let's miscount the arguments and leave a key without a value.

Query

SELECT OBJECT_CONSTRUCT('sku', 'A-100', 'qty') AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

000909 (22023): SQL compilation error: error line 1 at position 7
invalid number of arguments for [OBJECT_CONSTRUCT], expected 4, got 3

A compile error that says exactly what it counted. Good error: you cannot ship past it.

Now let's put a NULL in as a value

This is the one that matters. Let's build three pairs where the middle value is a SQL NULL, and build the same thing again with the other constructor.

Query

SELECT OBJECT_CONSTRUCT('a', 1, 'b', NULL, 'c', 3)                   AS WITH_A_NULL,
       OBJECT_KEYS(OBJECT_CONSTRUCT('a', 1, 'b', NULL, 'c', 3))       AS ITS_KEYS,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_CONSTRUCT('a', 1, 'b', NULL, 'c', 3)))
         AS HOW_MANY_KEYS_SURVIVED,
       OBJECT_CONSTRUCT_KEEP_NULL('a', 1, 'b', NULL, 'c', 3)          AS KEEP_NULL,
       ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_CONSTRUCT_KEEP_NULL('a', 1, 'b', NULL, 'c', 3)))      AS KEEP_NULL_KEYS;

Result1 row · 5 columns

WITH_A_NULL	ITS_KEYS	HOW_MANY_KEYS_SURVIVED	KEEP_NULL	KEEP_NULL_KEYS
{ "a": 1, "c": 3 }	[ "a", "c" ]	2	{ "a": 1, "b": null, "c": 3 }	3

WITH_A_NULL came back holding a and c. There is no b key set to null. The b key is gone, and HOW_MANY_KEYS_SURVIVED is 2.

So a NULL value does not store a null. It takes the key with it.

OBJECT_CONSTRUCT_KEEP_NULL is the version that does not do that. Same three pairs, KEEP_NULL_KEYS is 3, and b comes back as null.

Now let's try a JSON null instead of a SQL NULL, with the plain constructor.

Query

SELECT OBJECT_CONSTRUCT('a', 1, 'b', PARSE_JSON('null'))               AS WITH_JSON_NULL,
       ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_CONSTRUCT('a', 1, 'b', PARSE_JSON('null'))))           AS ITS_KEY_COUNT,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_CONSTRUCT('a', 1, 'b', NULL)))    AS SQL_NULL_KEY_COUNT;

Result1 row · 3 columns

WITH_JSON_NULL	ITS_KEY_COUNT	SQL_NULL_KEY_COUNT
{ "a": 1, "b": null }	2	1

It survived. ITS_KEY_COUNT is 2 where the same build with a SQL NULL gives 1.

The difference in one line

A JSON null is a value. A SQL NULL is an absence, and OBJECT_CONSTRUCT will not write a pair for something that is not there. That is defensible, and it is also the cause of everything in the next section.

Now let's watch that rule do real damage

Let's build one document per customer out of four columns: name, city, country and segment.

Query

SELECT CUSTOMER_ID,
       COUNTRY,
       OBJECT_CONSTRUCT('name', CUSTOMER_NAME, 'city', CITY,
                        'country', COUNTRY, 'segment', SEGMENT)  AS THE_DOCUMENT,
       ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_CONSTRUCT('name', CUSTOMER_NAME, 'city', CITY,
                          'country', COUNTRY, 'segment', SEGMENT))) AS KEY_COUNT
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 4 columns

CUSTOMER_ID	COUNTRY	THE_DOCUMENT	KEY_COUNT
101	USA	{ "city": "Charlotte", "country": "USA", "name": "Northwind Traders", "segment": "Enterprise" }	4
102	USA	{ "city": "Asheville", "country": "USA", "name": "Blue Ridge Supply", "segment": "SMB" }	4
103	Canada	{ "city": "Toronto", "country": "Canada", "name": "Kestrel Analytics", "segment": "Enterprise" }	4
104	Canada	{ "city": "Vancouver", "country": "Canada", "name": "Harbour Foods", "segment": "SMB" }	4
105	NULL	{ "city": "Zurich", "name": "Alpine Logistics", "segment": "Enterprise" }	3
106	USA	{ "city": "Raleigh", "country": "USA", "name": "Delta Print Co", "segment": "SMB" }	4
107	USA	{ "city": "Boston", "country": "USA", "name": "Meridian Health", "segment": "Enterprise" }	4
108	UK	{ "city": "Manchester", "country": "UK", "name": "Copper Kettle Ltd", "segment": "SMB" }	4
109	UK	{ "city": "London", "country": "UK", "name": "Vantage Media", "segment": "Enterprise" }	4
110	USA	{ "city": "Austin", "country": "USA", "name": "Sable Interiors", "segment": "SMB" }	4

Go down the KEY_COUNT column and find the exception. Customer 105 has 3 keys where every other row has 4. Look at its document: there is no country key at all.

So a square table has just produced documents of two different shapes. Let's count that properly.

Query

SELECT COUNT(*)                                                     AS ROWS_TOTAL,
       COUNT(DISTINCT ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_CONSTRUCT('name', CUSTOMER_NAME, 'city', CITY,
                          'country', COUNTRY, 'segment', SEGMENT)))) AS DISTINCT_SHAPES,
       MIN(ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_CONSTRUCT('name', CUSTOMER_NAME, 'city', CITY,
                          'country', COUNTRY, 'segment', SEGMENT)))) AS FEWEST_KEYS,
       MAX(ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_CONSTRUCT('name', CUSTOMER_NAME, 'city', CITY,
                          'country', COUNTRY, 'segment', SEGMENT)))) AS MOST_KEYS,
       COUNT_IF(COUNTRY IS NULL)                                    AS NULL_COUNTRIES
FROM CUSTOMERS;

Result1 row · 5 columns

ROWS_TOTAL	DISTINCT_SHAPES	FEWEST_KEYS	MOST_KEYS	NULL_COUNTRIES
10	2	3	4	1

Ten rows in, 2 distinct shapes out. The fewest keys on any document is 3 and the most is 4. And NULL_COUNTRIES is 1, which is the cause sitting right next to the effect.

Where ragged JSON comes from

This is the ragged-document problem that Episodes 86 to 91 were about detecting. Here we have made it ourselves, out of a table that had nothing wrong with it, with one function call and no warning of any kind.

Let's do the same build with KEEP_NULL

Same four columns, same ten rows, one word different in the function name.

Query

SELECT COUNT(*)                                                     AS ROWS_TOTAL,
       COUNT(DISTINCT ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_CONSTRUCT_KEEP_NULL('name', CUSTOMER_NAME, 'city', CITY,
                          'country', COUNTRY, 'segment', SEGMENT)))) AS DISTINCT_SHAPES,
       MIN(ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_CONSTRUCT_KEEP_NULL('name', CUSTOMER_NAME, 'city', CITY,
                          'country', COUNTRY, 'segment', SEGMENT)))) AS FEWEST_KEYS,
       MAX(ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_CONSTRUCT_KEEP_NULL('name', CUSTOMER_NAME, 'city', CITY,
                          'country', COUNTRY, 'segment', SEGMENT)))) AS MOST_KEYS
FROM CUSTOMERS;

Result1 row · 4 columns

ROWS_TOTAL	DISTINCT_SHAPES	FEWEST_KEYS	MOST_KEYS
10	1	4	4

DISTINCT_SHAPES is 1 now, and the fewest and the most are both 4. One NULL column was the whole difference between one shape and two.

So what does the kept null actually read back as? Let's look at the filled row and the empty one side by side.

Query

SELECT CUSTOMER_ID,
       OBJECT_CONSTRUCT_KEEP_NULL('country', COUNTRY):country       AS AS_VARIANT,
       OBJECT_CONSTRUCT_KEEP_NULL('country', COUNTRY):country IS NULL AS IS_SQL_NULL,
       IS_NULL_VALUE(OBJECT_CONSTRUCT_KEEP_NULL('country', COUNTRY):country)
         AS IS_JSON_NULL,
       TYPEOF(OBJECT_CONSTRUCT_KEEP_NULL('country', COUNTRY):country) AS ITS_TYPE
FROM CUSTOMERS
WHERE CUSTOMER_ID IN (101, 105)
ORDER BY CUSTOMER_ID;

Result2 rows · 5 columns

CUSTOMER_ID	AS_VARIANT	IS_SQL_NULL	IS_JSON_NULL	ITS_TYPE
101	"USA"	FALSE	FALSE	VARCHAR
105	null	FALSE	TRUE	NULL_VALUE

On customer 105, IS_SQL_NULL is FALSE, IS_JSON_NULL is TRUE, and ITS_TYPE is NULL_VALUE.

That is the right answer to have stored. The document now says we asked and there was no country, which is a different statement from this document has no country field. Only one of those two can be told apart from a field somebody forgot to write.

OBJECT_KEYS comes back sorted, so it is a shape

OBJECT_KEYS gives you the keys of an object as an array. The useful thing about it is the order.

Let's parse the same three keys written in two different orders and compare what comes out.

Query

SELECT OBJECT_KEYS(PARSE_JSON('{"zebra":1,"apple":2,"mango":3}'))  AS KEYS_FROM_ZAM,
       OBJECT_KEYS(PARSE_JSON('{"apple":2,"mango":3,"zebra":1}'))  AS KEYS_FROM_AMZ,
       OBJECT_KEYS(PARSE_JSON('{"zebra":1,"apple":2,"mango":3}'))
         = OBJECT_KEYS(PARSE_JSON('{"apple":2,"mango":3,"zebra":1}')) AS SAME_KEYS;

Result1 row · 3 columns

KEYS_FROM_ZAM	KEYS_FROM_AMZ	SAME_KEYS
[ "apple", "mango", "zebra" ]	[ "apple", "mango", "zebra" ]	TRUE

Both came back as apple, mango, zebra, and SAME_KEYS is TRUE. Two documents with the same keys compare equal no matter how they were written.

Which means you can group by it. Let's run a census of the shapes in our four documents.

Query

SELECT OBJECT_KEYS(PAYLOAD)                     AS THE_SHAPE,
       ARRAY_SIZE(OBJECT_KEYS(PAYLOAD))         AS KEY_COUNT,
       COUNT(*)                                 AS DOCUMENTS,
       ARRAY_AGG(EVENT_ID)                      AS WHICH_EVENTS
FROM EVENTS_JSON
GROUP BY OBJECT_KEYS(PAYLOAD)
ORDER BY DOCUMENTS DESC;

Result2 rows · 4 columns

THE_SHAPE	KEY_COUNT	DOCUMENTS	WHICH_EVENTS
[ "amount", "device", "event", "items", "tags", "user" ]	6	3	[ 1, 2, 4 ]
[ "amount", "event", "items", "tags", "user" ]	5	1	[ 3 ]

Two shapes. The six-key shape covers three documents, events 1, 2 and 4. The five-key shape covers one, event 3. That is the ragged one, found with a GROUP BY.

And as a single value

An array is awkward to carry round. Let's join the keys into text and hash it.

Query

SELECT EVENT_ID,
       ARRAY_TO_STRING(OBJECT_KEYS(PAYLOAD), ',')            AS SHAPE_AS_TEXT,
       HASH(ARRAY_TO_STRING(OBJECT_KEYS(PAYLOAD), ','))      AS SHAPE_HASH,
       ARRAY_SIZE(OBJECT_KEYS(PAYLOAD))                      AS KEY_COUNT
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 4 columns

EVENT_ID	SHAPE_AS_TEXT	SHAPE_HASH	KEY_COUNT
1	amount,device,event,items,tags,user	-613220675326540692	6
2	amount,device,event,items,tags,user	-613220675326540692	6
3	amount,event,items,tags,user	-2473700466049126795	5
4	amount,device,event,items,tags,user	-613220675326540692	6

Events 1, 2 and 4 share one SHAPE_AS_TEXT and one SHAPE_HASH. Event 3 has its own. So one column tells you the shape of a document, which you can store, index or compare between two tables.

And which keys are missing somewhere?

A shape census tells you how many shapes there are. It does not tell you which field is the optional one. For that, flatten the key array and count each key.

Query

SELECT K.VALUE::VARCHAR                      AS THE_KEY,
       COUNT(DISTINCT E.EVENT_ID)            AS PRESENT_IN,
       (SELECT COUNT(*) FROM EVENTS_JSON)    AS EVENTS_TOTAL,
       COUNT(DISTINCT E.EVENT_ID) = (SELECT COUNT(*) FROM EVENTS_JSON)
         AS PRESENT_EVERYWHERE
FROM EVENTS_JSON E,
     LATERAL FLATTEN(INPUT => OBJECT_KEYS(E.PAYLOAD)) K
GROUP BY K.VALUE
ORDER BY PRESENT_IN, THE_KEY;

Result6 rows · 4 columns

THE_KEY	PRESENT_IN	EVENTS_TOTAL	PRESENT_EVERYWHERE
device	3	4	FALSE
amount	4	4	TRUE
event	4	4	TRUE
items	4	4	TRUE
tags	4	4	TRUE
user	4	4	TRUE

Six keys came back. device is present in three events out of four, with PRESENT_EVERYWHERE FALSE. Every other key is in all four.

That is your optional field list, and it took one query. Episode 91 did the same job with a recursive flatten and the PATH column, which goes deeper. This form stays at the top level and is easier to read.

Now let's add a key with OBJECT_INSERT

OBJECT_INSERT takes an object, a key and a value, and gives you a new object. Let's add a tier to the user object.

Query

SELECT PAYLOAD:user                                        AS BEFORE_,
       OBJECT_INSERT(PAYLOAD:user, 'tier', 'gold')         AS AFTER_,
       ARRAY_SIZE(OBJECT_KEYS(PAYLOAD:user))               AS KEYS_BEFORE,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_INSERT(PAYLOAD:user, 'tier', 'gold')))
         AS KEYS_AFTER
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 4 columns

BEFORE_	AFTER_	KEYS_BEFORE	KEYS_AFTER
{ "id": 101, "name": "Northwind Traders" }	{ "id": 101, "name": "Northwind Traders", "tier": "gold" }	2	3

Two keys before, three after. Nothing surprising yet.

Now let's insert a key that is already in the object.

Query

SELECT OBJECT_INSERT(PAYLOAD:user, 'name', 'Overwritten') AS SHOULD_NOT_GET_THIS_FAR
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Query failed — Snowflake returned an error

100103 (22000): Duplicate field key 'name'

It refuses, and names the key. That is the right default. An insert that silently overwrote would be a very quiet way to lose data.

There is a fourth argument for when you do mean to overwrite. Let's pass TRUE.

Query

SELECT PAYLOAD:user.name::VARCHAR                                  AS NAME_BEFORE,
       OBJECT_INSERT(PAYLOAD:user, 'name', 'Overwritten', TRUE)     AS AFTER_,
       OBJECT_INSERT(PAYLOAD:user, 'name', 'Overwritten', TRUE):name::VARCHAR
         AS NAME_AFTER,
       ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_INSERT(PAYLOAD:user, 'name', 'Overwritten', TRUE)))  AS KEY_COUNT
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 4 columns

NAME_BEFORE	AFTER_	NAME_AFTER	KEY_COUNT
Northwind Traders	{ "id": 101, "name": "Overwritten" }	Overwritten	2

NAME_BEFORE and NAME_AFTER show the change, and KEY_COUNT is still 2. It replaced rather than added.

And what does that flag do on a key that does not exist yet?

Query

SELECT OBJECT_INSERT(PAYLOAD:user, 'brand_new', 1, TRUE)             AS RESULT_,
       ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_INSERT(PAYLOAD:user, 'brand_new', 1, TRUE)))         AS KEY_COUNT
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 2 columns

RESULT_	KEY_COUNT
{ "brand_new": 1, "id": 101, "name": "Northwind Traders" }	3

Nothing harmful. KEY_COUNT is 3, so it just added the key. The flag means overwriting is allowed, not overwriting is required.

And inserting a null?

You can probably guess, but let's check it rather than guess.

Query

SELECT OBJECT_INSERT(PAYLOAD:user, 'tier', NULL)                     AS WITH_SQL_NULL,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_INSERT(PAYLOAD:user, 'tier', NULL)))
         AS KEYS_AFTER_SQL_NULL,
       OBJECT_INSERT(PAYLOAD:user, 'tier', PARSE_JSON('null'))        AS WITH_JSON_NULL,
       ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_INSERT(PAYLOAD:user, 'tier', PARSE_JSON('null'))))    AS KEYS_AFTER_JSON_NULL
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 4 columns

WITH_SQL_NULL	KEYS_AFTER_SQL_NULL	WITH_JSON_NULL	KEYS_AFTER_JSON_NULL
{ "id": 101, "name": "Northwind Traders" }	2	{ "id": 101, "name": "Northwind Traders", "tier": null }	3

With a SQL NULL, KEYS_AFTER_SQL_NULL is 2. The insert did not happen at all. With a JSON null it is 3, and tier comes back as null.

Same rule as OBJECT_CONSTRUCT. A NULL value means no pair.

Now let's take one away with OBJECT_DELETE

OBJECT_DELETE is the other direction. Let's drop a key from the user object.

Query

SELECT PAYLOAD:user                                      AS BEFORE_,
       OBJECT_DELETE(PAYLOAD:user, 'name')               AS AFTER_,
       ARRAY_SIZE(OBJECT_KEYS(PAYLOAD:user))             AS KEYS_BEFORE,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_DELETE(PAYLOAD:user, 'name'))) AS KEYS_AFTER
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 4 columns

BEFORE_	AFTER_	KEYS_BEFORE	KEYS_AFTER
{ "id": 101, "name": "Northwind Traders" }	{ "id": 101 }	2	1

Two keys became one.

Now let's delete a key that is not in the object.

Query

SELECT OBJECT_DELETE(PAYLOAD:user, 'never_existed')                  AS RESULT_,
       ARRAY_SIZE(OBJECT_KEYS(PAYLOAD:user))                         AS KEYS_BEFORE,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_DELETE(PAYLOAD:user, 'never_existed')))
         AS KEYS_AFTER,
       OBJECT_DELETE(PAYLOAD:user, 'never_existed') = PAYLOAD:user    AS UNCHANGED
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 4 columns

RESULT_	KEYS_BEFORE	KEYS_AFTER	UNCHANGED
{ "id": 101, "name": "Northwind Traders" }	2	2	TRUE

Nothing happened and nothing complained. UNCHANGED is TRUE, which is a comparison of the result against the original rather than a count, so it is a stronger statement than the key counts beside it.

It takes several keys at once as well. Let's strip both objects out of every document, including the one that is missing device.

Query

SELECT EVENT_ID,
       ARRAY_SIZE(OBJECT_KEYS(PAYLOAD))                              AS KEYS_BEFORE,
       OBJECT_DELETE(PAYLOAD, 'user', 'device')                      AS STRIPPED,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_DELETE(PAYLOAD, 'user', 'device')))
         AS KEYS_AFTER
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 4 columns

EVENT_ID	KEYS_BEFORE	STRIPPED	KEYS_AFTER
1	6	{ "amount": 4200, "event": "purchase", "items": [ { "price": 300, "qty": 8, "sku": "A-100" }, { "price": 450, "qty": 4, "sku": "B-220" } ], "tags": [ "priority", "repeat" ] }	4
2	6	{ "amount": 890.5, "event": "purchase", "items": [ { "price": 296.83, "qty": 3, "sku": "C-310" } ], "tags": [ "new" ] }	4
3	5	{ "amount": "1500.00", "event": "refund", "items": [ { "price": 300, "qty": 5, "sku": "A-100" } ], "tags": [] }	4
4	6	{ "amount": 9250, "event": "purchase", "items": [ { "price": 500, "qty": 12, "sku": "D-400" }, { "price": 300, "qty": 10, "sku": "A-100" }, { "price": 156.25, "qty": 8, "sku": "E-505" } ], "tags": [ "priority", "enterprise", "repeat" ] }	4

KEYS_BEFORE reads 6, 6, 5, 6 and KEYS_AFTER reads 4 on every row. The ragged document came out the same shape as the other three, because deleting a key that is not there is a no-op.

So OBJECT_DELETE can be used to normalise a set of documents rather than just to remove a field. Here is the same function used as a deny list, this time taking out the two array keys and showing what is left.

Query

SELECT EVENT_ID,
       OBJECT_KEYS(OBJECT_DELETE(PAYLOAD, 'items', 'tags'))          AS WHAT_IS_LEFT,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_DELETE(PAYLOAD, 'items', 'tags')))
         AS HOW_MANY_LEFT
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 3 columns

EVENT_ID	WHAT_IS_LEFT	HOW_MANY_LEFT
1	[ "amount", "device", "event", "user" ]	4
2	[ "amount", "device", "event", "user" ]	4
3	[ "amount", "event", "user" ]	3
4	[ "amount", "device", "event", "user" ]	4

HOW_MANY_LEFT is 4 on three rows and 3 on event 3, which never had the device key the others kept. A deny list preserves whatever raggedness was already there.

Let's keep only what we want with OBJECT_PICK

OBJECT_PICK is the allow-list version. You name the keys you want instead of the ones you do not.

Query

SELECT EVENT_ID,
       OBJECT_PICK(PAYLOAD, 'event', 'amount')                       AS PICKED,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_PICK(PAYLOAD, 'event', 'amount')))
         AS PICKED_KEY_COUNT,
       OBJECT_PICK(PAYLOAD, 'event', 'device')                       AS PICK_A_MISSING_ONE,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_PICK(PAYLOAD, 'event', 'device')))
         AS COUNT_WITH_A_MISSING_KEY
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 5 columns

EVENT_ID	PICKED	PICKED_KEY_COUNT	PICK_A_MISSING_ONE	COUNT_WITH_A_MISSING_KEY
1	{ "amount": 4200, "event": "purchase" }	2	{ "device": { "os": "windows", "type": "desktop" }, "event": "purchase" }	2
2	{ "amount": 890.5, "event": "purchase" }	2	{ "device": { "os": "ios", "type": "mobile" }, "event": "purchase" }	2
3	{ "amount": "1500.00", "event": "refund" }	2	{ "event": "refund" }	1
4	{ "amount": 9250, "event": "purchase" }	2	{ "device": { "os": "android", "type": "tablet" }, "event": "purchase" }	2

Picking event and amount gives 2 keys on every row, because both keys are on every document.

Now look at the last column. Picking event and device gives 2 keys on three rows and 1 on event 3.

So OBJECT_PICK does not invent a key to satisfy the list. It picks what is there, and an allow list gives you ragged output from ragged input just as readily as a deny list does.

It takes an array of keys as well as a list of arguments, which matters when the key list is built rather than typed.

Query

SELECT OBJECT_PICK(PAYLOAD, ARRAY_CONSTRUCT('event', 'amount'))       AS PICKED_BY_ARRAY,
       OBJECT_PICK(PAYLOAD, ARRAY_CONSTRUCT('event', 'amount'))
         = OBJECT_PICK(PAYLOAD, 'event', 'amount')                    AS SAME_AS_VARARGS
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 2 columns

PICKED_BY_ARRAY	SAME_AS_VARARGS
{ "amount": 4200, "event": "purchase" }	TRUE

SAME_AS_VARARGS is TRUE. The two forms give the same object.

And the capital letter from Episode 88

Episode 88 left a question open. JSON keys are case sensitive, so GET(PAYLOAD, 'Amount') finds nothing on a document whose key is amount. Let's line up the exact spelling, the wrong spelling, and three spellings through GET_IGNORE_CASE.

Query

SELECT GET(PAYLOAD, 'amount')                     AS EXACT_CASE,
       GET(PAYLOAD, 'Amount')                     AS WRONG_CASE,
       GET_IGNORE_CASE(PAYLOAD, 'Amount')         AS IGNORING_CASE,
       GET_IGNORE_CASE(PAYLOAD, 'AMOUNT')         AS SHOUTING,
       GET_IGNORE_CASE(PAYLOAD, 'aMoUnT')         AS NONSENSE_CASE
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 5 columns

EXACT_CASE	WRONG_CASE	IGNORING_CASE	SHOUTING	NONSENSE_CASE
4200	NULL	4200	4200	4200

EXACT_CASE found the value. WRONG_CASE is NULL. And the three GET_IGNORE_CASE columns all found it, however the key was spelled.

Let's count the two over the whole table so there is a number rather than one row.

Query

SELECT COUNT(*)                                  AS ROWS_TOTAL,
       COUNT(GET(PAYLOAD, 'amount'))             AS EXACT_CASE_FOUND,
       COUNT(GET(PAYLOAD, 'Amount'))             AS WRONG_CASE_FOUND,
       COUNT(GET_IGNORE_CASE(PAYLOAD, 'Amount')) AS IGNORE_CASE_FOUND
FROM EVENTS_JSON;

Result1 row · 4 columns

ROWS_TOTAL	EXACT_CASE_FOUND	WRONG_CASE_FOUND	IGNORE_CASE_FOUND
4	4	0	4

4 rows, exact case found 4, wrong case found 0, ignoring case found 4.

Worth knowing, not worth defaulting to

GET_IGNORE_CASE is the answer to the capital letter, and it is not a free one. It has to consider every key in the object rather than look one up. Use it when you do not control the producer, and fix the spelling when you do.

Now let's build one object out of many rows

OBJECT_AGG is the aggregate. It takes a key column and a value column and gives you one object. Let's try country and customer id.

Query

SELECT OBJECT_AGG(COUNTRY, CUSTOMER_ID::VARIANT) AS SHOULD_NOT_GET_THIS_FAR
FROM CUSTOMERS;

Query failed — Snowflake returned an error

100103 (22000): Duplicate field key 'USA'

It refuses, and names the key it tripped on. Five of our ten customers are in that country, and an object cannot hold the same key twice.

Let's try the segment column instead.

Query

SELECT OBJECT_AGG(SEGMENT, CUSTOMER_ID::VARIANT) AS SHOULD_NOT_GET_THIS_FAR
FROM CUSTOMERS;

Query failed — Snowflake returned an error

100103 (22000): Duplicate field key 'Enterprise'

Same refusal, different key. Now let's use a column that actually is unique.

Query

SELECT ARRAY_SIZE(OBJECT_KEYS(OBJECT_AGG(CUSTOMER_ID::VARCHAR,
                                         CUSTOMER_NAME::VARIANT)))  AS KEYS_BUILT,
       COUNT(*)                                                     AS ROWS_IN,
       COUNT(DISTINCT CUSTOMER_ID)                                  AS DISTINCT_KEYS
FROM CUSTOMERS;

Result1 row · 3 columns

KEYS_BUILT	ROWS_IN	DISTINCT_KEYS
10	10	10

Ten keys from ten rows, with ten distinct values in the key column. That is the condition, and it is worth checking before you run the aggregate rather than after.

Query

SELECT COUNT(*)                          AS ROWS_TOTAL,
       COUNT(DISTINCT CUSTOMER_ID)       AS DISTINCT_CUSTOMER_ID,
       COUNT(DISTINCT COUNTRY)           AS DISTINCT_COUNTRY,
       COUNT(DISTINCT SEGMENT)           AS DISTINCT_SEGMENT,
       COUNT(DISTINCT CUSTOMER_ID) = COUNT(*)  AS CUSTOMER_ID_IS_SAFE,
       COUNT(DISTINCT COUNTRY) = COUNT(*)      AS COUNTRY_IS_SAFE,
       COUNT(DISTINCT SEGMENT) = COUNT(*)      AS SEGMENT_IS_SAFE
FROM CUSTOMERS;

Result1 row · 7 columns · scroll →

ROWS_TOTAL	DISTINCT_CUSTOMER_ID	DISTINCT_COUNTRY	DISTINCT_SEGMENT	CUSTOMER_ID_IS_SAFE	COUNTRY_IS_SAFE	SEGMENT_IS_SAFE
10	10	3	2	TRUE	FALSE	FALSE

One query, three candidate keys. The customer id is safe. Country has 3 distinct values over 10 rows and segment has 2, so both are FALSE.

A loud failure for once

OBJECT_AGG raising on a duplicate key is the correct behaviour, and it is a nicer failure than most things on this page: it happens at run time, it names the key, and it happens on the first duplicate. Put the COUNT(DISTINCT ...) check in front of it and you never meet it.

So let's aggregate first, then build

If the column you wanted as a key is not unique, make it unique before OBJECT_AGG sees it. Group first and aggregate the values.

Query

SELECT OBJECT_AGG(COUNTRY, N::VARIANT)                        AS CUSTOMERS_BY_COUNTRY,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_AGG(COUNTRY, N::VARIANT))) AS KEYS_BUILT
FROM (SELECT COUNTRY, COUNT(*) AS N
      FROM CUSTOMERS
      WHERE COUNTRY IS NOT NULL
      GROUP BY COUNTRY);

Result1 row · 2 columns

CUSTOMERS_BY_COUNTRY	KEYS_BUILT
{ "Canada": 2, "UK": 2, "USA": 5 }	3

One key per country with the count as its value, and KEYS_BUILT is 3.

A count is a thin value. Let's keep the ids instead, as an array per key.

Query

SELECT OBJECT_AGG(COUNTRY, IDS)                               AS IDS_BY_COUNTRY,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_AGG(COUNTRY, IDS)))      AS KEYS_BUILT
FROM (SELECT COUNTRY, ARRAY_AGG(CUSTOMER_ID) AS IDS
      FROM CUSTOMERS
      WHERE COUNTRY IS NOT NULL
      GROUP BY COUNTRY);

Result1 row · 2 columns

IDS_BY_COUNTRY	KEYS_BUILT
{ "Canada": [ 103, 104 ], "UK": [ 108, 109 ], "USA": [ 101, 102, 106, 107, 110 ] }	3

Same three keys, much richer values. ARRAY_AGG inside OBJECT_AGG is a pattern worth remembering, and we met ARRAY_AGG in Episode 92.

Now let's check that the grouping did not lose anybody.

Query

SELECT (SELECT COUNT(*) FROM CUSTOMERS)                        AS CUSTOMERS_TOTAL,
       (SELECT COUNT_IF(COUNTRY IS NULL) FROM CUSTOMERS)       AS NULL_COUNTRY,
       (SELECT COUNT(*) FROM CUSTOMERS WHERE COUNTRY IS NOT NULL)
         AS WITH_A_COUNTRY,
       (SELECT SUM(ARRAY_SIZE(IDS)) FROM
          (SELECT ARRAY_AGG(CUSTOMER_ID) AS IDS FROM CUSTOMERS
           WHERE COUNTRY IS NOT NULL GROUP BY COUNTRY))         AS IDS_IN_THE_OBJECT;

Result1 row · 4 columns

CUSTOMERS_TOTAL	NULL_COUNTRY	WITH_A_COUNTRY	IDS_IN_THE_OBJECT
10	1	9	9

Ten customers, one with no country, nine with one, and nine ids inside the object. The arithmetic closes, and the one customer who is not in there is the one the WHERE clause excluded on purpose.

The same pattern works on any table. Here it is over ORDERS, counting by status.

Query

SELECT OBJECT_AGG(STATUS, N::VARIANT)                        AS STATUS_COUNTS,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_AGG(STATUS, N::VARIANT))) AS HOW_MANY_KEYS
FROM (SELECT STATUS, COUNT(*) AS N FROM ORDERS GROUP BY STATUS);

Result1 row · 2 columns

STATUS_COUNTS	HOW_MANY_KEYS
{ "CANCELLED": 2, "PENDING": 3, "RETURNED": 2, "SHIPPED": 34 }	4

Four keys, one per status, with the order count as the value. SHIPPED carries 34 of them.

One last thing: OBJECT_AGG is quiet about a NULL

OBJECT_AGG shouts about a duplicate key. It says nothing at all about a NULL one. Let's pick four customers whose countries are all different, one of which is missing.

Query

SELECT OBJECT_AGG(COUNTRY, CUSTOMER_ID::VARIANT)                        AS RESULT_,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_AGG(COUNTRY, CUSTOMER_ID::VARIANT))) AS KEYS_BUILT,
       COUNT(*)                                                         AS ROWS_IN,
       COUNT(COUNTRY)                                                   AS NON_NULL_KEYS
FROM CUSTOMERS
WHERE CUSTOMER_ID IN (101, 103, 105, 108);

Result1 row · 4 columns

RESULT_	KEYS_BUILT	ROWS_IN	NON_NULL_KEYS
{ "Canada": 103, "UK": 108, "USA": 101 }	3	4	3

ROWS_IN is 4 and KEYS_BUILT is 3. No duplicate, no error, and one row simply is not in the result. NON_NULL_KEYS is the number that matches the output.

And a NULL value does the same thing. Let's key on the customer id, which is unique, and put the country in as the value.

Query

SELECT OBJECT_AGG(CUSTOMER_ID::VARCHAR, COUNTRY::VARIANT)              AS COUNTRY_BY_ID,
       ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_AGG(CUSTOMER_ID::VARCHAR, COUNTRY::VARIANT)))           AS KEYS_BUILT,
       COUNT(*)                                                        AS ROWS_IN,
       COUNT(COUNTRY)                                                  AS NON_NULL_VALUES
FROM CUSTOMERS;

Result1 row · 4 columns

COUNTRY_BY_ID	KEYS_BUILT	ROWS_IN	NON_NULL_VALUES
{ "101": "USA", "102": "USA", "103": "Canada", "104": "Canada", "106": "USA", "107": "USA", "108": "UK", "109": "UK", "110": "USA" }	9	10	9

Ten rows in, 9 keys out. Read the object and you will not find customer 105 anywhere in it.

Let's put the two numbers next to each other and subtract them, because that is the check worth keeping.

Query

SELECT COUNT(*)                                                        AS ROWS_IN,
       COUNT(COUNTRY)                                                  AS NON_NULL_VALUES,
       ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_AGG(CUSTOMER_ID::VARCHAR, COUNTRY::VARIANT)))           AS KEYS_OUT,
       COUNT(*) - ARRAY_SIZE(OBJECT_KEYS(
         OBJECT_AGG(CUSTOMER_ID::VARCHAR, COUNTRY::VARIANT)))           AS PAIRS_MISSING
FROM CUSTOMERS;

Result1 row · 4 columns

ROWS_IN	NON_NULL_VALUES	KEYS_OUT	PAIRS_MISSING
10	9	9	1

PAIRS_MISSING is 1. No error, no warning, and nothing in the result hints that it should have had another pair in it. If you build an object out of rows, count the keys yourself.

And a clean round trip

To finish, let's take an object apart with FLATTEN and put it straight back together with OBJECT_AGG.

Query

SELECT PAYLOAD:user                                               AS ORIGINAL,
       OBJECT_AGG(F.KEY, F.VALUE)                                 AS REBUILT,
       PAYLOAD:user = OBJECT_AGG(F.KEY, F.VALUE)                  AS IDENTICAL,
       ARRAY_SIZE(OBJECT_KEYS(PAYLOAD:user))                      AS KEYS_IN,
       ARRAY_SIZE(OBJECT_KEYS(OBJECT_AGG(F.KEY, F.VALUE)))        AS KEYS_OUT
FROM EVENTS_JSON E,
     LATERAL FLATTEN(INPUT => E.PAYLOAD:user) F
WHERE E.EVENT_ID = 1
GROUP BY E.PAYLOAD:user;

Result1 row · 5 columns

ORIGINAL	REBUILT	IDENTICAL	KEYS_IN	KEYS_OUT
{ "id": 101, "name": "Northwind Traders" }	{ "id": 101, "name": "Northwind Traders" }	TRUE	2	2

IDENTICAL is TRUE, with 2 keys in and 2 keys out. FLATTEN and OBJECT_AGG are inverses on an object, which is handy when you want to change a few values in the middle of the trip.

Key points

OBJECT_CONSTRUCT drops the whole pair when the value is NULL, not just the value. That is how a square table produces ragged documents.
OBJECT_CONSTRUCT_KEEP_NULL is the version that does not, and the kept value reads back as a JSON null: IS_NULL_VALUE TRUE and TYPEOF NULL_VALUE.
A JSON null survives where a SQL NULL does not, because a JSON null is a value.
OBJECT_KEYS comes back sorted, so it works as a shape. GROUP BY OBJECT_KEYS(...) is a census and HASH(ARRAY_TO_STRING(...)) is a one-column fingerprint.
Flatten the key array and count each key to get the optional field list.
OBJECT_INSERT refuses a key that already exists. A fourth argument of TRUE overwrites, and it is harmless on a key that is new.
Inserting a SQL NULL value does nothing at all. The key count does not move.
OBJECT_DELETE takes several keys, does nothing to a key that is not there, and can therefore normalise a ragged set of documents.
OBJECT_PICK is the allow list, and a key the document does not have simply yields fewer keys rather than a null one.
GET_IGNORE_CASE is the answer to the case-sensitive key Episode 88 ran into.
OBJECT_AGG raises on a duplicate key and is silent about a NULL one. Check the key with COUNT(DISTINCT ...) first, and compare rows in against keys out afterwards.
FLATTEN and OBJECT_AGG are inverses on an object.

Next in this series

Episode 94 — TYPEOF, the IS_ family and the AS_ family. Three ways to ask what is inside a VARIANT, one of which is the safe numeric read that TRY_CAST refused to give us in Episode 89.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. The EVENTS_JSON, CUSTOMERS and ORDERS rows are invented demo data, and the missing country on customer 105 is there on purpose. Every count in the text above is read off one of the grids on this page rather than worked out here, and no function is named in the text that does not appear in the query beside it. Nothing is timed and no performance claims are made anywhere in this series. Function behaviour follows the Snowflake semi-structured function documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 93 - OBJECT Functions - CONSTRUCT, KEYS, INSERT, DELETE, PICK, AGG

Let's look at the objects we already have

Let's start by building an object

Now let's put a NULL in as a value

Now let's watch that rule do real damage

Let's do the same build with KEEP_NULL

OBJECT_KEYS comes back sorted, so it is a shape

And as a single value

And which keys are missing somewhere?

Now let's add a key with OBJECT_INSERT

And inserting a null?

Now let's take one away with OBJECT_DELETE

Let's keep only what we want with OBJECT_PICK

And the capital letter from Episode 88

Now let's build one object out of many rows

So let's aggregate first, then build

One last thing: OBJECT_AGG is quiet about a NULL

And a clean round trip

Key points

No comments:

Post a Comment