Extract Json value from BigQuery column

user_12575531 0
Viewed 88

I have a column that contains a JSON, for ex:

{
    "overview": {
      "resourceName": "VM-test",
      "recommendedAction": "Change machine type",
      "resource": "//compute.googleapis.com/projects/test/zones/europe-west2-c/instances/VM-test",
      "currentMachineType": {
        "cpuMilliVcores": 8000.0,
        "memoryBytes": 3.221225472E10,
        "name": "n1-standard-8",
        "memoryMb": 30720.0,
        "guestCpus": 8.0
      },
      "location": "europe-west2-c",
      "recommendedMachineType": {
        "cpuMilliVcores": 4000.0,
        "name": "custom-4-23552",
        "memoryBytes": 2.4696061952E10,
        "memoryMb": 23552.0,
        "guestCpus": 4.0
      }
    }
}

I would like to extract the "resourceName": and its values into a different column called resourceName and the "resource": "//compute.googleapis.com/projects/test/europe-west2-c/instances/VM-test" onto another one called ProjectName which will hold the project name that is located inside the Uri . I looked over the JSON_EXTRACT but couldn't figure it out.

Any help would be greatly appreciated.

Thank you

google-bigquery json
2 Answers

Try json_value:

with mytable as (
  select '''
  {
    "overview": {
      "resourceName": "VM-test",
      "recommendedAction": "Change machine type",
      "resource": "//compute.googleapis.com/projects/test/zones/europe-west2-c/instances/VM-test",
      "currentMachineType": {
        "cpuMilliVcores": 8000.0,
        "memoryBytes": 3.221225472E10,
        "name": "n1-standard-8",
        "memoryMb": 30720.0,
        "guestCpus": 8.0
      },
      "location": "europe-west2-c",
      "recommendedMachineType": {
        "cpuMilliVcores": 4000.0,
        "name": "custom-4-23552",
        "memoryBytes": 2.4696061952E10,
        "memoryMb": 23552.0,
        "guestCpus": 4.0
      }
    }
  }''' as text
)
select 
  json_value(text, '$.overview.resourceName') as resourceName,
  json_value(text, '$.overview.resource') as projectName,
from mytable

enter image description here

user_134735250

Consider below

select 
  json_value(text, '$.overview.resourceName') as resourceName,
  regexp_extract(json_value(text, '$.overview.resource'), r'/projects/([^/]+)') as projectName
from `project.dataset.table`

If applied to sample data in your question - output is

enter image description here

user_52219440
Related