Postgres - Deleting attributes inside json object of json column

Question

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"
}

user330315 · Accepted Answer · 2022-07-28 11:01:08Z

3

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"})

answered Jul 28, 2022 at 11:01

user330315

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

Comments

Stormtrooper CWR · Accepted Answer · 2022-07-28 11:30:24Z

2

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

edited Jul 28, 2022 at 11:30

answered Jul 28, 2022 at 11:02

Stormtrooper CWR

5331 gold badge6 silver badges19 bronze badges

5 Comments

Aishu Over a year ago

This is giving this error Error - No operator matches the given name and argument types. You might need to add explicit type casts.

Stormtrooper CWR Over a year ago

sorry my bad, should work now!

user330315 Over a year ago

If you use jsonb_build_object() there is no need to cast the result.

Stormtrooper CWR Over a year ago

@a_horse_with_no_name it is necessary because json objects can not be concatenated in postgres, thats why we need to cast into jsonString (aka jsonb), alternatively to json_build_object()::jsonb you could just use '{}'::jsonb too.

user330315 Over a year ago

I meant: json_build_object('vendor', json_build_object())::jsonb can be written as jsonb_build_object('vendor', jsonb_build_object())

Collectives™ on Stack Overflow

Postgres - Deleting attributes inside json object of json column

2 Answers 2

Comments

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Related