Selecting only non-NULL keys in a Postgres JSONB field

Question

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!

Please share the code you wrote that didn't produce your desired output — dfundako
– dfundako, Commented Jun 13, 2018 at 13:57

user330315 · Accepted Answer · 2018-06-13 14:02:28Z

2

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.

answered Jun 13, 2018 at 14:02

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Selecting only non-NULL keys in a Postgres JSONB field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related