更新对象的jsonb数组中的键值 [英] Update key value in jsonb array of objects

问题描述

我有下表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?

推荐答案

您可以混合使用

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数组中的键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！