Using json_extract in create table for generated fields
(1) By anonymous on 2021-05-15 05:38:33 [link]
In a schema file from mysql I have a generated field like so: id VARCHAR(64) GENERATED ALWAYS AS (myjson->"$.id") STORED I'm trying to port this to sqlite3 and can't seem to figure out how to do it. I tried using json_extract(myjson, "$.id") but json_extract only seems to work in a SELECT statement. Would appreciate any ideas / pointers. Thanks!
(2) By Richard Hipp (drh) on 2021-05-15 11:41:22 in reply to 1 [link]
The string literal `'$.id'` should be in single-quotes, not double-quotes. Double-quoted string literals are a mysql-ism. > ~~~ CREATE TABLE t1(myjson json, id VARCHAR(64) AS (json_extract(myjson,'$.id'))); INSERT INTO t1(myjson) VALUES('{"a":5,"id":"xyzzy","b":7}'); SELECT id, myjson FROM t1; ~~~
(3) By anonymous on 2021-05-15 14:24:20 in reply to 2
Thank you, Richard. That works! A couple follow up questions <br> 1) I wanted one of my generated keys to be the primary key, but sqlite isn't allowing generated fields to be primary keys. Is there a reason for this? <br> 2) One option is to simply create a secondary index on `id` and not declare it as the primary key. A second work-around is to have the primary key be generated at insert time, e.g. <code> INSERT INTO t1(myjson, id) VALUES('{"foo": "bar"}', (json_extract(myjson, '$.id'))); </code> But I'm unable to refer to other fields in json_extract (the error says `no such column myjson`). The above line works in mysql though. Is there a different syntax for sqlite? Thank you again for the help!
(4) By Harald Hanche-Olsen (hanche) on 2021-05-15 17:22:11 in reply to 3 [link]
You can declare a UNIQUE constraint on a generated field. I believe that results in the creation of an index. Perhaps that is all you need? Here is another possibility: Have a separate VIEW with a trigger to do your insertion into. ``` CREATE TABLE t1( id VARCHAR(64) PRIMARY KEY, myjson TEXT); CREATE VIEW t1j AS SELECT json_insert(myjson,'$.id',id) myjson FROM t1; CREATE TRIGGER t1_ins INSTEAD OF INSERT ON t1j BEGIN INSERT INTO t1(id,myjson) VALUES (json_extract(NEW.myjson,'$.id'),json_remove(NEW.myjson,'$.id')); END; INSERT INTO t1j VALUES ('{"id":"foo","x":42}'), ('{"id":"bar","x":99}'); SELECT * FROM t1; ``` with the result (assuming column mode) ``` id myjson --- -------- foo {"x":42} bar {"x":99} ``` I did a bit of extra work to avoid duplicating the id in the database. The view puts it back.
(5) By Vinay (tunasandwich) on 2021-05-15 20:26:55 in reply to 4 [link]
Thank you for the detailed answer. This is very helpful. Turns out just adding UNIQUE solves my problem!