2

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
BernardT
  • 43
  • 3

2 Answers2

1

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 Fiddle.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
-1

In the postgreSQL jsonb data type is used for storing the json as its received .If you want to update any value inside it you need to transform the data from your code , before its eligible to get stored in DB .Thats a good practice .In this scenario, trailing spaces should be taken care from code itself.If you wanna update explicitly , thats also possible .

PFB link for that.

How to perform update operations on columns of type JSONB in Postgres 9.4

Ankur Srivastava
  • 855
  • 9
  • 10
  • Thanks for the pointer. Agreed. This was an oversight in the original import script which has since been patched. – BernardT Jun 06 '18 at 03:08