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 [link] [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 [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!