Update certain array elements of a json array in PostgreSQL 9.4
arrays, jsonb, postgresql, postgresql-9.4, sql-update
Solution
For Postgres 9.5 or later use `jsonb_set()`. See later answer of adriaan.
You cannot manipulate selected elements of a `json` / `jsonb` type directly. Functionality for that is still missing in Postgres 9.4. You have to do 3 steps:
- Unnest / decompose the JSON value.
- Manipulate selected elements.
- Aggregate / compose the value back again.
To replace the 3rd element of the json array (`data->3`) in the row with `id = 1` with a given (new) value (`'<new_value>'`):
UPDATE test t
SET data = t2.data
FROM (
SELECT id, array_to_json(
array_agg(CASE WHEN rn = 1 THEN '<new_value>' ELSE elem END))
) AS data
FROM test t2
, json_array_elements_text(t2.data) WITH ORDINALITY x(elem, rn)
WHERE id = 1
GROUP BY 1
) t2
WHERE t.id = t2.id
AND t.data <> t2.data; -- avoid empty updates
About `json_array_elements_text()`:
- How to turn JSON array into Postgres array?
About `WITH ORDINALITY`:
- PostgreSQL unnest() with element number
Problem
I have a table like this; ``` CREATE TABLE test ( id BIGSERIAL PRIMARY KEY, data JSONB ); INSERT INTO test(data) VALUES('[1,2,"a",4,"8",6]'); -- id = 1 INSERT INTO test(data) VALUES('[1,2,"b",4,"7",6]'); -- id = 2 ``` How to update element `data->1` and `data->3` into something else without `PL/*`?