How can I query GCP project labels in BigQuery instead of BigQuery labels?

Below are two approaches depending on how the labels are stored in your schema:

---

1. If the labels field is a JSON object

You can transform the labels record into a JSON string and then use JSON functions to query for specific keys and values. For example:

SQLCopied
SELECT
  name,
  JSON_EXTRACT_SCALAR(TO_JSON_STRING(prj.resource.data.labels), '$.vanity-name') AS vanity_name
FROM cloudresourcemanager_googleapis_com_Project AS prj
WHERE JSON_EXTRACT_SCALAR(TO_JSON_STRING(prj.resource.data.labels), '$.vanity-name') IS NOT NULL;

In this example, the query returns projects that have a key named vanity-name. If you also want to ensure the value equals, say, 'bob', modify the WHERE clause as follows:

SQLCopied
WHERE JSON_EXTRACT_SCALAR(TO_JSON_STRING(prj.resource.data.labels), '$.vanity-name') = 'bob'

---

2. If the labels field is a repeated record (an array of key-value pairs)

You can “unwrap” the labels by using UNNEST. In many asset inventory exports the labels are stored as an array of records with key and value fields. Here’s how you would query that:

SQLCopied
SELECT
  name,
  label.key,
  label.value
FROM cloudresourcemanager_googleapis_com_Project AS prj,
  UNNEST(prj.resource.data.labels) AS label
WHERE label.key = 'vanity-name' 
  AND label.value = 'bob';

If you only want to check for the existence of the key regardless of its value, you can omit the value condition:

SQLCopied
WHERE label.key = 'vanity-name'

---

Summary

• JSON approach: Convert the labels to a JSON string with TO_JSON_STRING then extract specific key values using JSON_EXTRACT_SCALAR. This is useful if labels form a JSON object.
• UNNEST approach: If labels is stored as a repeated field (an array of records), use UNNEST to iterate over the key-value pairs and filter accordingly.

Choose the method that corresponds with how your labels field is structured in your dataset.