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"
CodePudding user response:
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.