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

发布于 2025-01-25 06:42:27 字数 439 浏览 1 评论 0原文

我有一个包含一个带有数组字段的JSON列的表，我想检查数组中的数字是否属于一个范围，即，如果每个元素传递的谓词大于x，并且比y，则比y，

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

我' d期望这能起作用，但事实并非想像

依靠聚合，

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

因为我我有这两个记录，我想让那些速率值少于7和大于0的记录，例如，ID 2的记录不应出现在我想要的查询中。

原文

I have a table which contains a json column that has an array field, I want to check if the numbers inside the array belong to a range, that is if each element passes the predicate to be greater than X and lesser than Y,

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

I'd expect this to work but it doesn't, I understand that json_each returns a table, and I think maybe using grouping or aggregation I could get what I want, but I want to know if there is another way, I would like to not rely on aggregation, as I would want to for example, check if all elements pass a predicate, for example, if all are greater than X, or if I where working with strings, if they all match a pattern etc.

sample data:

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

If I have those two records, I'd like to get those whose rate has values lesser than 7 and greater than 0, for example, record with id 2 should not appear in the query I desire.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

長街聽風 2025-02-01 06:42:27

一种方法是提取CTE内部数组的所有值，并在主查询中使用不存在以检查是否是否某个特定id> 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>
);

更改＆lt;您的谓词＆gt; to以下内容：

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

请参阅演示。

One way to do it is to extract all the values of the array inside a CTE and use NOT EXISTS in the main query to check if all values of a certain id pass the predicate:

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>
);

Change <your predicate> to something like: