I want to fetch record from table using postgres but it consist inside jsonarray-CodePudding

I want to fetch device name on basis of department id which is inside array

select device_name 
where departmentIDs = 1

Sample data:

device_to_department_id | device_name   |  department_ids
-----------------------------------------------------------------
                      1 | xyz           | {"departmentIDs":[1,2]}

CodePudding user response：

select distinct device_name from device_to_department_table t, json_array_elements(department_ids -> 'departmentIDs') e where e::text::int in (1);

CodePudding user response：

You can use the contains operator:

select *
from device_to_department_table 
where department_ids  @> '{"departmentIDs":[1]}'

This would return all rows that contain at least the department ID 1 (so your sample row with [1,2] would be included)