I have table that look like this:
id device_name user_id device_subname
(varchar) (varchar) (int) (jsonb)
--- ------------- --------- -----------------
X2 wall lamp 1235 [{"name": "rear light", "commands":"light_v1"}]
Z6 rear light 1235 []
H4 rear light 1235 []
1C speakers 1476 []
V4 wall socket 1523 [{"name": "socket", "commands":"socket_v2"}]
How to get records with user_id = 1235 AND (device_name = 'rear light' OR device_subname->>name = 'rear light')
?
I've come to this solution:
SELECT DISTINCT
device.id, device.device_name, device.device_subname
FROM
devices AS device,
jsonb_array_elements(device.device_subname) AS "deviceSubname"
WHERE
device.user_id = '1235'
AND (
device.device_name = 'rear light'
OR
"deviceSubname"->>'name' = 'rear light'
)
Above query is resulting:
id device_name user_id device_subname
--- ------------- --------- -----------------
X2 wall lamp 1235 [{"name": "rear light", "commands":"light_v1"}]
What i want is to get this:
id device_name user_id device_subname
--- ------------- --------- -----------------
X2 wall lamp 1235 [{"name": "rear light", "commands":"light_v1"}]
Z6 rear light 1235 []
H4 rear light 1235 []
It's look like jsonb_array_elemenst()
is skip records if the device_subname
column is an empty array. How to do this query?
Best Answer
You can use the
@>
operator to check if the array contains a specific element. There is no need to usejsonb_array_elements
Your cross join is essentially an "inner join" so for rows that have no array elements, the row from the base table is not included.
You would have to use an outer join against the array elements if you really wanted to filter in the WHERE clause
The
@>
is more efficient though.