2022-12-16

Appending to Postgres json array (not jsonb)

I'm trying out the json (and jsonb) support in postgres. Currently the thing that has me stuck is how to append to a json array - I can do it with jsonb, but cannot work out if this is possible out of the box.

in jsonb:

CREATE TABLE OrderData (
    uuid text NOT NULL PRIMARY KEY UNIQUE,
    info jsonb NOT NULL
);

INSERT INTO orderdata
VALUES('abcd','[{"items": {"product": "Jelly","qty": 1});

UPDATE orderdata SET info = info || '{"items": {"product": "Grape","qty": 10}}'::jsonb WHERE uuid LIKE 'abcd';

The above works fine - and I can do a SELECT * FROM orderdata; to see the updates.

When I'm doing the same thing with json - i can get it started off:

CREATE TABLE OrderData (
    uuid text NOT NULL PRIMARY KEY UNIQUE,
    info json NOT NULL
);

INSERT INTO orderdata
VALUES('abcd','{"items": {"product": "Jelly","qty": 1}}'));

but then I can't use the || operator as it doesn't work on json (only jsonb).



No comments:

Post a Comment