Question 110752 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to update json property of TimeStamp in oracle

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=??
Tags:

2

Answers


  1. 0

    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
....
    Login or Signup to reply.
  2. 0

    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:

    AUDIT_INFO
    {"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"}}

    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}Td{2}:d{2}:d{2}):(d+Z")',
                   '1.2'
                 );

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search