I have a Postgres database with one of the column as int array(eg [1,2,3]) let say user_ids. Now I want to filter the select query based on a get variable let say user_id.
How can I use this in Query builder? select * from user where user_id in user_ids
PS: Since this is a paged query I don’t want to write custom query.
Considered storing the IDs in a subtable?, i find that approach much more flexible
1 Like
1 Like
The mebedded JSONB fields in PG are very powerful and useful but in cases like this, where you want to filter/JOIN/etc… I would recommend sub-tables - otherwise you will need a custom query and know the PG SQL pretty well.
E.g. here’s a few examples:
and (
(_selected_chairs is null and u.user_id is not null)
or (_selected_chairs is not null
and uwp.practise_room_chair_id in (
select sb::uuid from jsonb_array_elements_text(_selected_chairs) sb
)
)
)
And …
coalesce(u.title || ' ', '') || u.first_name || coalesce(' ' || u.last_name, '') as "title", case when u.user_id is not null then jsonb_build_array(u.user_id) else '[]'::jsonb end as "user_ids"
1 Like