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?
share|improve this question
    
please show json example?.. – Vao Tsun 5 mins ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.