postgreSQL query empty array fields within jsonb column

Question

device_id | device
-----------------------------
9809      | { "name" : "printer", "tags" : [] }    
9810      | { "name" : "phone", "tags" : [{"count": 2, "price" : 77}, {"count": 3, "price" : 37} ] }

For the following postgres SQL query on a jsonb column "device" that contains array 'tags':

SELECT t.device_id, elem->>'count', elem->>'price'
FROM   tbl t, json_array_elements(t.device->'tags') elem
where t.device_id = 9809

device_id is the primary key.

I have two issues that I don't know how to solve:

1. tags is an array field that may be empty, in which case I got 0 rows. I want output no matter tags is empty or not. Dummy values are ok.
2. If tags contain multiple elements, I got multiple rows for the same device id. How to aggregate those multiple elements into one row?

Answer 1

Your first problem can be solved by using a left outer join, that will substitute NULL values for missing matches on the right side.

The second problem can be solved with an aggregate function like json_agg, array_agg or string_agg, depending on the desired result type:

SELECT t.device_id,
       jsonb_agg(elem->>'count'),
       jsonb_agg(elem->>'price')
FROM tbl t
   LEFT JOIN LATERAL jsonb_array_elements(t.device->'tags') elem
      ON TRUE
GROUP BY t.device_id;

You will get a JSON array containing just null for those rows where the array is empty, I hope that is ok for you.