CODEWITHSUNDEEP
sqlpostgresqljsonb
user1079877
user1079877

Reputation: 9398

Strip empty objects on generated json/jsonb on Postgresql

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.

Upvotes: 2

Views: 2283

Answers (1)

roman
roman

Reputation: 117636

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

Upvotes: 1

Related Questions