SQLite Forum

Unexpected behaviour of the json_valid function and .json mode in CLI
Login

Unexpected behaviour of the json_valid function and .json mode in CLI

(1) By anonymous on 2020-09-05 19:15:00 [link] [source]

I have been experimenting with JSON in SQLite and encountered some unexpected behaviour using SQLite 3.33.0 in Windows.

The following setup:

.mode box
.nullvalue '?'
Drop Table If Exists JTest;
Create Table JTest (Testcase Integer, Value VarChar, Expected Integer);
Insert Into JTest Values (1,'{}',1);
Insert Into JTest Values (2,'{"key":"This is a test"}',1);
Insert Into JTest Values (3,'',0);
Insert Into JTest Values (4,null,0);
Insert Into JTest Values (5,'NotJSON',0);
Insert Into JTest Values (6,42,0);
Select Testcase,Expected,JSON_Valid(Value) As Result,Value From JTest;

gives this output:

┌──────────┬──────────┬────────┬──────────────────────────┐
│ Testcase │ Expected │ Result │          Value           │
├──────────┼──────────┼────────┼──────────────────────────┤
│ 1        │ 1        │ 1      │ {}                       │
│ 2        │ 1        │ 1      │ {"key":"This is a test"} │
│ 3        │ 0        │ 0      │                          │
│ 4        │ 0        │ 0      │ ?                        │
│ 5        │ 0        │ 0      │ NotJSON                  │
│ 6        │ 0        │ 1      │ 42                       │
└──────────┴──────────┴────────┴──────────────────────────┘

All except testcase 6 are as expected.
Testcase 6 however is an integer, it should not be considered valid JSON.

Exploring the .json mode in the CLI yields another unexpected result:

.mode json
.nullvalue '?'
Drop Table If Exists JTest;
Create Table JTest (Testcase Integer, Value VarChar);
Insert Into JTest Values (1,'{}');
Insert Into JTest Values (2,'{"key":"This is a test"}');
Insert Into JTest Values (3,'');
Insert Into JTest Values (4,null);
Insert Into JTest Values (5,'NotJSON');
Insert Into JTest Values (6,42);
Select Testcase,Value From JTest;

output is:

[{"Testcase":1,"Value":"{}"},
{"Testcase":2,"Value":"{\"key\":\"This is a test\"}"},
{"Testcase":3,"Value":""},
{"Testcase":4,"Value":null},
{"Testcase":5,"Value":"NotJSON"},
{"Testcase":6,"Value":"42"}]

But expected was:

[{"Testcase":1,"Value":"{}"},
{"Testcase":2,"Value":{"key":"This is a test"}},
{"Testcase":3,"Value":""},
{"Testcase":4,"Value":null},
{"Testcase":5,"Value":"NotJSON"},
{"Testcase":6,"Value":42}]

Testcase 2 is already valid JSON, it should copied as-is and not be subjected to the equivalent of json_quote.
Testcase 6 is an integer, but is wrongly encoded as a string

(2) By Stephan Beal (stephan) on 2020-09-05 19:56:53 in reply to 1 [link] [source]

Testcase 6 however is an integer, it should not be considered valid JSON.

Technically correct, but try this in your browser dev tools:

JSON.parse(1)
1
JSON.parse("42")
42

If it's good enough for Firefox and Chrome, it's very probably good enough for sqlite :).

(3) By anonymous on 2020-09-05 20:12:17 in reply to 2 [link] [source]

I see your point.

However, if I read https://www.json.org/json-en.html correctly, 42 would be a valid JSON value, but not a valid JSON object.

And https://www.sqlite.org/json1.html#jvalid would lead one to expect json_valid to test the validity of a JSON object.

(4) By Larry Brasfield (LarryBrasfield) on 2020-09-05 20:38:05 in reply to 3 [link] [source]

According to the doc for json_valid(), "The json_valid(X) function return 1 if the argument X is well-formed JSON".

The function first converts its argument to a string (via sqlite3_text_value()), which results in then testing '42' to see if it is well-formed JSON. According to the JSON spec you cited, it is. (See railroad chart for value, branch number.) The json_valid() function says it is valid JSON, it is actually valid JSON, hence there is no problem and the behavior should be expected.

(5) By David Raymond (dvdraymond) on 2020-09-08 12:51:59 in reply to 1 [link] [source]

Testcase 6 is an integer, but is wrongly encoded as a string

Nope, it's a string. Datatypes in SQLite Version 3 You declared the "Value" column as VarChar, so the column has TEXT affinity. "A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored." So you put in the integer 42, and it was stored in the database as the string '42'

(6) By anonymous on 2020-09-09 12:47:24 in reply to 5 [link] [source]

Thanks, that explains that issue.
I checked the code in shell.c and the 'translation' to JSON is indeed based on the declared affinity.
So for what I want I can't use .mode json but will have to write my own query.

(7) By anonymous on 2020-09-09 13:00:16 in reply to 4 [source]

Thanks, that explains it.
Basically it is an Expectation Management Issue: based on the example in the documentation I expected json_valid to test the validaty of a JSON OBJECT, however in reality it only tests for well-formed JSON.
Having my expectations now correctly managed I now know how to fix the query.