6

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

oz123
  • 27,559
  • 27
  • 125
  • 187

2 Answers2

11

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"]
Shawn
  • 47,241
  • 3
  • 26
  • 60
0

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.
Frenk999
  • 47
  • 3
  • Ok thanks, but it's look like a regex workaround not a real json filter function –  Aug 29 '20 at 22:14
  • It's either that or building sql.js with json1 extension enabled from the repository. For simple arrays in your example use this workarounnd. – Frenk999 Aug 29 '20 at 23:00