How can i remove from a jsonb all elements with value is null?

user_12285956 0
Viewed 762

I have a table "points" and the columns "node_id", "tags", etc.

How can i remove from the jsonb element "tags" all elements with the null value ?

{
  "addInfo": {
    "payment": {
      "payment:dkv": null,
      "payment:uta": null,
    },
    "fueltype": {
      "fuel:diesel": "yes",
      "fuel:octane_91": null,
      "fuel:octane_95": "yes",
      "fuel:octane_98": null
    },
    "operating": {
      "name": "Raiffeisen",
      "brand": "Raiffeisen",
      "operator": null,
      "opening_hours": "24/7"
    }
  }
}

i want to get this form:

{
  "addInfo": {
    "payment": {},
    "fueltype": {
      "fuel:diesel": "yes",
      "fuel:octane_95": "yes"
    },
    "operating": {
      "name": "Raiffeisen",
      "brand": "Raiffeisen",
      "opening_hours": "24/7"
    }
  }
}

I try until with this example code, it works but is not smart. I use twice jsonb_strip_nulls and replace and convert between text and jsonb. Is any other way to get the same smarter ?

SELECT node_id,
       nullif(jsonb_strip_nulls(replace("addInfo" ::text, '{}', 'null')
                                ::jsonb) ::text,
              '{}') ::jsonb
  FROM (SELECT jsonb_strip_nulls(
                 jsonb_build_object('addInfo',
                                     jsonb_build_object('EXAMPLE....'))) "addInfo"
          FROM points p
         WHERE p.tags notnull
           AND p.tags - >> 'amenity' = 'fuel') foo;

And how can i have the original sorting:

1 operating
2 payment
3 fueltype
jsonb postgresql
1 Answers

json_strip_nulls and jsonb_strip_nulls this functions deletes all object fields that have null values from the given JSON value. Null values that are not object fields are untouched. The best side of these functions is that they are recursive, so the function will be deleted null values in sub JSON objects too.

ATTENTION!!! - your JSON string code has invalid, I removed one , character in your JSON and commented on this.

select jsonb_strip_nulls(
'{
    "addInfo": {
        "payment": {
            "payment:dkv": null,
            "payment:uta": null   /* in here I removed character: "," */
        },
        "fueltype": {
            "fuel:diesel": "yes",
            "fuel:octane_91": null,
            "fuel:octane_95": "yes",
            "fuel:octane_98": null
        },
        "operating": {
            "name": "Raiffeisen",
            "brand": "Raiffeisen",
            "operator": null,
            "opening_hours": "24/7"
        }
    }
}')

Works fine!!! Result:

{
  "addInfo": {
    "payment": {},
    "fueltype": {
      "fuel:diesel": "yes",
      "fuel:octane_95": "yes"
    },
    "operating": {
      "name": "Raiffeisen",
      "brand": "Raiffeisen",
      "opening_hours": "24/7"
    }
  }
}
user_172960840
Related