使用 sqlite json_each 过滤 json 数组中的多个项目

Question

我有一个包含以下架构和数据的 sqlite 表：

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"]');

我想查找同时具有 fav1 组和 fav2 组的所有行。我可以通过以下方式查询单个组：

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

但我正在努力弄清楚如何查询多个组，以下似乎不起作用：

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

Answer 1

您可以通过聚合来实现：

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;

如果 json 数组内没有重复项，您可以将 COUNT(DISTINCT t.value) 更改为 COUNT(*)。

请参阅演示。