I have some jsonb data like:
{
"id":"58fd893414a570155ddf5120",
"TPDId":"10101",
"Services":[
"10093"
],
"DaysInstances":[
17304,
17300,
17301,
17302,
17303
],
"TPDProperties":{
"DisplayLabel":"TP display label W0ichP6h",
"TimePeriodType":"Maintenance"
}
}
The field DaysInstances is an array.
Now I want select records which DaysInstances has value between 17300 and 17303.
I tried this kind of sql but no use:
SELECT body FROM "TimePeriodInstance_100000001" where (body -> 'DaysInstances') between '17300' AND '17303';
This sql is worked but too difficult to splice in our system now :
SELECT DISTINCT body FROM "TimePeriodInstance_100000001" cross join
json_array_elements((body -> 'DaysInstances')::json) where value::text::int between '17300' AND '17303';
Any other ideas? Thanks~
Try:
(Works, if jsons id field is unique for every row)
select test1.* from test1 inner join (
select json_id from (
select col->>'id' as json_id, jsonb_array_elements_text(col->'DaysInstances') as arrel from test1
)t where arrel::numeric between 17300 and 17303
GROUP BY json_id
) t2
on test1.col->>'id' = t2.json_id
If you have unique identity column (say id), then better use this:
select test1.* from test1 inner join (
select id from (
select id, jsonb_array_elements_text(col->'DaysInstances') as arrel from test1
)t where arrel::numeric between 17300 and 17303
GROUP BY id
) t2
on test1.id = t2.id
You can do this without aggregation & LATERAL, with a plain old correlating subquery (with EXISTS):
SELECT body
FROM "TimePeriodInstance_100000001"
WHERE EXISTS(SELECT 1
FROM jsonb_array_elements(body -> 'DaysInstances') e
WHERE e BETWEEN '17300' AND '17303')
Note: the BETWEEN constraints are actually (implicitly) typed jsonb, which has comparison operators to back it up. If you want to bind ints f.ex., you will need casts to make it work (or use something like BETWEEN to_jsonb($1) AND to_jsonb($2)).