Postgresql: How to select records by column and jsonb array column

Question

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?

Answer 1

You can use the @> operator to check if the array contains a specific element. There is no need to use jsonb_array_elements

SELECT dv.id, dv.device_name, dv.device_subname
FROM devices AS dv
WHERE dv.user_id = 1235
  and (dv.device_name = 'rear light'
       or dv.device_subname @> '[{"name": "rear light"}]')

---

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

SELECT dv.id, dv.device_name, dv.device_subname
FROM devices AS dv
  LEFT JOIN jsonb_array_elements(dv.device_subname) as sub(item) on true
WHERE dv.user_id = 1235
  and (dv.device_name = 'rear light' or sub.item ->> 'name' = 'rear light')

The @> is more efficient though.