How do I update a JSON field in PostgreSQL?

Question

I have a table with a column called data that contains some JSON. If the data column for any given row in the table is not null, it will contain a JSON-encoded object with a key called companyDescription. The value associated with companyDescription is an arbitrary JavaScript object.

If I query my table like this

select data->>'companyDescription' from companies where data is not null;

I get rows like this

{"ops":[{"insert":"\n"}]}

I am trying to update all rows in the table so that the companyDescription values will be wrapped in another JSON-encoded JavaScript object in the following manner:

{"type":"quill","content":{"ops":[{"insert":"\n"}]}}

Here's what I have tried, but I think it won't work because the ->> operator is for selecting some JSON field as text, and indeed it fails with a syntax error.

update companies
set data->>'companyDescription' = CONCAT(
  '{"type":"quill","content":',
  (select data->>'companyDescription' from companies),
  '}'
);

What is the correct way to do this?

Pavel Stehule · Accepted Answer · 2020-05-15 10:43:32Z

You can use a function jsonb_set. Currently XML or JSON values are immutable. You cannot to update some parts of these values. You can replace these values by some new modified value.

postgres=# select * from test;
┌──────────────────────────────────────────────────────────────────────┐
│                                  v                                   │
╞══════════════════════════════════════════════════════════════════════╡
│ {"companyId": 10, "companyDescription": {"ops": [{"insert": "\n"}]}} │
└──────────────────────────────────────────────────────────────────────┘
(1 row)

postgres=# select jsonb_build_object('type', 'quill', 'content', v->'companyDescription') from test;
┌───────────────────────────────────────────────────────────┐
│                    jsonb_build_object                     │
╞═══════════════════════════════════════════════════════════╡
│ {"type": "quill", "content": {"ops": [{"insert": "\n"}]}} │
└───────────────────────────────────────────────────────────┘
(1 row)

postgres=# select jsonb_set(v, ARRAY['companyDescription'], jsonb_build_object('type', 'quill', 'content', v->'companyDescription')) from test;
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                             jsonb_set                                              │
╞════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ {"companyId": 10, "companyDescription": {"type": "quill", "content": {"ops": [{"insert": "\n"}]}}} │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)

So you final statement can looks like:

update companies
  set data = jsonb_set(data::jsonb, 
                       ARRAY['companyDescription'], 
                       jsonb_build_object('type', 'quill', 
                                          'content', data->'companyDescription'))
  where data is not null;

Thank you; that explains why all my previous search results showed examples in jsonb instead of json. How do I get the v in your example? I'm trying to use my original select, but I am not sure how map each row to itself. Do I need to use some kind of loop? Here's what I am trying: ix.io/2m5u
@JezenThomas - v is column name, it is data. I'll fix this query
Excellent, thanks. This does indeed work; just needs the explicit data::JSONB typecast.

Collectives™ on Stack Overflow

How do I update a JSON field in PostgreSQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related