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

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

• javascript
• sqlite
• sqlite-json1

2 Answers 2

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"]           

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.