I have one of the column audit_info with JSON data in the table:

{
  "AddInfo":{
    "UPN":"[email protected]",
    "UserName":"[email protected]",
    "TimeStamp":"2021-10-11T15:54:34:4805634Z",
    "Source":"xyz"
  },
  "ChangeInfo":{
    "UPN":"[email protected]",
    "UserName":"[email protected]",
    "TimeStamp":"2021-10-11T15:54:34:4832421Z",
    "Source":"xyz"
  }
}

I need to update TimeStamp of seconds decimal point from :4832421Z to .4832421Z

Can anyone please help me?

update the_table
  set audit_info=??

CodePudding user response：

Original Question:

If you do not have {:} anywhere else then you can use replace:

UPDATE table_name
SET audit_info = REPLACE(audit_info, '{:}', '{.}');

fiddle

Updated Question:

On later Oracle versions, if you want to update the last : to . in the paths $.AddInfo.TimeStamp and $.ChangeInfo.TimeStamp then you can use use JSON_TABLE to extract the timestamps and then simple string functions to extract the components before and after the last : and then use JSON_MERGEPATCH to update the specific paths:

MERGE INTO table_name dst
USING (
  SELECT t.ROWID AS rid,
         JSON_OBJECT(
           KEY 'AddInfo' VALUE JSON_OBJECT(
             KEY 'TimeStamp'
             VALUE SUBSTR(addinfo_ts, 1, INSTR(addinfo_ts, ':', -1) - 1)
                   || '.' || SUBSTR(addinfo_ts, INSTR(addinfo_ts, ':', -1)   1)
           ),
           KEY 'ChangeInfo' VALUE JSON_OBJECT(
             KEY 'TimeStamp'
             VALUE SUBSTR(changeinfo_ts, 1, INSTR(changeinfo_ts, ':', -1) - 1)
                   || '.' || SUBSTR(changeinfo_ts, INSTR(changeinfo_ts, ':', -1)   1)
           )
         ) AS patch
  FROM   table_name t
         CROSS APPLY JSON_TABLE(
           t.audit_info,
           '$'
           COLUMNS
             addinfo_ts   VARCHAR2(30) PATH '$.AddInfo.TimeStamp',
             changeinfo_ts VARCHAR2(30) PATH '$.ChangeInfo.TimeStamp'
          ) j
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET audit_info = JSON_MERGEPATCH(audit_info, src.patch);

Then, for the sample data, after the MERGE the table contains:

If you do not want to worry about specific paths then you can use a regular expression to match the timestamp:

UPDATE table_name
SET audit_info = REGEXP_REPLACE(
                   audit_info,
                   '("TimeStamp"\s*:\s*"\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}):(\d Z")',
                   '\1.\2'
                 );

fiddle

CodePudding user response：

Try replace. This replace the json field an text and then you convert it again an json. It should be something like this:

update [name_of_the_table] set audit_info = replace(audit_info::TEXT, ':', '.')::jsonb where ....