Append a value to json array

Question

I have a postgresql table profile with columns (username, friends). username is a string and friends is a json type and has the current values ["Mary", "Bob]. I want to append an item at the end of the array so it is ["Mary", "Bob", "Alice"]

I am currently have tried:

UPDATE profile SET friends = friends || '["Alice"]'::jsonb WHERE username = 'David';

This yields the error:

[ERROR] 23:56:23 error: operator does not exist: json || jsonb

I tried changing the first expression to include json instead of jsonb but I then got the error:

[ERROR] 00:06:25 error: operator does not exist: json || json

Other answers seem to suggesst the || operator is indeed a thing, e.g.:
Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+

How do I append an item to the end of a json array?

Answer 1

The data type json is much more limited than jsonb. Use jsonb instead of json and the solution you found simply works.

While stuck with json, a possible workaround is to cast to jsonb and back:

UPDATE profile
SET    friends = (friends::jsonb || '["Alice"]')::json
WHERE  username = 'David';

You might use an explicit type for jsonb '["Alice"]', too, but that's optional. While the expression is unambiguous like that, the untyped string literal will be coerced to jsonb automatically. If you instead provide a typed value in place of '["Alice"]', the explicit cast may be required.

If friends can be NULL, you'll want to define what should happen then. Currently, nothing happens. Or, to be precise, an update that doesn't change the NULL value.

Then again, if you only have simple arrays of strings, consider the plain Postgres array type text[] instead.