check if sqlite json array elements in range-CodePudding

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.

CodePudding user response：

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:

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

See the demo.