Postgresql – how to replace with default value when querying a JSONB column with a key that is not present in a particular row giving empty result

postgresqlquery-performancesubquery

I have a table that has a jsonb column as 'result' which has an array of objects.

Looks like this for every row:

[{"grade": "A", "subject": "MATH"}, {"grade": "B", "subject": "PHY"}...]

When I am querying this way

select up.id, jsonb_array_elements(to_jsonb(up.result)) ->>'subject' as result 
from "UserProfile"

I get correct result but problem arises when I query for a particular row with an id wherein this result column has an empty array data. Then it neither gives any result nor an error even though I am selecting 'id' in query. not able to put any default value also using coalesce

How can I resolve this issue?

Best Answer

Use a left join:

select up.id, r.element ->>'subject' as result 
from "UserProfile" up
  left join jsonb_array_elements(up.result) as r(element) on true

Related Solutions

Postgresql – CRUD operations on json data

Matheus de Oliveira created handy functions for JSON CRUD operations in postgresql. They can be imported using the \i directive. Notice the jsonb fork of the functions if jsonb is your data type.

9.3 json https://gist.github.com/matheusoliveira/9488951

(and while jsonb is not an option for Michael, for those who are using 9.4, there is a jsonb variant: https://gist.github.com/inindev/2219dff96851928c2282)

Operations Provided:

postgres=# SELECT json_append('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
        json_append        
---------------------------
 {"a":1,"b":2,"c":3,"a":4}
(1 row)

postgres=# SELECT json_delete('{"b": 2, "c": 3, "a": 4}', '{b, c}');
 json_delete 
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_update('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
 json_update 
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_merge('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
     json_merge      
---------------------
 {"b":2,"c":3,"a":4}
(1 row)

Postgresql – Postgres: querying jsonb array with an array

You can use the @> operator.

From the docs:

@> Does the left JSON value contain within it the right value?

So in your case it would look something like this:

select * from products where specs @> '{"spec_options": ["a", "b", "c"]}';

Best Answer

Related Solutions

Postgresql – CRUD operations on json data

Postgresql – Postgres: querying jsonb array with an array

Related Question