Appending to Postgres json array (not jsonb)

Question

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

Answer 1

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.