检查sqlite json数组元素是否在范围内

Question

我有一个表，其中包含一个json列，有一个数组字段，我想检查数组内的数字是否属于一个范围，也就是说，如果每个元素传递谓词大于X小于Y，

select nom.id, nom.doc
from nom
join json_each(nom.doc, "$.rate") e
where e.value > 0 and e.value < 7

我希望这但它不工作,我知道json_each返回一个表,我想也许使用分组或聚合我可以得到我想要的东西,但我想知道如果有另一种方法,我不愿依靠聚合,我想举个例子,检查是否所有的元素通过一个谓词,例如,如果所有大于X,或者如果我使用字符串,如果他们都匹配一个模式等。

样本数据:

id: 1, doc: {rate: [1, 2, 3]}
id: 2, doc: {rate: [1, 2, 3, 5 9]}

如果我有这两条记录，我希望得到那些速率值小于7且大于0的记录，例如，id为2的记录不应该出现在我想要的查询中。

Answer 1

一种方法是提取CTE内数组的所有值，并在主查询中使用NOT EXISTS来检查某个id的所有值是否通过谓词:

WITH cte AS (
SELECT n.id, n.doc, e.value
FROM nom n JOIN json_each(n.doc, "$.rate") e
)
SELECT n.id, n.doc
FROM nom n
WHERE NOT EXISTS (
SELECT 1
FROM cte c
WHERE c.id = n.id AND NOT <your predicate>
);

将<your predicate>更改为:

(c.value > 0 AND c.value < 7)