check if sqlite json array elements in range

Question

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.

Post sample data of your table to clarify what you want.

forpas
– forpas

2022-05-01 07:40:36 +00:00
Commented May 1, 2022 at 7:40 — forpas
– forpas, Commented May 1, 2022 at 7:40
Hi @forpas, just did it.

Luis Alfonso Buelvas Betancour
– Luis Alfonso Buelvas Betancour

2022-05-01 07:43:49 +00:00
Commented May 1, 2022 at 7:43 — Luis Alfonso Buelvas Betancour
– Luis Alfonso Buelvas Betancour, Commented May 1, 2022 at 7:43

forpas · Accepted Answer · 2022-05-01 07:48:22Z

1

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.

answered May 1, 2022 at 7:48

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

check if sqlite json array elements in range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related