Tengo algunos datos jsonb como:
{ "id":"58fd893414a570155ddf5120", "TPDId":"10101", "Services":[ "10093" ], "DaysInstances":[ 17304, 17300, 17301, 17302, 17303 ], "TPDProperties":{ "DisplayLabel":"TP display label W0ichP6h", "TimePeriodType":"Maintenance" } }El campo DaysInstances es una matriz.
Ahora quiero seleccionar registros que DaysInstances tenga un valor entre 17300 y 17303.
Probé este tipo de sql pero no sirvió de nada:
SELECT body FROM "TimePeriodInstance_100000001" where (body -> 'DaysInstances') between '17300' AND '17303';Este sql funciona pero es demasiado difícil de empalmar en nuestro sistema ahora:
SELECT DISTINCT body FROM "TimePeriodInstance_100000001" cross join json_array_elements((body -> 'DaysInstances')::json) where value::text::int between '17300' AND '17303';¿Alguna otra idea? Gracias~
Tratar:
(Funciona, si el campo de id jsons es único para cada fila)
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 Si tiene una columna de identidad única (digamos id ), mejor use esto:
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.idPuede hacer esto sin agregación y LATERAL , con una subconsulta de correlación simple y antigua (con EXISTS ):
SELECT body FROM "TimePeriodInstance_100000001" WHERE EXISTS(SELECT 1 FROM jsonb_array_elements(body -> 'DaysInstances') e WHERE e BETWEEN '17300' AND '17303') Nota : las restricciones BETWEEN son en realidad (implícitamente) tipeadas jsonb , que tiene operadores de comparación para respaldarlas. Si desea vincular int s f.ex., necesitará conversiones para que funcione (o use algo como BETWEEN to_jsonb($1) AND to_jsonb($2) ).