Is there a method to check if an array includes one value in SQLite?

Question

Let's say, we have this SQLite table with id=number and tags=text:

| id   | tags                |  
| ---- | ------------------- |   
| 1    | ["love","sadness"]  |    
| 2    | ["love"]            |   
| 3    | ["happiness","joy"] |

Is there a way to only return the rows which their cells tags include "love" for example, like this command in MySQL : SELECT * from my_table WHERE JSON_CONTAINS(tags, '"love"') in SQLite.

I use this with the library sql.js wtih node.js

Bladderwort

Answer 1

If your version of sqlite has the JSON1 extension compiled in:

SELECT *
FROM my_table
WHERE EXISTS (SELECT 1 FROM json_each(tags) WHERE value = 'love')
ORDER BY id;

will return

id  tags               
--  -------------------
1   ["love","sadness"] 
2   ["love"]           

Dallapiccola

Answer 2

I believe this should work:

SELECT * from my_table WHERE tags LIKE '%"love"%';

Keyword LIKE lets you query partial information in the column and it's used in WHERE clause just like operators =, IN, BETWEEN

Note:

• The percent sign % wildcard matches any sequence of zero or more characters.
• The underscore _ wildcard matches any single character.

Perrone

Ok thanks, but it's look like a regex workaround not a real json filter function – Bladderwort

It's either that or building sql.js with json1 extension enabled from the repository. For simple arrays in your example use this workarounnd. – Perrone