Using json_extract in create table for generated fields
(1) By anonymous on 2021-05-15 05:38:33 [link] [source]
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 [source]
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 [link] [source]
Thank you, Richard. That works! A couple follow up questions
- 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?
- 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.
INSERT INTO t1(myjson, id) VALUES('{"foo": "bar"}', (json_extract(myjson, '$.id')));
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] [source]
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] [source]
Thank you for the detailed answer. This is very helpful. Turns out just adding UNIQUE solves my problem!