Adding array element to JSON column in PostgreSQL [duplicate]

Question

This question already has answers here:

Pushing or appending to JSON array in PostgreSQL 9.4 (3 answers)

Closed 8 years ago.

We have a table (avl_vehiculo_data_estatica), which has a JSON column (data_vehdatesta) and, we need to update the contents of it by adding an element to actual JSON array that's stored in it:

Actual contents:

[{"iddata":"1","valor":"18"},{"iddata":"2","valor":"10"},{"iddata":"3","valor":"N/A"},{"iddata":"4","valor":"53"},{"iddata":"5","valor":"Agencia Vespucio Oeste"}]

Desired contents:

[{"iddata":"1","valor":"18"},{"iddata":"2","valor":"10"},{"iddata":"3","valor":"N/A"},{"iddata":"4","valor":"53"},{"iddata":"5","valor":"Agencia Vespucio Oeste"}, {"iddata":"6","valor":"ACTIVO"}]

So, what has to be done is to add a new JSON array element with the following values:

{"iddata":"6","valor":"ACTIVO"}

We've tried several PostgreSQL JSON related operators and functions, but haven't been able to find the correct mix to achieve such update, so any suggestions might be helpful.

DB Version:

PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

PS: There's a similar question/answer but no exactly the same as our case.

Answer 1

Just use || to concatenate jsonb, if you're using 9.5+.

SELECT lhs::jsonb || rhs::jsonb
FROM ( VALUES 
 (
 '[{"iddata":"1","valor":"18"},{"iddata":"2","valor":"10"},{"iddata":"3","valor":"N/A"},{"iddata":"4","valor":"53"},{"iddata":"5","valor":"Agencia Vespucio Oeste"}]',
 '{"iddata":"6","valor":"ACTIVO"}'
 )
) AS t(lhs,rhs);

Outputs:

[{"valor": "18", "iddata": "1"}, {"valor": "10", "iddata": "2"}, {"valor": "N/A", "iddata": "3"}, {"valor": "53", "iddata": "4"}, {"valor": "Agencia Vespucio Oeste", "iddata": "5"}, {"valor": "ACTIVO", "iddata": "6"}]

Note the explicit cast should not be required. I suggest storing your types as JSONB to start out with, unless you have good reason not to.

• Gonzalo Vasquez: close, buy not the exact solution, as the array brackets are in the way. I tried select data_vehdatesta || '{"iddata":"6","valor":"ACTIVO"}' from avl_vehiculo_data_estatica but could it be due to the fact that I'm using 9.4.9 and not 9.5+?
• Evan Carroll: @GonzaloVasquez I don't think jsonb || jsonb is in 9.4 can you check to see if you're getting the output that i'm getting above. I pasted my output.
• Gonzalo Vasquez: mmmmm: ERROR: operator does not exist: jsonb || jsonb ... not supported
• Evan Carroll: @GonzaloVasquez sounds like the other answer then is an exact duplicate. You need something like plv8 that pulls out the json in text, appends the json, and returns a new json.