CODEWITHSUNDEEP
google-bigquery
user2362699
user2362699

Reputation: 646

BigQuery Replacing NULL with a string

I am new to BigQuery. I have the following simple query:

SELECT name, assetType,resource.data.shieldedInstanceConfig.enableSecureBoot FROM test001_99a3cr6b.assets_compute_googleapis_com_Instance;

It gives me the the following output:

Row          name                                                                                                   assetType                       enableSecureBoot    
1   //compute.googleapis.com/projects/blah-ddi-test001-prod/zones/us-east4-b/instances/ddi-us-east4-b-5c2b51b2e3740 compute.googleapis.com/Instance null
2   //compute.googleapis.com/projects/blah-ddi-test001-prod/zones/us-east4-a/instances/ddi-us-east4-a-5c2b51b2e3740 compute.googleapis.com/Instance null
3   //compute.googleapis.com/projects/blah-ddi-test001-prod/zones/us-east4-a/instances/ddi-us-east4-a-f2f9a153c4590 compute.googleapis.com/Instance null
4   //compute.googleapis.com/projects/blah-admin-rbs-prod/zones/us-east4-c/instances/krp-pci-krp-vm-hx5q compute.googleapis.com/Instance true
5   //compute.googleapis.com/projects/blah-admin-rbs-prod/zones/us-east4-b/instances/krp-pci-krp-vm-bvt2 compute.googleapis.com/Instance true
6   //compute.googleapis.com/projects/blah-admin-rbs-prod/zones/us-east4-c/instances/orn-test-01 compute.googleapis.com/Instance true
7   //compute.googleapis.com/projects/blah-web-ingress-prod/zones/us-east4-c/instances/instance-1 compute.googleapis.com/Instance true
8   //compute.googleapis.com/projects/blah-network-hub/zones/us-central1-a/instances/orn-test-central1 compute.googleapis.com/Instance true
9   //compute.googleapis.com/projects/blah-network-hub/zones/us-east4-c/instances/orn-test-east4 compute.googleapis.com/Instance true
10  //compute.googleapis.com/projects/blah-network-hub/zones/us-east4-c/instances/test-vm compute.googleapis.com/Instance true
11  //compute.googleapis.com/projects/blah-network-hub/zones/us-central1-a/instances/test-vm-gui compute.googleapis.com/Instance true
12  //compute.googleapis.com/projects/blah-network-hub/zones/us-east4-c/instances/orn-test-east4 compute.googleapis.com/Instance true

I want to replace the values under field 'enableSecureBoot' with 'Not_Set' I tried IFNULL, as well as IF statements, but gives me unrecognized name 'enableSecureBoot' no matter what I do. Can you anyone give me a hint on how to do it, please?

Thanks so much!!

Upvotes: 1

Views: 3057

Answers (2)

Hui Zheng
Hui Zheng

Reputation: 3087

By looking at your SELECT statement, I think resource.data.shieldedInstanceConfig is a STRUCT in your table.

if you want to create a new column enableSecureBoot using the value of a STRUCT field resource.data.shieldedInstanceConfig.enableSecureBoot, you could do below

SELECT 
  name, 
  assetType, 
  IFNULL(
     CAST (resource.data.shieldedInstanceConfig.enableSecureBoot AS STRING),
    'Not_Set'
  ) AS enableSecureBoot
FROM test001_99a3cr6b.assets_compute_googleapis_com_Instance;

Please note that the original resource.data.shieldedInstanceConfig.enableSecureBoot is a BOOLEAN type column, and the new enableSecureBoot is a STRING type column

Upvotes: 3

user2362699
user2362699

Reputation: 646

This is what worked:

SELECT name, assetType,IFNULL(CAST(resource.data.shieldedInstanceConfig.enableSecureBoot  AS STRING), 'NOT SET') AS ENABLED 
FROM test001_99a3cr6b.assets_compute_googleapis_com_Instance;

Thanks again!

Alex

Upvotes: 0

Related Questions