PostgreSQL更新jsonb数组列中的值 [英] Postgresql update a value in a jsonb array column
本文介绍了PostgreSQL更新jsonb数组列中的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个带有jsonb的表,并且此数据位于其中:
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
}
]
但是现在我需要在所有行中将"vencimento"中的值从1573182000000更改为"10/10/2010"
but now I need in all row, alter the value in "vencimento" from 1573182000000 to "10/10/2010"
有可能吗?
使用此代码,我可以将数组拆分为列并更改te值
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;
但是如何更新我的行?
tks
推荐答案
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;
这篇关于PostgreSQL更新jsonb数组列中的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文