In postgres updating a jsonb column, how do you create OR append an array in one query?

Question

I have a table with some basic jsonb, for this example lets call it transactions

CREATE TABLE transactions (
    transaction_id text NOT NULL,
    body jsonb,
);

So inside the body I have some data we can ignore but it's an object something like {"price": 10} etc

I want to add a refund key, and default it to an empty array, or push into the array in one query.

The following accomplishes it in two queries:

UPDATE transactions 
SET body = jsonb_set(body, '{refunds}', '[]'::jsonb, true) 
WHERE transaction_id = 'abc'

UPDATE transactions 
SET body = jsonb_insert(body, '{refunds,0}', '{"id":"any"}'::jsonb, true) 
WHERE transaction_id = 'abc'

But I can't seem to instantiate the array if it doesn't exist in a single transaction/query. Tried the postgres jsonb docs as well of course.

Thanks in advance!

inquirer · Answer 1 · 2023-02-23T19:41:56.847

0

But what if, when creating a table, make the default key 'refunds' with an empty list.?

CREATE TABLE transactions (
    transaction_id text NOT NULL,
    body jsonb NOT NULL DEFAULT '{"refunds":[]}'::jsonb
);

Make a record "transaction_id".

INSERT INTO "transactions" ("transaction_id") VALUES ('abc');

Output

 transaction_id |      body
----------------+-----------------
 abc            | {"refunds": []}

Update

UPDATE transactions 
SET body = jsonb_set(body, '{refunds}', '[{"id":"any"}]'::jsonb, true)
WHERE transaction_id = 'abc';

edited Feb 23 '23 at 19:41

answered Feb 23 '23 at 09:33

inquirer

TY for the suggestion, but the body json is actually stored from a transaction response from an API. I have a storage of these that already exists as well, so starting the table from scratch is not an option. There must be some jsonb way to do this! – mdubbpro Feb 23 '23 at 13:33
@mdubbpro If I understand correctly, do you need to create refunds with an empty list in one update, or set refunds with values inside the list? If so, then updated the code, where is the example. – inquirer Feb 23 '23 at 19:41

1 Answers1