Strip empty objects on generated json/jsonb on Postgresql

Question

I stip all the nulls using json_strip_nulls easily but it causes to have some empty objects on the results:

{
  "id": 1,
  "organization_id": 1,
  "pairing_id": 1,
  "location": {},
  "device": {
    "tracking_id": 1
  },
  "events": [
    {}
  ]
}

Is there any simple way to remove the empty objects too? In here location and the empty object in events are have to be removed. You can find the complete examples with test data here in DB Fiddle.

I don't think there's a function for this, but you can replace {} and [] by null and then strip nulls again — roman
– roman, Commented Apr 29, 2019 at 19:19
Perfect. It's definitely good enough to be an answer to this question! — user1079877
– user1079877, Commented May 3, 2019 at 7:45

roman · Accepted Answer · 2019-05-03 07:47:42Z

1

In your certain example I'd suggest to add a couple of helper functions:

create or replace function json_object_nullif(
    _data json
)
returns json
as $$
    select nullif(json_strip_nulls(_data)::text, '{}')::json
$$ language sql;

create or replace function json_array_nullif(
    _data json
)
returns json
as $$
    select nullif(_data::text, '[null]')::json
$$ language sql;

and then adjust your view, so instead of json_build_object(...) you can use json_object_nullif(json_build_object(...)) and the same for json_agg.

db<>fiddle demo

answered May 3, 2019 at 7:47

roman

118k30 gold badges205 silver badges209 bronze badges

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

Collectives™ on Stack Overflow

Strip empty objects on generated json/jsonb on Postgresql

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