Postgres - Deleting attributes inside json object of json column-CodePudding

I have a Postgres JSON column (column name = data), in which I wanted to delete all the attributes inside the JSON object.

JSON

{
  "headerText": "header_text",
  "vendor": {
    "id": "some text",
    "metadata": 123123,
    "startDate": "1234234",
    "assetIds": [
      "some text"
    ],
    "endDate": "234435",
    "publishStart": 12443245,
    "publishEnd": 978128123
  },
  "footerText": "some_text"
}

So, here the attributes inside the vendor json object are dynamic, which means there may be additional attributes.

So I tried the below queries, but was unable to yield the expected result

1. update table_name set data = data::jsonb #- '{vendor.*}'
2. update table_name set data = data::jsonb - '{vendor.*}'::text[]

Expected:

{
  "headerText": "header text",
  "vendor": {},
  "footerText": "some text"
}

CodePudding user response：

Just replace vendor with an empty value by appending it.

update table_name 
  set data = data || '{"vendor": {}}'

This requires data to be defined as jsonb (which it should be). If it's not, you need to cast it: data::jsonb || ....

If you don't need the vendor key at all, you can also do:

update table_name 
  set data = data - 'vendor'

which completely removes the key from the value (so it results in {"footerText": "some_text", "headerText": "header_text"})

CodePudding user response：

You can use concatenation to overwrite values in (postgres) json:

update table_name set data = data::jsonb || json_build_object('vendor', json_build_object())::jsonb