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