Postgresql update a value in a jsonb array column

Question

I have one table with a jsonb and this data inside:

[
{
    "valor": "2025,79",
    "parcela": 46,
    "vencimento": 1570503600000
},
{
    "valor": "1987,7",
    "parcela": 47,
    "vencimento": 1573182000000
},
{
    "valor": "1950,47",
    "parcela": 48,
    "vencimento": 1575774000000
},
{
    "valor": "1912,88",
    "parcela": 49,
    "vencimento": 1578452400000
}

]

but now I need in all row, alter the value in "vencimento" from 1573182000000 to "10/10/2010"

it's possible ?

with this code I can split the array in columns and change te value

SELECT id, jsonb_set(d, '{vencimento}',
 quote_ident(to_char(to_timestamp(cast((d -> 'vencimento')::varchar as bigint) / 1000)::date, 'dd/mm/yyyy'))::jsonb
) 
    FROM notificato.requerimento, jsonb_array_elements(parcela) d where id = 1;

but how can I update my row?

tks

Bergi · Accepted Answer · 2020-02-20 17:26:42Z

1

Collect the values back to a jsonb array with jsonb_agg and assign that to your column:

UPDATE notificato.requerimento
SET parcela = (SELECT jsonb_agg(
  jsonb_set(d, '{vencimento}', to_jsonb(to_char(to_timestamp((d ->> 'vencimento')::bigint / 1000)::date, 'dd/mm/yyyy'))
) FROM jsonb_array_elements(parcela) d)
WHERE id = 1;

answered Feb 20, 2020 at 17:26

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql update a value in a jsonb array column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related