SQLite Forum

Is `JSON` a valid `create table` type?
Login

Is `JSON` a valid `create table` type?

(1) By example-user on 2020-10-28 22:08:09 [source]

E.g:

CREATE TABLE row_a (
row_a_id INTEGER PRIMARY KEY AUTOINCREMENT,
doc JSON NOT NULL,
);
  • Why does SQLite seem to take any string as a valid create table type?

  • Is JSON a valid type? Ive seen it in example snippets online.

  • Is there any benefit to defining the column types as SQLite seems to accept anything? What if I used SOMEINVALIDTYPE for all of the types?

(2) By Donald Griggs (dfgriggs) on 2020-10-28 22:33:48 in reply to 1 [link] [source]

I think you'll find your answers in the documentation:

https://sqlite.org/quirks.html

https://sqlite.org/datatype3.html

(3) By Keith Medcalf (kmedcalf) on 2020-10-28 23:44:22 in reply to 1 [link] [source]

As documented by the previous response. Your table definition, after correcting syntax errors, results in the following internal schema structure data:

sqlite> CREATE TABLE row_a (
   ...> row_a_id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> doc JSON NOT NULL
   ...> );
sqlite> pragma table_xinfo(row_a);
┌─────┬──────────┬─────────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │   name   │  type   │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼──────────┼─────────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ 0   │ row_a_id │ INTEGER │ INTEGER │      │ 0       │            │ 1  │ 1     │ 1       │ 0      │
│ 1   │ doc      │ JSON    │ NUMERIC │      │ 1       │            │ 0  │ 0     │ 0       │ 0      │
└─────┴──────────┴─────────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘

Note: I have modified the pragma to return additional information from the internal schema information structures and that your results will not contain all the columns.

The "type" can bee whatever you you wish it to be:

sqlite> create table t
   ...> (
   ...>   x Jolly Little Elephant Herding Mice not null,
   ...>   y integer
   ...> );
sqlite> pragma table_xinfo(t);
┌─────┬──────┬────────────────────────────────────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │ name │                type                │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼──────┼────────────────────────────────────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1  │      │                                    │ INTEGER │      │ 0       │            │ 1  │ 1     │ 0       │ 1      │
│ 0   │ x    │ Jolly Little Elephant Herding Mice │ NUMERIC │      │ 1       │            │ 0  │ 0     │ 0       │ 0      │
│ 1   │ y    │ integer                            │ INTEGER │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
└─────┴──────┴────────────────────────────────────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘

Note: I have also modified the pragma to return additional rows such as the implicit rowid which are not returned by the distributed code.

The "column affinity" is set in accordance with the rules set out in https://sqlite.org/datatype3.html and that the "type name" is an arbitrary string.

(4) By example-user on 2020-10-29 00:07:31 in reply to 3 [link] [source]

Thanks Keith thats helpful, it makes sense now.

It seems I would be better off setting TEXT instead of JSON.

(5) By Adrian Ho (lexfiend) on 2020-10-29 02:14:31 in reply to 4 [link] [source]

That's not necessarily true. Storage-wise, it only makes a difference when dealing with bare numbers -- being of NUMERIC affinity, JSON will trigger a numeric conversion, while TEXT will not. Otherwise, there is no difference with typical JSON objects.

OTOH, declaring a JSON column reminds the user/coder what sort of data is expected to go in there. That can be useful in the long term, especially if you're working in a team. ("You" and "you in six months" are one such team.)

(6) By J. King (jking) on 2020-10-29 02:25:08 in reply to 5 [link] [source]

You can also use an affinity_logical-type construction e.g. text_json, giving you the best of both worlds.