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 "}]
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.
Old sqlfiddle
c.log <> sub.log2, fixed.In PostgreSQL jsonb data type is used for storing JSON data as given. If you want to update any nested value you need to transform the data from your code before it's eligible to be stored in DB. In this scenario, trailing spaces are insignificant. If you wanna update explicitly, that's also possible.
PFB link for that.
