sqlite, generated columns and SQLite-CodePudding

I save into an SQLite database a TEXT column with a raw json. The json schema is not unique. I would like to use generated columns to extract values from the raw column

I can do with multiple columns, each using a different path to extract value

CREATE TABLE t (
           raw TEXT,
           c1 TEXT GENERATED ALWAYS AS (json_extract(raw, '$.field1')) VIRTUAL,
           c2 TEXT GENERATED ALWAYS AS (json_extract(raw, '$.field2')) VIRTUAL
         )

result:

sqlite> select * from t;
┌────────────────────────────────────────┬────────┬────┐
│                  raw                   │   c1   │ c2 │
├────────────────────────────────────────┼────────┼────┤
│ {"field1":"value1", "field3":"value2"} │ value1 │    │
└────────────────────────────────────────┴────────┴────┘

or i can have a single column with multiple paths passed to json_extract

CREATE TABLE t1 (
           raw TEXT,
           c1 TEXT GENERATED ALWAYS AS (json_extract(raw, '$.field1', '$.field2')) VIRTUAL   
         )

result

sqlite> select * from t1;
┌────────────────────────────────────────┬─────────────────┐
│                  raw                   │       c1        │
├────────────────────────────────────────┼─────────────────┤
│ {"field1":"value1", "field3":"value2"} │ ["value1",null] │
└────────────────────────────────────────┴─────────────────┘

I prefer the second solution, having one field. but json_extract with multiple paths return an array containing NULL result for paths not satifisfied ["value1",null]

How i can have an expression to have always a string, with only the first json_extract returning not null?

CodePudding user response：

answer: use COALESCE

CREATE TABLE t1 (
  raw TEXT,
  c1 TEXT GENERATED ALWAYS AS (COALESCE(json_extract(raw, '$.field1'), json_extract(raw,'$.field2'))) VIRTUAL   
)