Snowflake SQL Tutorial 87 - PARSE_JSON, TRY_PARSE_JSON, CHECK_JSON, TO_JSON - Loading JSON

Snowflake SQL · Episode 87

In this post we are going to cover PARSE_JSON, TRY_PARSE_JSON, CHECK_JSON and TO_JSON in Snowflake.

These are the four functions you need when JSON arrives as text. PARSE_JSON turns that text into a VARIANT you can read inside. TRY_PARSE_JSON does the same thing but returns NULL instead of failing. CHECK_JSON does not parse anything, it just tells you what is wrong with the text. And TO_JSON goes the other way, turning a VARIANT back into text.

We will start with a table of JSON sitting in a TEXT column and build it up step by step. On the way we will meet a filter that throws away good rows along with the bad one, and we will find out that Snowflake accepts JSON your application would reject.

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

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

On this page

1. Let's create some sample data
2. Let's start with PARSE_JSON on a row we know is good
3. Now let's remember that JSON is not only objects
4. Now let's run PARSE_JSON over the whole table
5. Let's use TRY_PARSE_JSON instead
6. So what does a NULL from TRY_PARSE_JSON actually mean?
7. Can we tell a broken row from a valid one?
8. Now let's ask CHECK_JSON for the reason
9. Let's load every row and keep the complaint
10. Now compare that to the filter people actually write
11. Now the part that surprises people: this is not a strict parser
12. So what does Snowflake refuse?
13. Now let's go the other way with TO_JSON
14. One last thing: does the text come back the way you sent it?

Let's create some sample data

In the last post we had a VARIANT column already full of documents. This time we are going to start one step earlier, with JSON that is still just characters.

Sample data

-- The tables on this page come from the setup script this course uses
-- throughout. Run it once and every post in this level works against the
-- same data.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

-- Two tables are used below.
--   EVENTS_JSON   - four documents already parsed, in a VARIANT column.
--   JSON_TEXT_RAW - eleven pieces of JSON that have NOT been parsed at all.
--                   Same kind of content, stored as TEXT.

Let's look at the text table first.

Query

SELECT * FROM JSON_TEXT_RAW ORDER BY ROW_ID;

Result11 rows · 3 columns

ROW_ID	WHAT_IT_IS	RAW_TEXT
1	valid object	{"sku":"A-100","qty":8,"price":300.00}
2	valid, other shape	{"sku":"B-220","price":450.00,"note":"back-ordered"}
3	trailing comma	{"sku":"C-310","qty":3,}
4	single quotes	{'sku':'D-400','qty':12}
5	unquoted key	{sku:"E-505","qty":8}
6	truncated	{"sku":"F-600","qty":
7	valid array	[1,2,3]
8	valid bare scalar	42
9	NULL, no text	NULL
10	empty string
11	the text null	null

Eleven rows and three columns. Each row holds one piece of JSON, and the WHAT_IT_IS column says what kind of piece it is.

Some of them are fine. There is a valid object, another valid object with different keys, an array and a bare number. The rest are the ones we are here for: a trailing comma, single quotes, an unquoted key and a document that was cut off halfway through.

Three rows that look alike

Rows nine, ten and eleven look the same in the RAW_TEXT column above and they are not the same at all. Row nine has no text in it. Row ten is an empty string. Row eleven holds the four characters null. A result grid prints all three of those the same way, which is why this table carries a label column. Keep an eye on that label, because the difference between those three rows is most of this post.

And this really is text. Nothing has been parsed yet.

Query

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'JSON_TEXT_RAW'
ORDER BY ORDINAL_POSITION;

Result3 rows · 2 columns

COLUMN_NAME	DATA_TYPE
ROW_ID	NUMBER
WHAT_IT_IS	TEXT
RAW_TEXT	TEXT

ROW_ID is a number and the other two columns are TEXT. So Snowflake has no idea that any of this is JSON. As far as it is concerned we have stored some strings.

Let's start with PARSE_JSON on a row we know is good

PARSE_JSON takes text and gives back a VARIANT. Let's run it on the first two rows, ask TYPEOF what we got, and then read a value out of it.

Query

SELECT ROW_ID, WHAT_IT_IS,
       PARSE_JSON(RAW_TEXT)               AS PARSED,
       TYPEOF(PARSE_JSON(RAW_TEXT))       AS ITS_TYPE,
       PARSE_JSON(RAW_TEXT):sku::VARCHAR  AS SKU
FROM JSON_TEXT_RAW
WHERE ROW_ID IN (1, 2)
ORDER BY ROW_ID;

Result2 rows · 5 columns

ROW_ID	WHAT_IT_IS	PARSED	ITS_TYPE	SKU
1	valid object	{ "price": 300, "qty": 8, "sku": "A-100" }	OBJECT	A-100
2	valid, other shape	{ "note": "back-ordered", "price": 450, "sku": "B-220" }	OBJECT	B-220

Both rows came back as an OBJECT, and the path :sku works on both of them. That is the whole job of PARSE_JSON in one grid: characters in, a document out, and now we can navigate it.

Have a look at the PARSED column as well. The text we put in had no line breaks in it. What came back is laid out over several lines with the keys in a different order. We will come back to that at the end of the post, because it matters more than it looks.

Now let's remember that JSON is not only objects

Most JSON you meet is an object with curly braces. It does not have to be. An array is a document on its own, so is a single number, and so is the word null.

Let's parse the three rows in our table that are not objects.

Query

SELECT ROW_ID, WHAT_IT_IS, RAW_TEXT,
       PARSE_JSON(RAW_TEXT)         AS PARSED,
       TYPEOF(PARSE_JSON(RAW_TEXT)) AS ITS_TYPE
FROM JSON_TEXT_RAW
WHERE ROW_ID IN (7, 8, 11)
ORDER BY ROW_ID;

Result3 rows · 5 columns

ROW_ID	WHAT_IT_IS	RAW_TEXT	PARSED	ITS_TYPE
7	valid array	[1,2,3]	[ 1, 2, 3 ]	ARRAY
8	valid bare scalar	42	42	INTEGER
11	the text null	null	null	NULL_VALUE

Three different types out of three pieces of text. ARRAY for the array, INTEGER for the bare number, and NULL_VALUE for the text null.

That last one is worth slowing down on. NULL_VALUE is not the same as a SQL NULL. It means this document parsed successfully and what it says is null. We will prove that with a query a bit further down.

So PARSE_JSON gives you whatever the text describes. It does not require an object.

Now let's run PARSE_JSON over the whole table

We have parsed the rows we picked out by hand. Let's do what you would actually do with a load and run it over every row at once.

Query

SELECT ROW_ID, PARSE_JSON(RAW_TEXT) AS SHOULD_NOT_GET_THIS_FAR
FROM JSON_TEXT_RAW
ORDER BY ROW_ID;

Query failed — Snowflake returned an error

100069 (22P02): Error parsing JSON: incomplete object value, pos 22

That is the error, and the important part is what we did not get back. Not ten rows and a warning about the eleventh. Nothing. Zero rows.

The complaint names a position, so we can go and find the text it is complaining about. It is the truncated row, and on its own it fails the same way.

Query

SELECT PARSE_JSON('{"sku":"F-600","qty":') AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100069 (22P02): Error parsing JSON: incomplete object value, pos 22

Same error, same position. One piece of bad text in a table of eleven rows stopped the whole statement.

One bad row stops everything

This is the behaviour to plan around. PARSE_JSON is strict about failing, and a SELECT is all or nothing. If you point it at a staging table of JSON text and one row in a million is broken, you do not get the other rows. You get an error message.

Let's use TRY_PARSE_JSON instead

Snowflake gives us a safe version. TRY_PARSE_JSON parses the text when it can and returns NULL when it cannot, instead of raising.

Let's run it over all eleven rows, and ask on every row whether the answer came back NULL.

Query

SELECT ROW_ID, WHAT_IT_IS,
       TRY_PARSE_JSON(RAW_TEXT)                  AS PARSED,
       TRY_PARSE_JSON(RAW_TEXT) IS NULL          AS CAME_BACK_NULL,
       TYPEOF(TRY_PARSE_JSON(RAW_TEXT))          AS ITS_TYPE
FROM JSON_TEXT_RAW
ORDER BY ROW_ID;

Result11 rows · 5 columns

ROW_ID	WHAT_IT_IS	PARSED	CAME_BACK_NULL	ITS_TYPE
1	valid object	{ "price": 300, "qty": 8, "sku": "A-100" }	FALSE	OBJECT
2	valid, other shape	{ "note": "back-ordered", "price": 450, "sku": "B-220" }	FALSE	OBJECT
3	trailing comma	{ "qty": 3, "sku": "C-310" }	FALSE	OBJECT
4	single quotes	{ "qty": 12, "sku": "D-400" }	FALSE	OBJECT
5	unquoted key	{ "qty": 8, "sku": "E-505" }	FALSE	OBJECT
6	truncated	NULL	TRUE	NULL
7	valid array	[ 1, 2, 3 ]	FALSE	ARRAY
8	valid bare scalar	42	FALSE	INTEGER
9	NULL, no text	NULL	TRUE	NULL
10	empty string	NULL	TRUE	NULL
11	the text null	null	FALSE	NULL_VALUE

It ran. No error, and every row came back. Now read down the CAME_BACK_NULL column and count the rows that say TRUE. There are three of them: rows six, nine and ten.

Row six is the one we expected, because that is the truncated document. The other two are not what you would guess. Row nine had no text at all, and row ten was an empty string.

So we have swapped one problem for a quieter one. The statement runs now, but a NULL in that column does not tell us which of those three things happened.

So what does a NULL from TRY_PARSE_JSON actually mean?

Let's count the difference rather than talk about it. One query, four counts: how many rows there are, how many came back NULL, how many are genuinely broken according to CHECK_JSON, how many had no text, and how many were valid and still came back NULL.

Query

SELECT COUNT(*)                                       AS ROWS_TOTAL,
       COUNT_IF(TRY_PARSE_JSON(RAW_TEXT) IS NULL)     AS TRY_RETURNED_NULL,
       COUNT_IF(CHECK_JSON(RAW_TEXT) IS NOT NULL)     AS ACTUALLY_BROKEN,
       COUNT_IF(RAW_TEXT IS NULL)                     AS NO_TEXT_AT_ALL,
       COUNT_IF(CHECK_JSON(RAW_TEXT) IS NULL
                AND TRY_PARSE_JSON(RAW_TEXT) IS NULL) AS VALID_BUT_STILL_NULL
FROM JSON_TEXT_RAW;

Result1 row · 5 columns

ROWS_TOTAL	TRY_RETURNED_NULL	ACTUALLY_BROKEN	NO_TEXT_AT_ALL	VALID_BUT_STILL_NULL
11	3	1	1	2

Three rows came back NULL and only one of them was broken. One had no text to parse. And two were perfectly valid JSON that still produced a NULL.

Let's check that claim about validity directly, because it is the surprising bit. We will ask CHECK_JSON and TRY_PARSE_JSON the same question about an empty string, a SQL NULL and a string of spaces.

Query

SELECT CHECK_JSON('')                        AS EMPTY_COMPLAINT,
       CHECK_JSON('') IS NULL                AS EMPTY_CALLED_VALID,
       TRY_PARSE_JSON('')                    AS EMPTY_PARSED,
       TRY_PARSE_JSON('') IS NULL            AS EMPTY_GAVE_NULL,
       CHECK_JSON(NULL::VARCHAR) IS NULL     AS NULL_CALLED_VALID,
       TRY_PARSE_JSON(NULL::VARCHAR) IS NULL AS NULL_GAVE_NULL,
       CHECK_JSON('   ') IS NULL             AS SPACES_CALLED_VALID,
       TRY_PARSE_JSON('   ') IS NULL         AS SPACES_GAVE_NULL;

Result1 row · 8 columns · scroll →

EMPTY_COMPLAINT	EMPTY_CALLED_VALID	EMPTY_PARSED	EMPTY_GAVE_NULL	NULL_CALLED_VALID	NULL_GAVE_NULL	SPACES_CALLED_VALID	SPACES_GAVE_NULL
NULL	TRUE	NULL	TRUE	TRUE	TRUE	TRUE	TRUE

CHECK_JSON has no complaint about any of them. It calls an empty string valid, a NULL valid and a space valid. And TRY_PARSE_JSON returns NULL for all three.

One answer, three questions

So a NULL out of TRY_PARSE_JSON means one of three things: the text was broken, there was no text, or the text was empty. Three different facts and one answer for all of them. That is fine in a worksheet and it is not fine in a load, because the three need different handling.

Can we tell a broken row from a valid one?

Not from the NULL, no. Let's put the truncated row next to the row holding the text null and ask everything we have of both of them.

Query

SELECT ROW_ID, WHAT_IT_IS, RAW_TEXT,
       TRY_PARSE_JSON(RAW_TEXT) IS NULL        AS TRY_SAYS_NULL,
       IS_NULL_VALUE(TRY_PARSE_JSON(RAW_TEXT)) AS IS_NULL_VALUE_SAYS,
       TYPEOF(TRY_PARSE_JSON(RAW_TEXT))        AS TYPEOF_SAYS,
       CHECK_JSON(RAW_TEXT)                    AS CHECK_JSON_SAYS
FROM JSON_TEXT_RAW
WHERE ROW_ID IN (6, 11)
ORDER BY ROW_ID;

Result2 rows · 7 columns · scroll →

ROW_ID	WHAT_IT_IS	RAW_TEXT	TRY_SAYS_NULL	IS_NULL_VALUE_SAYS	TYPEOF_SAYS	CHECK_JSON_SAYS
6	truncated	{"sku":"F-600","qty":	TRUE	NULL	NULL	incomplete object value, pos 22
11	the text null	null	FALSE	TRUE	NULL_VALUE	NULL

These two rows disagree on every column, and that is the point. Row six is broken, so TRY_PARSE_JSON gave NULL and CHECK_JSON gave a complaint. Row eleven parsed fine, so TRY_SAYS_NULL is FALSE, IS_NULL_VALUE is TRUE and CHECK_JSON has nothing to say.

The last column is the one that separates them. Only CHECK_JSON gives back a reason.

The text null is not a SQL NULL

Let's look at that row on its own, because it is the mirror image of the empty string we just saw.

Query

SELECT TRY_PARSE_JSON('null')                     AS WHAT_CAME_BACK,
       TRY_PARSE_JSON('null') IS NULL             AS IS_IT_SQL_NULL,
       IS_NULL_VALUE(TRY_PARSE_JSON('null'))      AS IS_IT_JSON_NULL,
       TYPEOF(TRY_PARSE_JSON('null'))             AS ITS_TYPE,
       TRY_PARSE_JSON('null')::VARCHAR            AS CAST_TO_VARCHAR,
       TRY_PARSE_JSON('NULL') IS NULL             AS UPPERCASE_NULL_IS_SQL_NULL,
       CHECK_JSON('NULL')                         AS UPPERCASE_COMPLAINT;

Result1 row · 7 columns · scroll →

WHAT_CAME_BACK	IS_IT_SQL_NULL	IS_IT_JSON_NULL	ITS_TYPE	CAST_TO_VARCHAR	UPPERCASE_NULL_IS_SQL_NULL	UPPERCASE_COMPLAINT
null	FALSE	TRUE	NULL_VALUE	NULL	FALSE	NULL

Is it a SQL NULL? FALSE. Is it a JSON null? TRUE. Its type is NULL_VALUE, and casting it to VARCHAR does give a SQL NULL.

Have a look at the last two columns as well. NULL in capitals is not JSON, so it does not parse to a JSON null. It comes back as a SQL NULL and CHECK_JSON still has no complaint about it.

So the row holding valid null text survives a NULL filter, while the two valid rows with nothing in them do not. That is the wrong way round, and it is exactly what we are about to measure.

Now let's ask CHECK_JSON for the reason

CHECK_JSON works the opposite way round from the other two. It returns NULL when the text is fine, and a complaint when it is not.

Let's run it over every row.

Query

SELECT ROW_ID, WHAT_IT_IS,
       CHECK_JSON(RAW_TEXT)               AS THE_COMPLAINT,
       CHECK_JSON(RAW_TEXT) IS NULL       AS IT_IS_VALID
FROM JSON_TEXT_RAW
ORDER BY ROW_ID;

Result11 rows · 4 columns

ROW_ID	WHAT_IT_IS	THE_COMPLAINT	IT_IS_VALID
1	valid object	NULL	TRUE
2	valid, other shape	NULL	TRUE
3	trailing comma	NULL	TRUE
4	single quotes	NULL	TRUE
5	unquoted key	NULL	TRUE
6	truncated	incomplete object value, pos 22	FALSE
7	valid array	NULL	TRUE
8	valid bare scalar	NULL	TRUE
9	NULL, no text	NULL	TRUE
10	empty string	NULL	TRUE
11	the text null	NULL	TRUE

Read down the THE_COMPLAINT column. Ten of the eleven cells are empty and one is not. Row six carries the reason, and it is the same text the failed statement gave us earlier.

That is a column you can put in a table and look at later. Let's turn it into the report you want before you load anything.

Query

SELECT COUNT(*)                                   AS ROWS_TOTAL,
       COUNT_IF(CHECK_JSON(RAW_TEXT) IS NULL)     AS VALID_JSON,
       COUNT_IF(CHECK_JSON(RAW_TEXT) IS NOT NULL) AS INVALID_JSON,
       COUNT(DISTINCT CHECK_JSON(RAW_TEXT))       AS DISTINCT_COMPLAINTS
FROM JSON_TEXT_RAW;

Result1 row · 4 columns

ROWS_TOTAL	VALID_JSON	INVALID_JSON	DISTINCT_COMPLAINTS
11	10	1	1

Eleven rows, ten of them valid JSON, one invalid, and one distinct complaint across the whole table. If that last number had come back higher, we would have more than one kind of problem to fix.

Let's load every row and keep the complaint

Now we have enough to write a load that does not throw anything away. We keep the raw text, the parsed document, the complaint and a flag saying whether the row was valid.

Query

CREATE OR REPLACE TABLE JSON_LOADED AS
SELECT ROW_ID,
       WHAT_IT_IS,
       RAW_TEXT,
       TRY_PARSE_JSON(RAW_TEXT)                     AS DOC,
       CHECK_JSON(RAW_TEXT)                         AS PARSE_COMPLAINT,
       CHECK_JSON(RAW_TEXT) IS NULL                 AS IS_VALID
FROM JSON_TEXT_RAW;

Result1 row · 1 column

status
Table JSON_LOADED successfully created.

Nothing is filtered in that statement. There is no WHERE clause at all. The bad row goes in with its reason attached.

Let's see what landed in the table.

Query

SELECT ROW_ID, WHAT_IT_IS, IS_VALID, PARSE_COMPLAINT, TYPEOF(DOC) AS DOC_TYPE
FROM JSON_LOADED
ORDER BY ROW_ID;

Result11 rows · 5 columns

ROW_ID	WHAT_IT_IS	IS_VALID	PARSE_COMPLAINT	DOC_TYPE
1	valid object	TRUE	NULL	OBJECT
2	valid, other shape	TRUE	NULL	OBJECT
3	trailing comma	TRUE	NULL	OBJECT
4	single quotes	TRUE	NULL	OBJECT
5	unquoted key	TRUE	NULL	OBJECT
6	truncated	FALSE	incomplete object value, pos 22	NULL
7	valid array	TRUE	NULL	ARRAY
8	valid bare scalar	TRUE	NULL	INTEGER
9	NULL, no text	TRUE	NULL	NULL
10	empty string	TRUE	NULL	NULL
11	the text null	TRUE	NULL	NULL_VALUE

Every row made it. Exactly one row says FALSE under IS_VALID, and it is row six, with the complaint sitting in the next column.

The DOC_TYPE column is useful too. It tells us what each row parsed into: objects for the first few rows, then ARRAY, then INTEGER. Rows nine and ten are NULL, because there was nothing to parse. Row eleven is NULL_VALUE, because there was something to parse and it said null.

And let's prove nothing was lost on the way in.

Query

SELECT (SELECT COUNT(*) FROM JSON_TEXT_RAW)               AS ROWS_IN,
       (SELECT COUNT(*) FROM JSON_LOADED)                 AS ROWS_OUT,
       (SELECT COUNT_IF(IS_VALID) FROM JSON_LOADED)       AS VALID_ROWS,
       (SELECT COUNT_IF(NOT IS_VALID) FROM JSON_LOADED)   AS INVALID_ROWS,
       (SELECT COUNT(*) FROM JSON_TEXT_RAW)
         = (SELECT COUNT(*) FROM JSON_LOADED)             AS NOTHING_LOST;

Result1 row · 5 columns

ROWS_IN	ROWS_OUT	VALID_ROWS	INVALID_ROWS	NOTHING_LOST
11	11	10	1	TRUE

Eleven rows in, eleven rows out, ten valid and one invalid. NOTHING_LOST is TRUE.

Now compare that to the filter people actually write

The usual way to handle this is to parse with TRY_PARSE_JSON and then add WHERE DOC IS NOT NULL. It looks harmless. Let's count what it costs.

Query

SELECT COUNT(*)                                 AS ROWS_KEPT,
       (SELECT COUNT(*) FROM JSON_LOADED)        AS ROWS_IN_TABLE,
       (SELECT COUNT(*) FROM JSON_LOADED)
         - COUNT(*)                             AS ROWS_DROPPED
FROM JSON_LOADED
WHERE DOC IS NOT NULL;

Result1 row · 3 columns

ROWS_KEPT	ROWS_IN_TABLE	ROWS_DROPPED
8	11	3

Eight rows kept out of eleven. Three rows dropped, and only one of those three was bad. The other two were valid rows that happened to have nothing to parse.

That is the whole argument for keeping the complaint column. With PARSE_COMPLAINT in the table you can write WHERE IS_VALID and mean it. Without it, the only tool you have is a NULL test, and a NULL test cannot tell a broken row from an empty one.

Now the part that surprises people: this is not a strict parser

Three rows in our table break the JSON spec and parsed anyway, without a word of complaint. Let's go through them one at a time.

First the trailing comma, which is row three.

Query

SELECT PARSE_JSON('{"sku":"C-310","qty":3,}')          AS WHAT_CAME_BACK,
       TYPEOF(PARSE_JSON('{"sku":"C-310","qty":3,}'))  AS ITS_TYPE,
       PARSE_JSON('{"sku":"C-310","qty":3,}'):sku::VARCHAR AS THE_SKU,
       PARSE_JSON('{"sku":"C-310","qty":3,}'):qty::INT     AS THE_QTY,
       CHECK_JSON('{"sku":"C-310","qty":3,}')          AS CHECK_JSON_COMPLAINT;

Result1 row · 5 columns

WHAT_CAME_BACK	ITS_TYPE	THE_SKU	THE_QTY	CHECK_JSON_COMPLAINT
{ "qty": 3, "sku": "C-310" }	OBJECT	C-310	3	NULL

It parsed, it is an OBJECT, both paths work and CHECK_JSON has no complaint. The trailing comma is simply gone.

Now row four, which uses single quotes instead of double quotes.

Query

SELECT PARSE_JSON('{''sku'':''D-400'',''qty'':12}')         AS WHAT_CAME_BACK,
       PARSE_JSON('{''sku'':''D-400'',''qty'':12}'):sku::VARCHAR AS THE_SKU,
       PARSE_JSON('{''sku'':''D-400'',''qty'':12}'):qty::INT     AS THE_QTY,
       CHECK_JSON('{''sku'':''D-400'',''qty'':12}')         AS CHECK_JSON_COMPLAINT;

Result1 row · 4 columns

WHAT_CAME_BACK	THE_SKU	THE_QTY	CHECK_JSON_COMPLAINT
{ "qty": 12, "sku": "D-400" }	D-400	12	NULL

Also fine, and look at the document that came back. The keys and the value are in double quotes now. Snowflake read the single quotes and stored proper JSON.

And row five, where the key is not quoted at all.

Query

SELECT PARSE_JSON('{sku:"E-505","qty":8}')             AS WHAT_CAME_BACK,
       PARSE_JSON('{sku:"E-505","qty":8}'):sku::VARCHAR AS THE_SKU,
       OBJECT_KEYS(PARSE_JSON('{sku:"E-505","qty":8}')) AS ITS_KEYS,
       CHECK_JSON('{sku:"E-505","qty":8}')             AS CHECK_JSON_COMPLAINT;

Result1 row · 4 columns

WHAT_CAME_BACK	THE_SKU	ITS_KEYS	CHECK_JSON_COMPLAINT
{ "qty": 8, "sku": "E-505" }	E-505	[ "qty", "sku" ]	NULL

Fine again, and OBJECT_KEYS shows the key came through as sku with nothing odd about it.

Are those the same value as the strict spelling?

This is the question that matters. Loose text that happens to parse is one thing. Loose text that produces exactly the same value is another. Let's compare those three spellings, plus one with extra whitespace in it, against the properly quoted version with =.

Query

SELECT PARSE_JSON('{"sku":"X","qty":1}')  = PARSE_JSON('{"sku":"X","qty":1,}')
         AS TRAILING_COMMA_SAME,
       PARSE_JSON('{"sku":"X","qty":1}')  = PARSE_JSON('{''sku'':''X'',''qty'':1}')
         AS SINGLE_QUOTES_SAME,
       PARSE_JSON('{"sku":"X","qty":1}')  = PARSE_JSON('{sku:"X","qty":1}')
         AS UNQUOTED_KEY_SAME,
       PARSE_JSON('{"sku":"X","qty":1}')  = PARSE_JSON('{ "sku" : "X" , "qty" : 1 }')
         AS WHITESPACE_SAME;

Result1 row · 4 columns

TRAILING_COMMA_SAME	SINGLE_QUOTES_SAME	UNQUOTED_KEY_SAME	WHITESPACE_SAME
TRUE	TRUE	TRUE	TRUE

TRUE every time. Not close enough, not nearly the same. The same value.

Do not use this as a validator

So text that your application would reject, that a linter would reject, and that most JSON libraries would reject, loads in here clean and produces the right answer. That is convenient when you are handed messy files. It also means Snowflake is not your JSON validator. If something upstream needs to know that a payload was well formed, do not conclude it from the fact that Snowflake parsed it.

So what does Snowflake refuse?

It is not that anything goes. Let's put a row of awkward cases through CHECK_JSON and read the complaints.

Query

SELECT CHECK_JSON('{"sku":"X" /* a comment */ }')  AS BLOCK_COMMENT,
       CHECK_JSON('{"sku":"X" // a comment
}')                                                 AS LINE_COMMENT,
       CHECK_JSON('{"sku":"X",}')                   AS TRAILING_COMMA,
       CHECK_JSON('[1,2,3,]')                       AS TRAILING_COMMA_IN_ARRAY,
       CHECK_JSON('{"a":1}{"b":2}')                 AS TWO_DOCUMENTS,
       CHECK_JSON('{"a":1')                         AS UNCLOSED,
       CHECK_JSON('NaN')                            AS NOT_A_NUMBER,
       CHECK_JSON('{"a":01}')                       AS LEADING_ZERO;

Result1 row · 8 columns · scroll →

BLOCK_COMMENT	LINE_COMMENT	TRAILING_COMMA	TRAILING_COMMA_IN_ARRAY	TWO_DOCUMENTS	UNCLOSED	NOT_A_NUMBER	LEADING_ZERO
invalid character outside of a string: '/', pos 12	invalid character outside of a string: '/', pos 12	NULL	NULL	more than one document in the input	incomplete object value, pos 7	NULL	NULL

Four of the eight are refused. A block comment and a line comment both give the same complaint about an invalid character outside a string, at the same position. Two documents in one string gives its own message. An unclosed brace gives the incomplete-object complaint we have already met.

The other four are accepted. A trailing comma in an object, a trailing comma in an array, NaN and a number written with a leading zero all come back with no complaint at all.

Now let's actually parse the ones it accepted, because “no complaint” and “the value you expected” are not the same promise.

Query

SELECT TRY_PARSE_JSON('{"sku":"X",}')            AS TRAILING_COMMA,
       TRY_PARSE_JSON('[1,2,3,]')                AS TRAILING_COMMA_ARRAY,
       TRY_PARSE_JSON('{"a":1}{"b":2}')          AS TWO_DOCUMENTS,
       TRY_PARSE_JSON('{"a":1')                  AS UNCLOSED,
       TRY_PARSE_JSON('NaN')                     AS NOT_A_NUMBER,
       TRY_PARSE_JSON('{"a":01}')                AS LEADING_ZERO;

Result1 row · 6 columns · scroll →

TRAILING_COMMA	TRAILING_COMMA_ARRAY	TWO_DOCUMENTS	UNCLOSED	NOT_A_NUMBER	LEADING_ZERO
{ "sku": "X" }	[ 1, 2, 3, undefined ]	NULL	NULL	NaN	{ "a": 1 }

The object with the trailing comma comes back with one key and nothing else. NaN comes back as NaN. The leading zero comes back as a plain 1. The two that CHECK_JSON refused come back as NULL, which is TRY_PARSE_JSON doing its job.

And then there is the array. Look at TRAILING_COMMA_ARRAY carefully. The text we handed in was [1,2,3,], and the value printed back has a fourth entry in it reading undefined.

The one to remember

CHECK_JSON has no complaint about [1,2,3,], and the value TRY_PARSE_JSON gives back for it prints an extra entry that was never in the text. A trailing comma inside an object is dropped. A trailing comma inside an array is not. This is the result from this episode I would actually remember.

Now let's go the other way with TO_JSON

We have been turning text into documents. TO_JSON turns a document back into text. So does TO_VARCHAR, and they are not the same thing.

Let's take one string value out of a parsed document and run both of them on it, with the length of each answer.

Query

SELECT PAYLOAD:user.name                        AS AS_VARIANT,
       TO_JSON(PAYLOAD:user.name)               AS TO_JSON_,
       TO_VARCHAR(PAYLOAD:user.name)            AS TO_VARCHAR_,
       LENGTH(TO_JSON(PAYLOAD:user.name))       AS TO_JSON_LENGTH,
       LENGTH(TO_VARCHAR(PAYLOAD:user.name))    AS TO_VARCHAR_LENGTH
FROM EVENTS_JSON
WHERE EVENT_ID = 1;

Result1 row · 5 columns

AS_VARIANT	TO_JSON_	TO_VARCHAR_	TO_JSON_LENGTH	TO_VARCHAR_LENGTH
"Northwind Traders"	"Northwind Traders"	Northwind Traders	19	17

TO_JSON gave us nineteen characters and TO_VARCHAR gave us seventeen. The two extra characters are the double quotes.

That is the whole difference. In JSON the quotes are what make it a string, so TO_JSON keeps them. TO_VARCHAR hands you the string itself. Pick by what you are going to do with the answer.

Over a whole document, TO_JSON gives you one line of text. Let's run it on the four event payloads.

Query

SELECT EVENT_ID,
       TO_JSON(PAYLOAD)          AS AS_JSON_TEXT,
       LENGTH(TO_JSON(PAYLOAD))  AS ITS_LENGTH
FROM EVENTS_JSON
ORDER BY EVENT_ID;

Result4 rows · 3 columns

EVENT_ID	AS_JSON_TEXT	ITS_LENGTH
1	{"amount":4200,"device":{"os":"windows","type":"desktop"},"event":"purchase","items":[{"price":300,"qty":8,"sku":"A-100"},{"price":450,"qty":4,"sku":"B-220"}],"tags":["priority","repeat"],"user":{"id":101,"name":"Northwind Traders"}}	233
2	{"amount":890.5,"device":{"os":"ios","type":"mobile"},"event":"purchase","items":[{"price":296.83,"qty":3,"sku":"C-310"}],"tags":["new"],"user":{"id":102,"name":"Blue Ridge Supply"}}	182
3	{"amount":"1500.00","event":"refund","items":[{"price":300,"qty":5,"sku":"A-100"}],"tags":[],"user":{"id":103,"name":"Kestrel Analytics"}}	138
4	{"amount":9250,"device":{"os":"android","type":"tablet"},"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"],"user":{"id":107,"name":"Meridian Health"}}	284

Four documents, four strings, each on one line with its length beside it. That output looks very much like the kind of text we started this post with, which raises an obvious question.

One last thing: does the text come back the way you sent it?

Let's test it properly. We will parse a piece of text, write it straight back out with TO_JSON, and compare the result to the text we started with.

Query

SELECT PARSE_JSON('{"event":"purchase","user":{"id":101},"amount":4200}')
         AS WHAT_CAME_BACK,
       TO_JSON(PARSE_JSON('{"event":"purchase","user":{"id":101},"amount":4200}'))
         AS AS_TEXT_AGAIN,
       TO_JSON(PARSE_JSON('{"event":"purchase","user":{"id":101},"amount":4200}'))
         = '{"event":"purchase","user":{"id":101},"amount":4200}'
         AS SAME_TEXT;

Result1 row · 3 columns

WHAT_CAME_BACK	AS_TEXT_AGAIN	SAME_TEXT
{ "amount": 4200, "event": "purchase", "user": { "id": 101 } }	{"amount":4200,"event":"purchase","user":{"id":101}}	FALSE

SAME_TEXT is FALSE. Compare the two columns and you can see why. The keys have come back in alphabetical order rather than the order we wrote them.

Whitespace goes too. Let's measure that one on its own.

Query

SELECT LENGTH('{ "a" : 1 }')                            AS TEXT_IN_LENGTH,
       LENGTH(TO_JSON(PARSE_JSON('{ "a" : 1 }')))       AS TEXT_OUT_LENGTH,
       TO_JSON(PARSE_JSON('{ "a" : 1 }'))               AS TEXT_OUT,
       PARSE_JSON('{ "a" : 1 }') = PARSE_JSON('{"a":1}') AS SAME_VALUE;

Result1 row · 4 columns

TEXT_IN_LENGTH	TEXT_OUT_LENGTH	TEXT_OUT	SAME_VALUE
11	7	{"a":1}	TRUE

Eleven characters in and seven characters out. The spaces are gone, and the last column says the value is the same anyway.

So the text is not preserved. The next question is whether anything was actually lost.

Query

SELECT PARSE_JSON(TO_JSON(PARSE_JSON('{"event":"purchase","amount":4200}')))
         = PARSE_JSON('{"event":"purchase","amount":4200}')      AS VALUES_MATCH,
       PARSE_JSON('{"a":1,"b":2}') = PARSE_JSON('{"b":2,"a":1}') AS ORDER_IRRELEVANT,
       TO_JSON(PARSE_JSON('{"a":1,"b":2}'))
         = TO_JSON(PARSE_JSON('{"b":2,"a":1}'))                  AS TEXT_ALSO_MATCHES;

Result1 row · 3 columns

VALUES_MATCH	ORDER_IRRELEVANT	TEXT_ALSO_MATCHES
TRUE	TRUE	TRUE

TRUE, TRUE, TRUE. The value survives the round trip exactly. Key order makes no difference to equality. And two documents written with their keys in different orders produce identical text.

That last column is the useful one. TO_JSON gives you a canonical form: the value, spelled one fixed way, with sorted keys and no whitespace. Which is exactly what you want for comparing two documents, and exactly wrong for auditing what somebody sent you.

If you need the bytes you were sent, keep the text column you were sent. That is why the load earlier in this post kept RAW_TEXT alongside the parsed document.

Key points

• PARSE_JSON turns text into a VARIANT, and it is strict about failing. One bad row in the table and the statement returns no rows at all.
• JSON is not only objects. An array, a bare number and the word null are all documents, and they parse to ARRAY, INTEGER and NULL_VALUE.
• TRY_PARSE_JSON returns NULL instead of raising, but a NULL from it means three different things: broken text, no text, or empty text.
• CHECK_JSON is the one that tells you which. It returns the complaint when the text is bad and NULL when it is fine.
• Load every row and keep the complaint beside the document. Here that was eleven rows in and eleven rows out, with the one bad row carrying its own reason.
• WHERE DOC IS NOT NULL kept eight of our eleven rows, and two of the three it dropped were perfectly valid.
• A trailing comma, single quotes and an unquoted key all parse in Snowflake, and they produce exactly the same value as the strict spelling. Snowflake is not a JSON validator.
• It does refuse comments, two documents in one string and an unclosed brace. It does not refuse NaN or a leading zero.
• TO_JSON keeps the JSON quotes around a string. TO_VARCHAR gives you the string itself.
• TO_JSON is canonical, not faithful. The value round trips exactly, the text does not, so keep the raw column.

Next in this series

Episode 88 — navigating a document. The colon, the dot and the brackets, GET and GET_PATH, and the one capital letter in a key name that empties a report without raising an error.

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 JSON_TEXT_RAW and EVENTS_JSON data is invented demo data created by that script. Every count in the text above was read off the grid beside it rather than worked out here. Nothing on this page is timed and no performance or benchmark claims are made anywhere in this series. Function behaviour follows the Snowflake PARSE_JSON documentation.