Search code examples
sqljsonsqlitegroup-byhaving

Filtering multiple items in json array with sqlite json_each

I have an sqlite table with the following schema and data:

CREATE TABLE Feeds (
    id     INTEGER PRIMARY KEY AUTOINCREMENT,
    groups JSON    NOT NULL
                   DEFAULT ('[]') 
);

INSERT INTO Feeds(groups) VALUES ('["fav1", "fav2"]');
INSERT INTO Feeds(groups) VALUES ('["fav3", "fav4"]');
INSERT INTO Feeds(groups) VALUES ('["fav1"]');
INSERT INTO Feeds(groups) VALUES ('["fav1", "fav2", "fav5"]');

I want to find all the rows that have both the fav1 group and the fav2 group. I am able to query a single group via the following:

SELECT * FROM Feeds, json_each(groups) WHERE json_each.value IS "fav1"

But i am struggling to figure out how to query more than one group, the following doesnt seem to work:

SELECT * FROM Feeds, json_each(groups) WHERE json_each.value IS "fav1" AND json_each.value IS "fav2"
Solution

  • You can do it with aggregation:

    SELECT f.* 
FROM Feeds f, json_each(groups) t 
WHERE t.value IN ('fav1', 'fav2')
GROUP BY f.id
HAVING COUNT(DISTINCT t.value) = 2;

    You can change COUNT(DISTINCT t.value) to just COUNT(*) if there are no duplicates inside the json array.

    See the demo.