Adding array element to JSON column in PostgreSQL [duplicate]

Question

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.