本文介绍了更新对象的jsonb数组中的键值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有下表company和名为log的jsonb列:-
I have the following table company with a jsonb column named log: -
|code | log ----------------------------------------------------------------------------- |K50 | [{"date": "2002-02-06", "type": "Chg Name", "oldvalue": "TEH "}, {"date": "2003-08-26", "type": "Chg Name", "oldvalue": "TEOA "}] |C44 | [{"date": "2003-05-07", "type": "Chg Name", "oldvalue": "CDE "}]如何修剪oldvalue中的尾随空白?
How to trim the trailing blanks in the oldvalue?
推荐答案您可以混合使用 jsonb函数和运算符:
You can do it with a mix of jsonb functions and operators:
UPDATE company c SET log = sub.log2 FROM ( SELECT * FROM company c CROSS JOIN LATERAL ( SELECT jsonb_agg(jsonb_set(l, '{oldvalue}', to_jsonb(rtrim(l->>'oldvalue')))) AS log2 FROM jsonb_array_elements(c.log) l ) sub WHERE jsonb_typeof(log) = 'array' -- exclude NULL and non-arrays ) sub WHERE c.code = sub.code -- assuming code is unique AND c.log <> sub.log2; -- only where column actually changed.SQL提琴.
更多推荐
更新对象的jsonb数组中的键值
发布评论