CODEWITHSUNDEEP
sqlpostgresqljsonbpostgresql-9.6
user2490003
user2490003

Reputation: 11920

Selecting only non-NULL keys in a Postgres JSONB field

I have a postgres 9.6 table with a JSONB column

> SELECT id, data FROM my_table ORDER BY id LIMIT 4;

 id |               data    
----+---------------------------------------
  1 | {"a": [1, 7], "b": null, "c": [8]}
  2 | {"a": [2, 9], "b": [1], "c": null}
  3 | {"a": [8, 9], "b": null, "c": [3, 4]}
  4 | {}

As you can see, some JSON keys have null values.

I'd like to exclude these - is there an easy way to SELECT only the non-null key-value pairs to produce:

 id |               data    
----+---------------------------------------
  1 | {"a": [1, 7], "c": [8]}
  2 | {"a": [2, 9], "b": [1]}
  3 | {"a": [8, 9], "c": [3, 4]}
  4 | {}

Thanks!

Upvotes: 2

Views: 542

Answers (1)

user330315
user330315

Reputation:

You can use jsonb_strip_nulls()

select id, jsonb_strip_nulls(data) as data
from my_table;

Online example: http://rextester.com/GGJRW83576

Note that this function would not remove null values inside the arrays.

Upvotes: 2

Related Questions