How do I remove NULL values when extracting JSON to generated columns?

Question

I save a TEXT column with raw JSON. The JSON schema is not unique. I want to use generated columns to extract values from the raw column. I can do that with multiple columns, each using a different path to extract a 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 but json_extract with multiple paths returns an array containing NULL values for paths not satisfying ["value1",null] . How can I always get a string with only the first json_extract returning not NULL?

Answer 1

answer: use COALESCE

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