Appending to Postgres json array (not jsonb)-CodePudding

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).

CodePudding user response：

As you insist on using json you have to cast your column to jsonbevery time you want to do something useful:

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

Postgres will automatically cast the result of the expression on the right side of the = back to JSON in the assignment.