This question already has an answer here:

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.

marked as duplicate by Evan Carroll, Community Apr 4 '17 at 18:15

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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.

  • 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+? – Gonzalo Vasquez Apr 4 '17 at 17:42
  • @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. – Evan Carroll Apr 4 '17 at 17:44
  • mmmmm: ERROR: operator does not exist: jsonb || jsonb ... not supported – Gonzalo Vasquez Apr 4 '17 at 17:45
  • @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. – Evan Carroll Apr 4 '17 at 17:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.