How to check if array contains an item in JSON column using Sqlite?

Question

I'm using sqlite to store JSON data that I have no control over. I have a logs table that looks like this.

id	value
s8i13s85e8f34zm8vikkcv5n	{"key":["a","b"]}
m2abxfn2n9pkyc9kjmko5462	{"key": "sometext"}

Then I use the following query to get the rows where value.key contains a:

SELECT * FROM logs WHERE EXISTS (SELECT * FROM json_each(json_extract(logs.value,'$.key')) WHERE json_each.value = 'a')

The query works fine if key is an array or if it doesn't exist. But it fails if is a string (like the second row of the table)

The error I get is:

SQL error or missing database (malformed JSON)

And it is because json_each throws if the parameter is an string.

Because of the requirements I can't control the user data or the queries.

Ideally I would like to figure out a query that either doesn't fail or that detects that the value is a string instead of an array and uses LIKE to see if the string contains 'a'.

Any help would be appreciated. Happy holidays :)

forpas · Accepted Answer · 2022-12-08 15:07:28Z

3

Use a CASE expression in the WHERE clause which checks if the value is an array or not:

SELECT * 
FROM logs 
WHERE CASE
  WHEN value LIKE '{"key":[%]}' THEN
    EXISTS (
     SELECT * 
     FROM json_each(json_extract(logs.value,'$.key')) 
     WHERE json_each.value = 'a'
    )
  ELSE json_extract(value,'$.key') = 'a'
END;

See the demo.

answered Dec 8, 2022 at 15:07

forpas

165k10 gold badges51 silver badges85 bronze badges

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

1 Comment

jpcapdevila Over a year ago

Works great. Thanks a lot! I did a couple of changes to better support my use case: 1 - Use json_extract on the CASE to check specifically for the key, otherwise it would go to the ELSE expression if the value column had any other keys. 2 - Use the LIKE operator instead of = on the ELSE expression, that way I can check for strings or arrays that contain x with a single query.

Collectives™ on Stack Overflow

How to check if array contains an item in JSON column using Sqlite?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related