How can I update json empty value in PostgreSQL

Question

When I try to update like below, I received invalid input syntax for type json. Expected "," or "}".

The record's content of problem is {'open': ','}.
If the record's content is {'open': ',abc'}, I can update correctly like {'open': 'abc'}.

update hoge SET extra =
REPLACE(
       extra::TEXT,
       extra->>'open',
       regexp_replace(extra->>'open', '^,', '')
)::JSONB

Any Idea?

Kaushik Nayak · Accepted Answer · 2019-05-01 04:25:15Z

1

use JSONB_SET for update and filter the records that are to be updated in the where clause

update hoge set extra = jsonb_set(extra, '{open}' ,'"abc"')
  where extra->>'open' = ',abc';

answered May 1, 2019 at 4:25

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Answer 1