SQLite Forum

Storing invalid JSON (integer-based keys)?
Login

Storing invalid JSON (integer-based keys)?

(1.1) By Layne Sadler (HashRocketSyntax) on 2020-10-16 18:26:50 edited from 1.0 [link] [source]

Hi there. I am using an ORM for SQLite that makes use of the json1 extension to implement a JSONField() attribute on tables.

sqlite> select json_valid('{1: "k1"}');
0
sqlite> select json_valid('{"1": "k1"}');
1

I forgot that integer-based keys are not valid JSON. It turns out that I can store this invalid JSON. But when I try to fetch it, sometimes it returns as integer keys 0, 1, 2 and sometimes it returns string keys '0', '1', '2'.

Should attempting to store this invalid JSON (integer-based keys) result in an error?

(2.2) By Keith Medcalf (kmedcalf) on 2020-10-16 20:03:54 edited from 2.1 in reply to 1.1 [link] [source]

There is no such thing as a JSON type. JSON is just a text string. If you want to be sure that your TEXT is valid JSON then you will have to CHECK that it is valid JSON, just like a date/time/datetime is just a text string and if you want to make sure that the TEXT is a valid date/time/datetime text string, you need to check that it is valid.

https://sqlite.org/datatype3.html

create table t
(
   id integer primary key,
   json text check (json IS null or json_valid(json))
   dt text check (datetime(dt, '+0 days') IS dt)
);

would prevent you from inserting text that is not valid json into a text field or a datetime that is not a datetime. Otherwise you can pretty much put whatever you like wherever your little heart desires to put it (with very few restrictions) without resulting in an error.

(3) By Layne Sadler (HashRocketSyntax) on 2020-10-16 18:43:11 in reply to 2.1 [source]

Thanks Keith. Sorry for phrasing as if it was a sqlite thing and not stepping through all of the software in between.