Welcome To TechBrothersIT: Snowflake SQL Tutorial 92 - ARRAY Functions

Snowflake SQL Tutorial 92 - ARRAY Functions - SIZE, CONTAINS, SLICE, CAT, SORT, AGG

Snowflake SQL · Episode 92

In this post we are going to cover the ARRAY functions in Snowflake: building arrays, measuring them, searching them, slicing them, joining them, sorting them, and building one out of rows.

An array inside a VARIANT is not much use until you can ask questions about it. These are the functions that ask. Almost none of them raise an error, which sounds like a good thing and is the reason this post is long: you have to know what their quiet answers mean.

We will start with the arrays we already have and build it up step by step. On the way we will meet one argument order that returns FALSE on every row and never says a word about it.

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

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

On this page

Let's look at the arrays we have
Let's start by building one
Now let's drop that NULL with COMPACT
Now let's measure them with ARRAY_SIZE
What happens when we aggregate that size?
Let's search an array with ARRAY_CONTAINS
Now let's swap the two arguments
What does CONTAINS say about a key that is not there?
Let's find where a value sits with ARRAY_POSITION
Let's take a piece out with ARRAY_SLICE
Now let's join two arrays with ARRAY_CAT
Can we cast our way out of it?
Now let's sort one with ARRAY_SORT
Now the set operations
Do these functions need the cast at all?
A few more worth knowing
One last thing: let's build an array out of rows

Let's look at the arrays we have

This post runs against the same four JSON documents as the last two, plus the ORDERS table at the very end. Copy this into a worksheet and run it once.

Sample data

-- Two tables on this page. EVENTS_JSON holds four JSON documents and is the
-- table this whole level runs against. ORDERS is the plain relational table
-- the course has used since Level 1, and it turns up at the end.
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" ]
}');

Let's start by looking at the arrays on those documents, with their sizes beside them.

Query

SELECT EVENT_ID,
       PAYLOAD:tags                     AS TAGS,
       ARRAY_SIZE(PAYLOAD:tags)         AS TAG_COUNT,
       ARRAY_SIZE(PAYLOAD:items)        AS ITEM_COUNT,
       PAYLOAD:device                   AS DEVICE,
       ARRAY_SIZE(PAYLOAD:device)       AS DEVICE_SIZE
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 6 columns · scroll →

EVENT_ID	TAGS	TAG_COUNT	ITEM_COUNT	DEVICE	DEVICE_SIZE
1	[ "priority", "repeat" ]	2	2	{ "os": "windows", "type": "desktop" }	NULL
2	[ "new" ]	1	1	{ "os": "ios", "type": "mobile" }	NULL
3	[]	0	1	NULL	NULL
4	[ "priority", "enterprise", "repeat" ]	3	3	{ "os": "android", "type": "tablet" }	NULL

TAG_COUNT reads 2, 1, 0, 3. Event 3 has an empty tags array, which is an array with nothing in it rather than a missing key.

Now have a look at the last column. DEVICE holds an object on three of the four rows, and DEVICE_SIZE is NULL on all four of them. Hold on to that, because we come back to it twice.

Let's start by building one

ARRAY_CONSTRUCT takes whatever you give it and puts it in an array. Let's try a few shapes at once.

Query

SELECT ARRAY_CONSTRUCT(1, 2, 3)                       AS THREE_NUMBERS,
       ARRAY_CONSTRUCT('a', 'b')                      AS TWO_STRINGS,
       ARRAY_CONSTRUCT(1, 'two', 3.5, TRUE)           AS MIXED_TYPES,
       ARRAY_CONSTRUCT()                              AS EMPTY_ARRAY,
       ARRAY_SIZE(ARRAY_CONSTRUCT())                  AS EMPTY_SIZE,
       ARRAY_CONSTRUCT(1, NULL, 3)                    AS WITH_A_NULL,
       ARRAY_SIZE(ARRAY_CONSTRUCT(1, NULL, 3))        AS SIZE_WITH_A_NULL;

Result1 row · 7 columns · scroll →

THREE_NUMBERS	TWO_STRINGS	MIXED_TYPES	EMPTY_ARRAY	EMPTY_SIZE	WITH_A_NULL	SIZE_WITH_A_NULL
[ 1, 2, 3 ]	[ "a", "b" ]	[ 1, "two", 3.5, true ]	[]	0	[ 1, undefined, 3 ]	3

Three numbers, two strings, and a mixed array holding a number, a string, a decimal and a boolean. Mixed types in one array are allowed, because the elements are VARIANT.

ARRAY_CONSTRUCT() with no arguments gives an empty array, and its size is 0.

Now the one to notice. Look at WITH_A_NULL. We passed ARRAY_CONSTRUCT(1, NULL, 3) and it came back printing undefined in the middle, with a size of 3.

So a SQL NULL goes into the array as an element. It is not dropped.

Now let's drop that NULL with COMPACT

There is a second constructor that does drop it. Let's run the two side by side.

Query

SELECT ARRAY_CONSTRUCT(1, NULL, 3)                          AS KEEPS_NULL,
       ARRAY_CONSTRUCT_COMPACT(1, NULL, 3)                  AS DROPS_NULL,
       ARRAY_SIZE(ARRAY_CONSTRUCT(1, NULL, 3))              AS SIZE_KEPT,
       ARRAY_SIZE(ARRAY_CONSTRUCT_COMPACT(1, NULL, 3))      AS SIZE_DROPPED,
       ARRAY_CONSTRUCT_COMPACT(NULL, NULL)                  AS ALL_NULLS,
       ARRAY_SIZE(ARRAY_CONSTRUCT_COMPACT(NULL, NULL))      AS ALL_NULLS_SIZE;

Result1 row · 6 columns · scroll →

KEEPS_NULL	DROPS_NULL	SIZE_KEPT	SIZE_DROPPED	ALL_NULLS	ALL_NULLS_SIZE
[ 1, undefined, 3 ]	[ 1, 3 ]	3	2	[]	0

ARRAY_CONSTRUCT keeps the undefined and reports a size of 3. ARRAY_CONSTRUCT_COMPACT gives back only the two real values and a size of 2.

And compacting two nulls gives an empty array with a size of 0, not a NULL array.

Now let's hand COMPACT a JSON null instead of a SQL NULL and see whether it treats them the same way.

Query

SELECT ARRAY_CONSTRUCT_COMPACT(1, PARSE_JSON('null'), 3)         AS WITH_JSON_NULL,
       ARRAY_SIZE(ARRAY_CONSTRUCT_COMPACT(1, PARSE_JSON('null'), 3)) AS ITS_SIZE,
       ARRAY_CONSTRUCT_COMPACT(1, NULL, 3)                       AS WITH_SQL_NULL,
       ARRAY_SIZE(ARRAY_CONSTRUCT_COMPACT(1, NULL, 3))           AS SQL_NULL_SIZE;

Result1 row · 4 columns

WITH_JSON_NULL	ITS_SIZE	WITH_SQL_NULL	SQL_NULL_SIZE
[ 1, null, 3 ]	3	[ 1, 3 ]	2

It does not. The JSON null survives, printing as null, with a size of 3. The SQL NULL is dropped and the size is 2.

The difference in one line

A JSON null is a value: the document said the key was null and meant it. A SQL NULL is an absence. COMPACT removes absences and keeps values, and that distinction decides the answer in half the queries on this page.

Now let's measure them with ARRAY_SIZE

ARRAY_SIZE looks simple and has two different ways of saying nothing. Let's put all of them in one query: the tags array, the device key, and a key that has never existed in this table.

Query

SELECT EVENT_ID,
       ARRAY_SIZE(PAYLOAD:tags)            AS TAGS_SIZE,
       ARRAY_SIZE(PAYLOAD:device)          AS DEVICE_SIZE_MISSING_ON_ONE,
       ARRAY_SIZE(PAYLOAD:never_existed)   AS A_KEY_THAT_NEVER_WAS,
       ARRAY_SIZE(PAYLOAD:tags) = 0        AS TAGS_IS_EMPTY,
       ARRAY_SIZE(PAYLOAD:device) IS NULL  AS DEVICE_SIZE_IS_NULL
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 6 columns · scroll →

EVENT_ID	TAGS_SIZE	DEVICE_SIZE_MISSING_ON_ONE	A_KEY_THAT_NEVER_WAS	TAGS_IS_EMPTY	DEVICE_SIZE_IS_NULL
1	2	NULL	NULL	FALSE	TRUE
2	1	NULL	NULL	FALSE	TRUE
3	0	NULL	NULL	TRUE	TRUE
4	3	NULL	NULL	FALSE	TRUE

Go along event 3's row. TAGS_SIZE is 0 and TAGS_IS_EMPTY is TRUE. That 0 is a real answer about a real array.

Now go down DEVICE_SIZE_MISSING_ON_ONE. It is NULL on every row, not just on the row where the key is missing. The column name promises more than the answer delivers: device is an OBJECT, and ARRAY_SIZE of an object is NULL whether the object is there or not.

A_KEY_THAT_NEVER_WAS is NULL too. Same answer, different reason.

Let's prove the rule on four values whose types we chose ourselves.

Query

SELECT ARRAY_SIZE(PAYLOAD:user)          AS SIZE_OF_AN_OBJECT,
       ARRAY_SIZE(PAYLOAD:amount)        AS SIZE_OF_A_NUMBER,
       ARRAY_SIZE(PAYLOAD:event)         AS SIZE_OF_A_STRING,
       ARRAY_SIZE(PARSE_JSON('null'))    AS SIZE_OF_A_JSON_NULL
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 4 columns

SIZE_OF_AN_OBJECT	SIZE_OF_A_NUMBER	SIZE_OF_A_STRING	SIZE_OF_A_JSON_NULL
NULL	NULL	NULL	NULL

An object, a number, a string and a JSON null. All four NULL. So ARRAY_SIZE returns 0 for an empty array and NULL for anything that is not an array at all.

What happens when we aggregate that size?

That distinction stops being academic the moment you put an aggregate round it. Let's sum and count both sizes over the table.

Query

SELECT COUNT(*)                              AS EVENTS,
       SUM(ARRAY_SIZE(PAYLOAD:tags))         AS TOTAL_TAGS,
       COUNT(ARRAY_SIZE(PAYLOAD:tags))       AS ROWS_WITH_A_TAG_SIZE,
       SUM(ARRAY_SIZE(PAYLOAD:device))       AS TOTAL_DEVICE_SIZE,
       COUNT(ARRAY_SIZE(PAYLOAD:device))     AS ROWS_WITH_A_DEVICE_SIZE,
       AVG(ARRAY_SIZE(PAYLOAD:tags))         AS AVG_TAGS
FROM EVENTS_JSON;

Result1 row · 6 columns · scroll →

EVENTS	TOTAL_TAGS	ROWS_WITH_A_TAG_SIZE	TOTAL_DEVICE_SIZE	ROWS_WITH_A_DEVICE_SIZE	AVG_TAGS
4	6	4	NULL	0	1.500000

TOTAL_TAGS is 6 over 4 events, and ROWS_WITH_A_TAG_SIZE is 4, so every row contributed.

TOTAL_DEVICE_SIZE is NULL, and ROWS_WITH_A_DEVICE_SIZE is 0. SUM over a column that is NULL on every row returns NULL, and COUNT of it returns 0.

One thing to watch

This is the shape of the bug. The query ran, the column is in the report, and the number in it is a NULL that came from a type mismatch rather than from missing data. If you are summing ARRAY_SIZE of anything, put COUNT of the same expression next to it once and look at the two together.

Let's search an array with ARRAY_CONTAINS

ARRAY_CONTAINS answers one question: does this array hold this value? The order of its two arguments is value first, array second.

Let's ask three questions of the tags on every event.

Query

SELECT EVENT_ID,
       PAYLOAD:tags                                                AS TAGS,
       ARRAY_CONTAINS('priority'::VARIANT, PAYLOAD:tags)            AS HAS_PRIORITY,
       ARRAY_CONTAINS('repeat'::VARIANT, PAYLOAD:tags)              AS HAS_REPEAT,
       ARRAY_CONTAINS('enterprise'::VARIANT, PAYLOAD:tags)          AS HAS_ENTERPRISE
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 5 columns

EVENT_ID	TAGS	HAS_PRIORITY	HAS_REPEAT	HAS_ENTERPRISE
1	[ "priority", "repeat" ]	TRUE	TRUE	FALSE
2	[ "new" ]	FALSE	FALSE	FALSE
3	[]	FALSE	FALSE	FALSE
4	[ "priority", "enterprise", "repeat" ]	TRUE	TRUE	TRUE

Event 1 has priority and repeat, so the first two answers are TRUE and HAS_ENTERPRISE is FALSE. Event 4 is TRUE on all three. Event 3 is FALSE on all three, because its array is empty.

That is the function working. Now let's get it wrong.

Now let's swap the two arguments

We will write exactly the same test with the array first and the value second, which is the order most people guess.

Query

SELECT ARRAY_CONTAINS(PAYLOAD:tags, 'priority'::VARIANT) AS SHOULD_NOT_GET_THIS_FAR
FROM EVENTS_JSON;

Result4 rows · 1 column

SHOULD_NOT_GET_THIS_FAR
FALSE
FALSE
FALSE
FALSE

It ran. Four rows came back, and every one of them says FALSE.

No error. No warning. Just FALSE, on every row, for ever. A filter written this way removes everything and looks like a filter that found nothing.

Now let's make a different mistake. We will keep the right order but drop the ::VARIANT cast on the value.

Query

SELECT ARRAY_CONTAINS('priority', PAYLOAD:tags) AS SHOULD_NOT_GET_THIS_FAR
FROM EVENTS_JSON;

Query failed — Snowflake returned an error

001044 (42P13): SQL compilation error: error line 1 at position 7
Invalid argument types for function 'ARRAY_CONTAINS': (VARCHAR(8), VARIANT)

That one is a compile error, and it names both types it was handed.

Which mistake would you rather make?

So the loud mistake is the harmless one. Forgetting the cast costs you ten seconds and the query never runs. Swapping the arguments costs you a correct-looking report, and nothing in Snowflake will ever mention it.

What does CONTAINS say about a key that is not there?

We have seen FALSE on an empty array. Let's ask the same question of the device key, which is missing on event 3.

Query

SELECT EVENT_ID,
       ARRAY_SIZE(PAYLOAD:tags)                                AS TAG_COUNT,
       ARRAY_CONTAINS('priority'::VARIANT, PAYLOAD:tags)       AS IN_TAGS,
       ARRAY_CONTAINS('priority'::VARIANT, PAYLOAD:device)     AS IN_A_MISSING_KEY,
       ARRAY_CONTAINS('priority'::VARIANT, PAYLOAD:tags) IS NULL AS TAGS_ANSWER_IS_NULL
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 5 columns

EVENT_ID	TAG_COUNT	IN_TAGS	IN_A_MISSING_KEY	TAGS_ANSWER_IS_NULL
1	2	TRUE	FALSE	FALSE
2	1	FALSE	FALSE	FALSE
3	0	FALSE	NULL	FALSE
4	3	TRUE	FALSE	FALSE

Look at event 3. IN_TAGS is FALSE, which is a real answer about an empty array. IN_A_MISSING_KEY is NULL. Not FALSE. NULL.

So there is a third answer, and it means I cannot tell. Let's count carefully and see whether it bites here.

Query

SELECT COUNT(*)                                                       AS EVENTS_TOTAL,
       COUNT_IF(ARRAY_CONTAINS('priority'::VARIANT, PAYLOAD:tags))    AS TAGGED_PRIORITY,
       COUNT_IF(NOT ARRAY_CONTAINS('priority'::VARIANT, PAYLOAD:tags)) AS NOT_TAGGED,
       COUNT_IF(ARRAY_CONTAINS('priority'::VARIANT, PAYLOAD:tags) IS NULL)
         AS ANSWER_WAS_NULL,
       COUNT_IF(ARRAY_CONTAINS('priority'::VARIANT, PAYLOAD:tags))
         + COUNT_IF(NOT ARRAY_CONTAINS('priority'::VARIANT, PAYLOAD:tags))
         AS THE_TWO_ADDED_UP
FROM EVENTS_JSON;

Result1 row · 5 columns

EVENTS_TOTAL	TAGGED_PRIORITY	NOT_TAGGED	ANSWER_WAS_NULL	THE_TWO_ADDED_UP
4	2	2	0	4

Tagged 2, not tagged 2, and ANSWER_WAS_NULL is 0. The two counts add up to 4, which is every event.

They add up because every one of these four documents has a tags key. Run the same pair of counts over PAYLOAD:device and they will not, and the difference will be the row whose answer is NULL.

Let's find where a value sits with ARRAY_POSITION

ARRAY_POSITION takes the same two arguments in the same order and gives you the index instead of a boolean.

Query

SELECT EVENT_ID,
       PAYLOAD:tags                                            AS TAGS,
       ARRAY_POSITION('priority'::VARIANT, PAYLOAD:tags)       AS WHERE_PRIORITY,
       ARRAY_POSITION('repeat'::VARIANT, PAYLOAD:tags)         AS WHERE_REPEAT,
       ARRAY_POSITION('nope'::VARIANT, PAYLOAD:tags)           AS WHERE_ABSENT,
       ARRAY_POSITION('nope'::VARIANT, PAYLOAD:tags) IS NULL   AS ABSENT_IS_NULL
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 6 columns · scroll →

EVENT_ID	TAGS	WHERE_PRIORITY	WHERE_REPEAT	WHERE_ABSENT	ABSENT_IS_NULL
1	[ "priority", "repeat" ]	0	1	NULL	TRUE
2	[ "new" ]	NULL	NULL	NULL	TRUE
3	[]	NULL	NULL	NULL	TRUE
4	[ "priority", "enterprise", "repeat" ]	0	2	NULL	TRUE

On event 1, priority is at 0 and repeat is at 1. On event 4, priority is at 0 and repeat is at 2. These indexes are zero based, the same as ARRAY_SLICE and the INDEX column of FLATTEN.

And when the value is not in the array at all, look at WHERE_ABSENT. It is NULL on every row, with ABSENT_IS_NULL confirming it. Not -1, which is what the same function returns in a lot of other languages.

Zero is a position, not a failure

ARRAY_POSITION(x, a) = 0 means found, at the front. A NULL means not there. If you test it with > 0 you will silently skip every value that happens to be the first element.

Let's take a piece out with ARRAY_SLICE

ARRAY_SLICE takes a start and an end, and the end is exclusive. Let's try four slices of event 4, which has three tags.

Query

SELECT PAYLOAD:tags                                  AS ALL_TAGS,
       ARRAY_SIZE(PAYLOAD:tags)                      AS N,
       ARRAY_SLICE(PAYLOAD:tags, 0, 1)               AS FIRST_ONE,
       ARRAY_SLICE(PAYLOAD:tags, 1, 3)               AS FROM_ONE_TO_THREE,
       ARRAY_SLICE(PAYLOAD:tags, 0, 99)              AS END_PAST_THE_END,
       ARRAY_SLICE(PAYLOAD:tags, 2, 2)               AS AN_EMPTY_SLICE,
       ARRAY_SIZE(ARRAY_SLICE(PAYLOAD:tags, 2, 2))   AS EMPTY_SLICE_SIZE
FROM EVENTS_JSON
WHERE EVENT_ID = 4;

Result1 row · 7 columns · scroll →

ALL_TAGS	N	FIRST_ONE	FROM_ONE_TO_THREE	END_PAST_THE_END	AN_EMPTY_SLICE	EMPTY_SLICE_SIZE
[ "priority", "enterprise", "repeat" ]	3	[ "priority" ]	[ "enterprise", "repeat" ]	[ "priority", "enterprise", "repeat" ]	[]	0

0 to 1 gives the first tag on its own. 1 to 3 gives the other two. 0 to 99 gives all of them, so an end past the end is not an error.

And 2 to 2 gives an empty array with a size of 0. A slice that selects nothing is still a slice.

Now let's slice the device key, which is an object on three rows and missing on one.

Query

SELECT EVENT_ID,
       ARRAY_SIZE(PAYLOAD:tags)                         AS N,
       ARRAY_SLICE(PAYLOAD:tags, 0, 2)                  AS SLICE_OF_TAGS,
       ARRAY_SIZE(ARRAY_SLICE(PAYLOAD:tags, 0, 2))      AS SLICE_SIZE,
       ARRAY_SLICE(PAYLOAD:device, 0, 2)                AS SLICE_OF_MISSING,
       ARRAY_SLICE(PAYLOAD:device, 0, 2) IS NULL        AS MISSING_SLICE_IS_NULL
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 6 columns · scroll →

EVENT_ID	N	SLICE_OF_TAGS	SLICE_SIZE	SLICE_OF_MISSING	MISSING_SLICE_IS_NULL
1	2	[ "priority", "repeat" ]	2	[ { "os": "windows", "type": "desktop" } ]	FALSE
2	1	[ "new" ]	1	[ { "os": "ios", "type": "mobile" } ]	FALSE
3	0	[]	0	NULL	TRUE
4	3	[ "priority", "enterprise" ]	2	[ { "os": "android", "type": "tablet" } ]	FALSE

On event 3, SLICE_OF_MISSING is NULL and MISSING_SLICE_IS_NULL is TRUE. That is the answer you would expect.

Now look at the other three rows. SLICE_OF_MISSING came back as a one-element array holding the whole device object. ARRAY_SLICE did not refuse the object. It wrapped it and then sliced the wrapper.

Hold that thought, because the next section is built on it.

Now let's join two arrays with ARRAY_CAT

ARRAY_CAT takes two arrays and gives you one. Let's try it on arrays we build ourselves first.

Query

SELECT ARRAY_CAT(ARRAY_CONSTRUCT(1, 2), ARRAY_CONSTRUCT(3))            AS ONE_TWO_THREE,
       ARRAY_SIZE(ARRAY_CAT(ARRAY_CONSTRUCT(1, 2), ARRAY_CONSTRUCT(3))) AS ITS_SIZE,
       ARRAY_CAT(ARRAY_CONSTRUCT(1), ARRAY_CONSTRUCT())                AS WITH_AN_EMPTY,
       ARRAY_CAT(ARRAY_CONSTRUCT(), ARRAY_CONSTRUCT())                 AS TWO_EMPTIES,
       ARRAY_CAT(ARRAY_CONSTRUCT(1), NULL)                             AS WITH_A_NULL,
       ARRAY_CAT(ARRAY_CONSTRUCT(1), NULL) IS NULL                     AS NULL_POISONS_IT;

Result1 row · 6 columns · scroll →

ONE_TWO_THREE	ITS_SIZE	WITH_AN_EMPTY	TWO_EMPTIES	WITH_A_NULL	NULL_POISONS_IT
[ 1, 2, 3 ]	3	[ 1 ]	[]	NULL	TRUE

[1, 2] and [3] give three elements. An empty array on the right leaves the left one unchanged. Two empties give an empty array.

And NULL on the right gives NULL for the whole thing, with NULL_POISONS_IT saying so. One NULL argument and the result is gone.

Now let's run it on the real tags column, which is a VARIANT rather than a declared ARRAY.

Query

SELECT ARRAY_CAT(PAYLOAD:tags, ARRAY_CONSTRUCT('audited')) AS SHOULD_NOT_GET_THIS_FAR
FROM EVENTS_JSON;

Result4 rows · 1 column

SHOULD_NOT_GET_THIS_FAR
[ "priority", "repeat", "audited" ]
[ "new", "audited" ]
[ "audited" ]
[ "priority", "enterprise", "repeat", "audited" ]

All four rows came back with audited appended, including event 3, whose empty array became a one-element one.

A correction

That grid is a correction to something said earlier in this course. ARRAY_CAT does accept a VARIANT that holds an array. No cast is needed, and the capture's own column name (SHOULD_NOT_GET_THIS_FAR) is wrong about its own output.

What ARRAY_CAT will not take is an OBJECT on the left. That is the real rule, and it is a runtime error rather than a quiet answer. Which is where people reach for a cast.

Can we cast our way out of it?

The obvious move is ::ARRAY. Let's see what that does to an object before we rely on it.

Query

SELECT PAYLOAD:user::ARRAY            AS AN_OBJECT_AS_ARRAY,
       PAYLOAD:user::ARRAY IS NULL    AS OBJECT_CAST_IS_NULL
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 2 columns

AN_OBJECT_AS_ARRAY	OBJECT_CAST_IS_NULL
[ { "id": 101, "name": "Northwind Traders" } ]	FALSE

It did not fail. OBJECT_CAST_IS_NULL is FALSE, and the user object came back wrapped in a one-element array. The same thing we saw ARRAY_SLICE do.

It is not only objects, either.

Query

SELECT PAYLOAD:amount::ARRAY AS A_NUMBER_AS_ARRAY
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 1 column

A_NUMBER_AS_ARRAY
[ 4200 ]

A number wrapped the same way. So ::ARRAY on a VARIANT is not a check. It is a wrap, and it almost never refuses.

Let's see what that does to the concatenation we actually wanted.

Query

SELECT EVENT_ID,
       PAYLOAD:device IS NULL                                            AS DEVICE_MISSING,
       PAYLOAD:device::ARRAY                                             AS CAST_OF_MISSING,
       PAYLOAD:device::ARRAY IS NULL                                     AS CAST_IS_STILL_NULL,
       ARRAY_CAT(PAYLOAD:device::ARRAY, ARRAY_CONSTRUCT('audited'))       AS CAT_RESULT,
       ARRAY_CAT(PAYLOAD:device::ARRAY,
                 ARRAY_CONSTRUCT('audited')) IS NULL                     AS CAT_GAVE_NULL
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 6 columns · scroll →

EVENT_ID	DEVICE_MISSING	CAST_OF_MISSING	CAST_IS_STILL_NULL	CAT_RESULT	CAT_GAVE_NULL
1	FALSE	[ { "os": "windows", "type": "desktop" } ]	FALSE	[ { "os": "windows", "type": "desktop" }, "audited" ]	FALSE
2	FALSE	[ { "os": "ios", "type": "mobile" } ]	FALSE	[ { "os": "ios", "type": "mobile" }, "audited" ]	FALSE
3	TRUE	NULL	TRUE	NULL	TRUE
4	FALSE	[ { "os": "android", "type": "tablet" } ]	FALSE	[ { "os": "android", "type": "tablet" }, "audited" ]	FALSE

On events 1, 2 and 4, CAT_RESULT is the device object and the string audited sitting side by side in one array. That is not an error. It is a silent change of shape, which is harder to find.

And on event 3, where the key is missing, CAST_IS_STILL_NULL is TRUE, so CAT_GAVE_NULL is TRUE as well. The row is still in the result. Its value is not.

That distinction matters when you count. Let's count all three forms.

Query

SELECT COUNT(*)                                                     AS EVENTS_TOTAL,
       COUNT(ARRAY_CAT(PAYLOAD:tags::ARRAY, ARRAY_CONSTRUCT('x')))  AS CAST_ONLY_ON_TAGS,
       COUNT(ARRAY_CAT(PAYLOAD:device::ARRAY, ARRAY_CONSTRUCT('x'))) AS CAST_ONLY_ON_DEVICE,
       COUNT(ARRAY_CAT(COALESCE(PAYLOAD:device::ARRAY, ARRAY_CONSTRUCT()),
                       ARRAY_CONSTRUCT('x')))                       AS CAST_AND_COALESCE
FROM EVENTS_JSON;

Result1 row · 4 columns

EVENTS_TOTAL	CAST_ONLY_ON_TAGS	CAST_ONLY_ON_DEVICE	CAST_AND_COALESCE
4	4	3	4

The cast on the tags column keeps 4 of 4. The cast on the device column keeps 3, because COUNT skips the NULL that event 3 produced. Wrap it in COALESCE and it is back to 4.

Here is the COALESCE form in full, on both columns at once.

Query

SELECT EVENT_ID,
       ARRAY_CAT(COALESCE(PAYLOAD:device::ARRAY, ARRAY_CONSTRUCT()),
                 ARRAY_CONSTRUCT('audited'))                        AS SAFE_RESULT,
       ARRAY_SIZE(ARRAY_CAT(COALESCE(PAYLOAD:device::ARRAY, ARRAY_CONSTRUCT()),
                            ARRAY_CONSTRUCT('audited')))            AS SAFE_SIZE,
       ARRAY_CAT(COALESCE(PAYLOAD:tags::ARRAY, ARRAY_CONSTRUCT()),
                 ARRAY_CONSTRUCT('audited'))                        AS SAFE_ON_TAGS,
       ARRAY_SIZE(ARRAY_CAT(COALESCE(PAYLOAD:tags::ARRAY, ARRAY_CONSTRUCT()),
                            ARRAY_CONSTRUCT('audited')))            AS SAFE_TAGS_SIZE
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 5 columns

EVENT_ID	SAFE_RESULT	SAFE_SIZE	SAFE_ON_TAGS	SAFE_TAGS_SIZE
1	[ { "os": "windows", "type": "desktop" }, "audited" ]	2	[ "priority", "repeat", "audited" ]	3
2	[ { "os": "ios", "type": "mobile" }, "audited" ]	2	[ "new", "audited" ]	2
3	[ "audited" ]	1	[ "audited" ]	1
4	[ { "os": "android", "type": "tablet" }, "audited" ]	2	[ "priority", "enterprise", "repeat", "audited" ]	4

Every row has a value now. Event 3's SAFE_RESULT is a one-element array holding just audited, because there was no device to put in front of it.

And for comparison, the cast on the tags column alone, where there was never anything wrong to fix.

Query

SELECT EVENT_ID,
       ARRAY_SIZE(PAYLOAD:tags)                                          AS TAGS_BEFORE,
       ARRAY_CAT(PAYLOAD:tags::ARRAY, ARRAY_CONSTRUCT('audited'))        AS TAGS_AFTER,
       ARRAY_SIZE(ARRAY_CAT(PAYLOAD:tags::ARRAY,
                            ARRAY_CONSTRUCT('audited')))                 AS SIZE_AFTER
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 4 columns

EVENT_ID	TAGS_BEFORE	TAGS_AFTER	SIZE_AFTER
1	2	[ "priority", "repeat", "audited" ]	3
2	1	[ "new", "audited" ]	2
3	0	[ "audited" ]	1
4	3	[ "priority", "enterprise", "repeat", "audited" ]	4

SIZE_AFTER is one more than TAGS_BEFORE on every row, including event 3, which went from 0 to 1.

The form worth memorising

COALESCE(x::ARRAY, ARRAY_CONSTRUCT()) is the form to remember. It is five more characters than the cast on its own and it is the difference between a value and a NULL on every row where the key did not turn up.

Now let's sort one with ARRAY_SORT

ARRAY_SORT takes an array and an optional direction. Let's do numbers, strings and the real tags column in one go.

Query

SELECT ARRAY_SORT(ARRAY_CONSTRUCT(3, 1, 2))                  AS ASCENDING,
       ARRAY_SORT(ARRAY_CONSTRUCT(3, 1, 2), FALSE)            AS DESCENDING,
       ARRAY_SORT(ARRAY_CONSTRUCT('b', 'a', 'C'))             AS STRINGS_ASCENDING,
       ARRAY_SORT(PAYLOAD:tags)                               AS TAGS_SORTED,
       PAYLOAD:tags                                           AS TAGS_ORIGINAL
FROM EVENTS_JSON
WHERE EVENT_ID = 4;

Result1 row · 5 columns

ASCENDING	DESCENDING	STRINGS_ASCENDING	TAGS_SORTED	TAGS_ORIGINAL
[ 1, 2, 3 ]	[ 3, 2, 1 ]	[ "C", "a", "b" ]	[ "enterprise", "priority", "repeat" ]	[ "priority", "enterprise", "repeat" ]

Ascending is the default, and passing FALSE as the second argument reverses it.

Now look at STRINGS_ASCENDING. We gave it b, a and C, and it gave back C first, then a, then b. So it is not case insensitive. Capital letters sort ahead of lower-case ones.

And the real tags came back in alphabetical order, which is not the order they are stored in.

Let's find out where a NULL ends up.

Query

SELECT ARRAY_SORT(ARRAY_CONSTRUCT(3, NULL, 1))                     AS ASC_WITH_NULL,
       ARRAY_SORT(ARRAY_CONSTRUCT(3, NULL, 1), FALSE)              AS DESC_WITH_NULL,
       ARRAY_SORT(ARRAY_CONSTRUCT(3, NULL, 1), TRUE, TRUE)         AS NULLS_FIRST_FLAG,
       ARRAY_SORT(ARRAY_CONSTRUCT(3, NULL, 1), TRUE, FALSE)        AS NULLS_LAST_FLAG,
       ARRAY_SIZE(ARRAY_SORT(ARRAY_CONSTRUCT(3, NULL, 1)))         AS SIZE_PRESERVED;

Result1 row · 5 columns

ASC_WITH_NULL	DESC_WITH_NULL	NULLS_FIRST_FLAG	NULLS_LAST_FLAG	SIZE_PRESERVED
[ 1, 3, undefined ]	[ undefined, 3, 1 ]	[ undefined, 1, 3 ]	[ 1, 3, undefined ]	3

Ascending, the undefined goes last. Descending, it goes first. There are two more flags for controlling that directly, and the last column is the important one: the size is 3 either way.

ARRAY_SORT never drops anything. That is worth saying straight after COMPACT, which does.

Now let's sort an array where the elements are not all the same type.

Query

SELECT ARRAY_CONSTRUCT(10, 'apple', TRUE, 2.5, 'Banana')             AS ORIGINAL,
       ARRAY_SORT(ARRAY_CONSTRUCT(10, 'apple', TRUE, 2.5, 'Banana')) AS SORTED,
       ARRAY_SIZE(ARRAY_SORT(ARRAY_CONSTRUCT(10, 'apple', TRUE, 2.5, 'Banana')))
         AS SIZE_PRESERVED;

Result1 row · 3 columns

ORIGINAL	SORTED	SIZE_PRESERVED
[ 10, "apple", true, 2.5, "Banana" ]	[ true, 2.5, 10, "Banana", "apple" ]	5

The boolean came first, then the two numbers in order, then the two strings, with Banana ahead of apple because of the capital letter. All five elements are still there.

Type first, then value. That is the same rule ORDER BY follows on a VARIANT column, which we met in Episode 89.

And an array of objects? Let's try event 4's items, which are three objects with the same three keys.

Query

SELECT PAYLOAD:items            AS ORIGINAL,
       ARRAY_SORT(PAYLOAD:items) AS SORTED
FROM EVENTS_JSON
WHERE EVENT_ID = 4;

Result1 row · 2 columns

ORIGINAL	SORTED
[ { "price": 500, "qty": 12, "sku": "D-400" }, { "price": 300, "qty": 10, "sku": "A-100" }, { "price": 156.25, "qty": 8, "sku": "E-505" } ]	[ { "price": 156.25, "qty": 8, "sku": "E-505" }, { "price": 300, "qty": 10, "sku": "A-100" }, { "price": 500, "qty": 12, "sku": "D-400" } ]

No error. It sorted them, and comparing the two columns the order it chose follows the price key, which is the first key in each object once the keys are in order.

Now the set operations

Two functions here. ARRAYS_OVERLAP answers whether two arrays share anything, and ARRAY_INTERSECTION tells you what.

Query

SELECT ARRAYS_OVERLAP(ARRAY_CONSTRUCT('a','b'), ARRAY_CONSTRUCT('b','c'))
         AS THEY_OVERLAP,
       ARRAYS_OVERLAP(ARRAY_CONSTRUCT('a'), ARRAY_CONSTRUCT('z'))
         AS THEY_DO_NOT,
       ARRAY_INTERSECTION(ARRAY_CONSTRUCT('a','b'), ARRAY_CONSTRUCT('b','c'))
         AS THE_INTERSECTION,
       ARRAY_INTERSECTION(ARRAY_CONSTRUCT('a'), ARRAY_CONSTRUCT('z'))
         AS AN_EMPTY_INTERSECTION,
       ARRAY_SIZE(ARRAY_INTERSECTION(ARRAY_CONSTRUCT('a'), ARRAY_CONSTRUCT('z')))
         AS EMPTY_INTERSECTION_SIZE,
       ARRAYS_OVERLAP(ARRAY_CONSTRUCT('a'), ARRAY_CONSTRUCT())
         AS OVERLAP_WITH_EMPTY;

Result1 row · 6 columns · scroll →

THEY_OVERLAP	THEY_DO_NOT	THE_INTERSECTION	AN_EMPTY_INTERSECTION	EMPTY_INTERSECTION_SIZE	OVERLAP_WITH_EMPTY
TRUE	FALSE	[ "b" ]	[]	0	FALSE

a, b against b, c overlaps and the intersection is b. a against z does not, and the intersection is an empty array with a size of 0 rather than a NULL.

And overlap with an empty array is FALSE. There is nothing in it to share.

On real data that pair is a watchlist check. Let's ask which events carry a tag we care about, and which tags matched.

Query

SELECT EVENT_ID,
       PAYLOAD:tags                                                      AS TAGS,
       ARRAYS_OVERLAP(PAYLOAD:tags, ARRAY_CONSTRUCT('priority','urgent')) AS MATCHES_WATCHLIST,
       ARRAY_INTERSECTION(PAYLOAD:tags, ARRAY_CONSTRUCT('priority','repeat'))
         AS WHICH_ONES,
       ARRAY_SIZE(ARRAY_INTERSECTION(PAYLOAD:tags,
                  ARRAY_CONSTRUCT('priority','repeat')))                  AS HOW_MANY
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 5 columns

EVENT_ID	TAGS	MATCHES_WATCHLIST	WHICH_ONES	HOW_MANY
1	[ "priority", "repeat" ]	TRUE	[ "priority", "repeat" ]	2
2	[ "new" ]	FALSE	[]	0
3	[]	FALSE	[]	0
4	[ "priority", "enterprise", "repeat" ]	TRUE	[ "priority", "repeat" ]	2

Events 1 and 4 match the watchlist. WHICH_ONES lists the tags that matched and HOW_MANY counts them, and both of those events matched 2.

So you get the answer and the evidence in one query, which is usually what an alert needs.

Do these functions need the cast at all?

We have been writing PAYLOAD:tags with no ::ARRAY on it for most of this post. Let's check that properly rather than assume it.

Query

SELECT TYPEOF(PAYLOAD:tags)                                  AS WHAT_IT_IS,
       ARRAY_SIZE(PAYLOAD:tags)                              AS ARRAY_SIZE_OK,
       ARRAY_CONTAINS('priority'::VARIANT, PAYLOAD:tags)     AS ARRAY_CONTAINS_OK,
       ARRAY_SLICE(PAYLOAD:tags, 0, 1)                       AS ARRAY_SLICE_OK,
       ARRAY_SORT(PAYLOAD:tags)                              AS ARRAY_SORT_OK,
       ARRAY_POSITION('repeat'::VARIANT, PAYLOAD:tags)       AS ARRAY_POSITION_OK
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 6 columns · scroll →

WHAT_IT_IS	ARRAY_SIZE_OK	ARRAY_CONTAINS_OK	ARRAY_SLICE_OK	ARRAY_SORT_OK	ARRAY_POSITION_OK
ARRAY	2	TRUE	[ "priority" ]	[ "priority", "repeat" ]	1

TYPEOF says the value is an ARRAY, and ARRAY_SIZE, ARRAY_CONTAINS, ARRAY_SLICE, ARRAY_SORT and ARRAY_POSITION all answered on the bare VARIANT.

Here are seven more, one at a time, on the same column.

ARRAYS_OVERLAP

SELECT ARRAYS_OVERLAP(PAYLOAD:tags, ARRAY_CONSTRUCT('priority')) AS OVERLAP_ON_VARIANT
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 1 column

OVERLAP_ON_VARIANT
TRUE

ARRAY_INTERSECTION

SELECT ARRAY_INTERSECTION(PAYLOAD:tags, ARRAY_CONSTRUCT('priority'))
         AS INTERSECTION_ON_VARIANT
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 1 column

INTERSECTION_ON_VARIANT
[ "priority" ]

ARRAY_APPEND

SELECT ARRAY_APPEND(PAYLOAD:tags, 'audited') AS APPEND_ON_VARIANT
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 1 column

APPEND_ON_VARIANT
[ "priority", "repeat", "audited" ]

ARRAY_DISTINCT

SELECT ARRAY_DISTINCT(PAYLOAD:tags) AS DISTINCT_ON_VARIANT
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 1 column

DISTINCT_ON_VARIANT
[ "priority", "repeat" ]

ARRAY_REVERSE

SELECT ARRAY_REVERSE(PAYLOAD:tags) AS REVERSE_ON_VARIANT
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 1 column

REVERSE_ON_VARIANT
[ "repeat", "priority" ]

ARRAY_TO_STRING

SELECT ARRAY_TO_STRING(PAYLOAD:tags, ',') AS TO_STRING_ON_VARIANT
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 1 column

TO_STRING_ON_VARIANT
priority,repeat

ARRAY_COMPACT

SELECT ARRAY_COMPACT(PAYLOAD:tags) AS COMPACT_ON_VARIANT
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 1 column

COMPACT_ON_VARIANT
[ "priority", "repeat" ]

Every one of them answered. And the same seven with the cast put back on, so you can compare.

Query

SELECT ARRAYS_OVERLAP(PAYLOAD:tags::ARRAY, ARRAY_CONSTRUCT('priority')) AS OVERLAP_,
       ARRAY_INTERSECTION(PAYLOAD:tags::ARRAY,
                          ARRAY_CONSTRUCT('priority'))                  AS INTERSECTION_,
       ARRAY_APPEND(PAYLOAD:tags::ARRAY, 'audited')                     AS APPEND_,
       ARRAY_DISTINCT(PAYLOAD:tags::ARRAY)                              AS DISTINCT_,
       ARRAY_REVERSE(PAYLOAD:tags::ARRAY)                               AS REVERSE_,
       ARRAY_TO_STRING(PAYLOAD:tags::ARRAY, ',')                        AS TO_STRING_,
       ARRAY_COMPACT(PAYLOAD:tags::ARRAY)                               AS COMPACT_
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 7 columns · scroll →

OVERLAP_	INTERSECTION_	APPEND_	DISTINCT_	REVERSE_	TO_STRING_	COMPACT_
TRUE	[ "priority" ]	[ "priority", "repeat", "audited" ]	[ "priority", "repeat" ]	[ "repeat", "priority" ]	priority,repeat	[ "priority", "repeat" ]

Identical answers. So the cast is not doing any work on a VARIANT that already holds an array, which is worth knowing because — as we saw two sections ago — on a VARIANT that holds anything else the same cast quietly changes the shape.

A few more worth knowing

There are several small ones you will reach for without needing a section each. Let's put them in two queries.

Query

SELECT ARRAY_DISTINCT(ARRAY_CONSTRUCT(1, 2, 2, 3, 1))            AS DISTINCT_,
       ARRAY_SIZE(ARRAY_DISTINCT(ARRAY_CONSTRUCT(1, 2, 2, 3, 1))) AS DISTINCT_SIZE,
       ARRAY_COMPACT(ARRAY_CONSTRUCT(1, NULL, 2))                AS COMPACT_,
       ARRAY_REVERSE(ARRAY_CONSTRUCT(1, 2, 3))                   AS REVERSED,
       ARRAY_MIN(ARRAY_CONSTRUCT(3, 1, 2))                       AS THE_MIN,
       ARRAY_MAX(ARRAY_CONSTRUCT(3, 1, 2))                       AS THE_MAX;

Result1 row · 6 columns · scroll →

DISTINCT_	DISTINCT_SIZE	COMPACT_	REVERSED	THE_MIN	THE_MAX
[ 1, 2, 3 ]	3	[ 1, 2 ]	[ 3, 2, 1 ]	1	3

ARRAY_DISTINCT took five elements down to 3. ARRAY_COMPACT is COMPACT applied to an array that already exists, rather than while building one. ARRAY_REVERSE reverses, and ARRAY_MIN and ARRAY_MAX give the smallest and largest element.

Query

SELECT ARRAY_APPEND(ARRAY_CONSTRUCT(1, 2), 3)                AS APPENDED,
       ARRAY_PREPEND(ARRAY_CONSTRUCT(1, 2), 0)               AS PREPENDED,
       ARRAY_INSERT(ARRAY_CONSTRUCT(1, 3), 1, 2)             AS INSERTED,
       ARRAY_REMOVE(ARRAY_CONSTRUCT(1, 2, 2, 3), 2::VARIANT) AS REMOVED_BY_VALUE,
       ARRAY_REMOVE_AT(ARRAY_CONSTRUCT(1, 2, 3), 1)          AS REMOVED_BY_INDEX;

Result1 row · 5 columns

APPENDED	PREPENDED	INSERTED	REMOVED_BY_VALUE	REMOVED_BY_INDEX
[ 1, 2, 3 ]	[ 0, 1, 2 ]	[ 1, 2, 3 ]	[ 1, 3 ]	[ 1, 3 ]

ARRAY_APPEND adds at the end and ARRAY_PREPEND at the front. Watch the argument order on those two, because it is array first, value second — the opposite of ARRAY_CONTAINS.

ARRAY_INSERT puts a value at an index. ARRAY_REMOVE removes by value and wants the ::VARIANT cast, and ARRAY_REMOVE_AT removes by index.

One last thing: let's build an array out of rows

Everything so far has gone from an array to an answer. ARRAY_AGG goes the other way: it takes a column and gives you one array. Let's run it over the ORDERS table.

Query

SELECT COUNT(*)                                  AS ORDERS,
       ARRAY_SIZE(ARRAY_AGG(STATUS))             AS AGG_SIZE,
       ARRAY_SIZE(ARRAY_AGG(DISTINCT STATUS))    AS DISTINCT_AGG_SIZE,
       ARRAY_AGG(DISTINCT STATUS)                AS THE_STATUSES,
       ARRAY_SIZE(ARRAY_AGG(AMOUNT))             AS AMOUNT_AGG_SIZE
FROM ORDERS;

Result1 row · 5 columns

ORDERS	AGG_SIZE	DISTINCT_AGG_SIZE	THE_STATUSES	AMOUNT_AGG_SIZE
41	41	4	[ "SHIPPED", "CANCELLED", "PENDING", "RETURNED" ]	40

41 orders and 41 elements in the array of statuses. With DISTINCT it is 4, and THE_STATUSES lists them.

Now look at the last column. ARRAY_AGG of the AMOUNT column gave 40 elements, not 41.

Let's find out why.

Query

SELECT COUNT(*)                          AS ROWS_TOTAL,
       COUNT(AMOUNT)                     AS NON_NULL_AMOUNTS,
       ARRAY_SIZE(ARRAY_AGG(AMOUNT))     AS ELEMENTS_IN_THE_ARRAY,
       ARRAY_SIZE(ARRAY_AGG(AMOUNT)) = COUNT(*)      AS MATCHES_ROW_COUNT,
       ARRAY_SIZE(ARRAY_AGG(AMOUNT)) = COUNT(AMOUNT) AS MATCHES_NON_NULL_COUNT
FROM ORDERS;

Result1 row · 5 columns

ROWS_TOTAL	NON_NULL_AMOUNTS	ELEMENTS_IN_THE_ARRAY	MATCHES_ROW_COUNT	MATCHES_NON_NULL_COUNT
41	40	40	FALSE	TRUE

41 rows, 40 non-null amounts, 40 elements. MATCHES_ROW_COUNT is FALSE and MATCHES_NON_NULL_COUNT is TRUE. ARRAY_AGG skips nulls.

Which means the array is not row aligned. Element 0 is not necessarily the first row, and you cannot pair this array up with another one built the same way from a different column.

Last query. Let's send an array back the other way and check nothing was lost on the trip.

Query

SELECT (SELECT COUNT(DISTINCT STATUS) FROM ORDERS)             AS DISTINCT_IN_TABLE,
       (SELECT ARRAY_SIZE(ARRAY_AGG(DISTINCT STATUS)) FROM ORDERS) AS IN_THE_ARRAY,
       (SELECT COUNT(*) FROM TABLE(FLATTEN(INPUT =>
            (SELECT ARRAY_AGG(DISTINCT STATUS) FROM ORDERS))))  AS BACK_TO_ROWS;

Result1 row · 3 columns

DISTINCT_IN_TABLE	IN_THE_ARRAY	BACK_TO_ROWS
4	4	4

4 distinct statuses in the table, 4 elements in the array, 4 rows back out of FLATTEN. A clean round trip.

Key points

ARRAY_CONSTRUCT keeps a SQL NULL as an element printed undefined. ARRAY_CONSTRUCT_COMPACT drops it, but keeps a JSON null, because a JSON null is a value.
ARRAY_SIZE returns 0 for an empty array and NULL for anything that is not an array. SUM over that NULL gives you NULL and nothing says why.
ARRAY_CONTAINS takes the value first and the array second. Backwards, it returns FALSE on every row and never raises.
Forgetting the ::VARIANT cast is a compile error, which makes it the safe mistake.
On a missing key ARRAY_CONTAINS answers NULL, not FALSE, so true and false need not add up to your row count.
ARRAY_POSITION is zero based and returns NULL when the value is absent, not -1.
ARRAY_SLICE's end is exclusive, an end past the end is fine, and an empty slice is an empty array rather than an error.
ARRAY_CAT accepts a VARIANT holding an array, refuses an OBJECT on the left, and a NULL argument poisons the whole result.
::ARRAY on a non-array VARIANT wraps it rather than failing. COALESCE(x::ARRAY, ARRAY_CONSTRUCT()) is the form that survives a missing key.
ARRAY_SORT sorts type first then value, puts capitals ahead of lower case, and never drops an element.
ARRAYS_OVERLAP and ARRAY_INTERSECTION give you the answer and the evidence in one query.
ARRAY_AGG skips nulls, so the array it builds is not row aligned with the table it came from.

Next in this series

Episode 93 — the OBJECT functions. OBJECT_CONSTRUCT, OBJECT_KEYS, OBJECT_INSERT, OBJECT_DELETE, OBJECT_PICK and OBJECT_AGG — and how one NULL column turns a perfectly square table into documents of two different shapes.

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 documents and the ORDERS rows are invented demo data. Every count in the text above is read off one of the grids on this page rather than worked out here, and where a capture's own column name disagrees with its output the text follows the output. 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 92 - ARRAY Functions - SIZE, CONTAINS, SLICE, CAT, SORT, AGG

Let's look at the arrays we have

Let's start by building one

Now let's drop that NULL with COMPACT

Now let's measure them with ARRAY_SIZE

What happens when we aggregate that size?

Let's search an array with ARRAY_CONTAINS

Now let's swap the two arguments

What does CONTAINS say about a key that is not there?

Let's find where a value sits with ARRAY_POSITION

Let's take a piece out with ARRAY_SLICE

Now let's join two arrays with ARRAY_CAT

Can we cast our way out of it?

Now let's sort one with ARRAY_SORT

Now the set operations

Do these functions need the cast at all?

A few more worth knowing

One last thing: let's build an array out of rows

Key points

No comments:

Post a Comment