How to replace the data of a specific key inside a JSON in BigQuery?

user_14499048 0
Viewed 111

I have the JSON data in the below format and I want to replace the values of _nanoseconds with some dummy value ('111') without disturbing the JSON structure.

{
  "Category": "Winning",
  "CreatedAt": {
    "_seconds": 1602574892,
    "_nanoseconds": 461491000
  },
  "UpdatedAt": {
    "_seconds": 1602574892,
    "_nanoseconds": 463021000
  }
}
google-bigquery json
1 Answers
select *,
  regexp_replace(json, r'("_nanoseconds": )(\d+)',  '\\1777') as dummy_json
from `project.dataset.table`

if applied to sample data in your question - output is

enter image description here

user_52219440
Related