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 :)

Answer 1

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.