转换jsonb中的日期(Postgres)

Question

我有jsonb列数据=

"{"history": [{"endDate": "30.06.2015", "classname": "Class A", "startDate": "2010-04-01", "numberAction": "0016", "positionName": "Teacher"},
{"endDate": "31.06.2010", "classname": "Class A", "startDate": "2005-08-10", "numberAction": "0015", "positionName": "Student"},  
{"endDate": "2005.08.09", "classname": "Class B", "startDate": "2005-02-21", "numberAction": "0014", "positionName": " Student "}]}"

如您所见，数组中"endDate"的日期不正确。请告诉我如何将它们转换为YYYY-MM-DD格式？

我的空闲尝试：

UPDATE table
SET data = jsonb_set(data, '{endDate}', to_date('{endDate}', 'YYYY-MM-DD'), false)
WHERE id = 'UUID';

Answer 1

答案=

update table
set data = data - 'history' || 
jsonb_build_object
( 'history'
,   ( select jsonb_agg 
( case when aa.value ->> 'endDate' like '%.%' then 
aa.value - 'endDate' || jsonb_build_object
( 'endDate'
,  to_date(aa.value ->> 'endDate','dd.mm.yyyy')
) 
else 
aa.value
end 
)
from jsonb_array_elements(data -> 'history') as aa
)
)
WHERE uuid = 'UUID'
and exists ( select *
from jsonb_array_elements(data -> 'history') as aa
where aa.value ->> 'endDate' like '%.%' 
);