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?

user4157124 · Accepted Answer · 2024-09-12 23:28:47Z

1

answer: use COALESCE

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

edited Sep 12, 2024 at 23:28

user4157124

3,00021 gold badges33 silver badges48 bronze badges

answered Jan 11, 2022 at 15:45

George Livanoss

6092 gold badges8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

surfmuggle · Accepted Answer · 2024-09-15 10:45:12Z

Be aware coalesce will only return 1 value which might not be what OP wants:

coalesce(X,Y,...)

The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL. Coalesce() must have at least 2 arguments.

See for yourself

SELECT 1, 'only foo', COALESCE('foo','bar', NULL)
UNION ALL
SELECT 2, 'only bar', COALESCE(NULL,'bar', 'zap')
UNION ALL
SELECT 3, 'only zap', COALESCE(NULL,NULL, 'zap')

Will return

ID	Description	Result
1	only foo	foo
2	only bar	bar
3	only zap	zap

But this might be helpfull

WITH CTE AS (
  SELECT 'Aaaa' AS A, 
         NULL AS B, 
         ', Ceee ' AS C, 
         'Dee' AS D
)
SELECT *, 
       COALESCE(A, '') || ', ' || 
       COALESCE(B, '') || ', ' || 
       COALESCE(C, '') || ', ' || 
       COALESCE(D, '') AS concatenated_columns
FROM CTE;

This results in

A	B	C	D	concatenated_columns
Aaaa		, Ceee	Dee	Aaaa, , , Ceee , Dee

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related