Analyze object enrichments in BigQuery

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Querying object enrichments with SQL in BigQuery lets you search, filter, and analyze metadata across millions of stored objects without inspecting each object individually.

When you configure Storage Insights datasets, Cloud Storage automatically exports your object inventory and metadata, including object enrichments generated by AI models, directly into BigQuery. This page explains how Cloud Storage structures object enrichments in BigQuery. It also provides SQL recipes for finding labels, detecting faces, identifying explicit content, extracting bounding boxes, and categorizing PDF documents.

Get the required roles

To get the permissions that you need to query object enrichments in BigQuery, ask your administrator to grant you the following IAM roles on your project:

• BigQuery Job User (roles/bigquery.jobUser)
• BigQuery Data Viewer (roles/bigquery.dataViewer)

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to query object enrichments in BigQuery. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to query object enrichments in BigQuery:

• bigquery.jobs.create
• bigquery.tables.getData

You might also be able to get these permissions with custom roles or other predefined roles.

How object enrichments integrates with Storage Insights

When you configure an Storage Insights dataset for your project, Cloud Storage periodically exports your bucket inventory and object contexts into a BigQuery dataset.

In BigQuery, your data appears in the object_contexts_catalog_view view. This view provides a unified catalog of your stored objects. Each row contains object attributes (project, bucket, name, generation) and a repeated contexts record array:

• contexts.key: The unique context identifier. Machine learning enrichments generated by Cloud Storage always start with the google:annotation: prefix (for example, google:annotation:label-detector-v1 or google:annotation:face-detector-v1).
• contexts.value: The model execution status for the enrichment (such as SUCCESS or FAILURE).
• contexts.extendedData: A JSON column containing the model's enrichment payload, such as detected labels, face landmarks, polygon vertices, and category paths.

For the complete table structure and column descriptions, see the Object contexts catalog schema.

Understand query patterns: UNNEST versus EXISTS

An object can have multiple contexts, and each model can output multiple detections (such as multiple labels or detected faces). When querying the object_contexts_catalog_view view, you typically use one of the following SQL patterns:

• Row-per-detection (UNNEST): Use the UNNEST operator on the repeated contexts record array and the model's inner JSON array (by using the JSON_QUERY_ARRAY function). Unnesting flattens repeated enrichments so that each detected feature produces its own row. Use this approach when you need to inspect individual labels, extract bounding box coordinates, or calculate statistics across detections.
• Row-per-object (EXISTS): Use a correlated EXISTS subquery to test whether an object contains a specific label, model output, or threshold. This approach evaluates repeated contexts without unnesting them in the outer query. It returns exactly one row per object regardless of how many detections match.

Query examples

The following SQL examples show how to query and filter object enrichments for common data curation and analysis workflows.

In each query, replace the following:

• PROJECT_ID: The ID of your Google Cloud project.
• DATASET_ID: The name of the BigQuery dataset where Storage Insights exports your data.

Find images containing a specific label

To search for images evaluated by the label-detector model, you can either return each matching label as an individual row or return unique images that contain the label.

Return matching labels (flattens rows)

The following query flattens the contexts and labelAnnotations arrays to return a separate row for each matching label found within an image:

SELECT
  t.project,
  t.bucket,
  t.name,
  t.generation,
  STRING(label.description) AS label_description,
  FLOAT64(label.score) AS confidence_score
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
  UNNEST(t.contexts) AS context,
  UNNEST(JSON_QUERY_ARRAY(context.extendedData.labelAnnotationGroup.labelAnnotations)) AS label
WHERE
  context.key = 'google:annotation:label-detector-v1'
  AND LOWER(STRING(label.description)) = 'car'
  AND FLOAT64(label.score) > 0.75
LIMIT 10;

Return unique images (avoids duplicate rows)

If an image contains multiple matching labels, unnesting produces multiple rows for that object. To return exactly one row per image, check for the existence of the label in an EXISTS subquery:

SELECT
  project,
  bucket,
  name,
  generation
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t
WHERE
  EXISTS(
    SELECT 1
    FROM
      UNNEST(t.contexts) AS context,
      UNNEST(JSON_QUERY_ARRAY(context.extendedData.labelAnnotationGroup.labelAnnotations)) AS label
    WHERE
      context.key = 'google:annotation:label-detector-v1'
      AND LOWER(STRING(label.description)) = 'car'
      AND FLOAT64(label.score) > 0.75
  )
LIMIT 10;

Find portrait and selfie photos with a smiling subject (face detection)

You can combine multiple facial attributes, such as emotional expressions, head orientation angles, and bounding box dimensions, to find specific kinds of photos.

The following query uses a common table expression (CTE) to calculate face bounding box coverage. It filters for smiling, forward-facing portraits where the subject occupies at least 60% of the image:

WITH
  face_stats AS (
    SELECT
      t.project,
      t.bucket,
      t.name,
      t.generation,
      -- Extract the joy likelihood score (0.0 to 1.0)
      FLOAT64(face.joyProbability) AS joy_probability,
      -- Extract head pose orientation angles (degrees)
      FLOAT64(face.rollAngle) AS roll_angle,
      FLOAT64(face.panAngle) AS pan_angle,
      FLOAT64(face.tiltAngle) AS tilt_angle,
      -- Calculate the bounding box area relative to total image area (0.0 to 1.0)
      (
        SELECT AS STRUCT
          (MAX(FLOAT64(v.x)) - MIN(FLOAT64(v.x)))
          * (MAX(FLOAT64(v.y)) - MIN(FLOAT64(v.y))) AS coverage
        FROM
          UNNEST(JSON_QUERY_ARRAY(face.boundingPoly.normalizedVertices)) AS v
      ).coverage AS face_coverage_percentage
    FROM
      `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
      UNNEST(t.contexts) AS context,
      UNNEST(
        JSON_QUERY_ARRAY(
          context.extendedData.faceAnnotationGroup.faceAnnotations)) AS face
    WHERE
      context.key = 'google:annotation:face-detector-v1'
  )
SELECT
  project,
  bucket,
  name,
  generation,
  joy_probability,
  face_coverage_percentage
FROM
  face_stats
WHERE
  joy_probability > 0.5
  AND roll_angle BETWEEN -10 AND 10
  AND pan_angle BETWEEN -10 AND 10
  AND tilt_angle BETWEEN -10 AND 10
  AND face_coverage_percentage >= 0.60
LIMIT 10;

This query applies the following filters:

• joy_probability > 0.5: Selects faces evaluated as expressing happiness or smiling.
• BETWEEN -10 AND 10: Restricts the roll, pan, and tilt angles so the face is oriented directly toward the camera.
• face_coverage_percentage >= 0.60: Uses normalized polygon vertices (0.0 to 1.0) to filter for faces that occupy 60% or more of the frame.

Find images that have content safety flags

The content-safety model evaluates images for adult and violent content. The model assigns each category a likelihood rating, such as VERY_UNLIKELY, UNLIKELY, POSSIBLE, LIKELY, or VERY_LIKELY.

The following query returns unique images that the model flags with high confidence as adult content:

SELECT
  project,
  bucket,
  name,
  generation
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t
WHERE
  EXISTS(
    SELECT 1
    FROM
      UNNEST(t.contexts) AS context
    WHERE
      context.key = 'google:annotation:content-safety-v1'
      AND STRING(context.extendedData.contentSafetyAnnotation.adultContentLikelihood) = 'VERY_LIKELY'
  )
LIMIT 10;

To filter for violent content or different sensitivities, replace the adultContentLikelihood field with the violentContentLikelihood field, or check for the 'LIKELY' rating.

Count images containing a specific label

To count the total number of unique images containing a specific label without double-counting images that match multiple labels, use COUNT(*) with an EXISTS subquery:

SELECT
  COUNT(*) AS image_count
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t
WHERE
  EXISTS(
    SELECT 1
    FROM
      UNNEST(t.contexts) AS context,
      UNNEST(
        JSON_QUERY_ARRAY(
          context.extendedData.labelAnnotationGroup.labelAnnotations)) AS label
    WHERE
      context.key = 'google:annotation:label-detector-v1'
      AND LOWER(STRING(label.description)) = 'ristretto'
      AND FLOAT64(label.score) > 0.5
  );

Extract bounding box coordinates for detected objects

The object-detector model detects physical objects in an image and provides normalized bounding polygons (boundingPoly) indicating where the object appears in the frame.

The following query extracts detected objects named Drink and constructs top-level bounding box coordinate columns (x_min, y_min, x_max, y_max):

SELECT
  t.project,
  t.bucket,
  t.name,
  t.generation,
  STRING(object.name) AS object_name,
  FLOAT64(object.score) AS confidence_score,
  -- Calculate bounding box boundaries from normalized vertices
  (
    SELECT AS STRUCT
      MIN(FLOAT64(v.x)) AS x_min,
      MIN(FLOAT64(v.y)) AS y_min,
      MAX(FLOAT64(v.x)) AS x_max,
      MAX(FLOAT64(v.y)) AS y_max
    FROM
      UNNEST(JSON_QUERY_ARRAY(object.boundingPoly.normalizedVertices)) AS v
  ).*
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
  UNNEST(t.contexts) AS context,
  UNNEST(
    JSON_QUERY_ARRAY(
      context.extendedData.objectAnnotationGroup.objectAnnotations)) AS object
WHERE
  context.key = 'google:annotation:object-detector-v1'
  AND LOWER(STRING(object.name)) = 'drink'
  AND FLOAT64(object.score) > 0.5
LIMIT 10;

The subquery calculates the minimum and maximum normalized coordinates for each polygon, and the .* expression unpacks the x_min, y_min, x_max, and y_max coordinate fields directly into the result set.

Match objects across multiple models

Because Cloud Storage attaches enrichments from each model as independent contexts on the same object, you can join multiple model results to find highly specific content.

The following query unnests contexts twice to find images that the label-detector model classifies as Ristretto and that contain a localized Drink object identified by the object-detector model:

SELECT
  t.project,
  t.bucket,
  t.name,
  t.generation,
  STRING(object.name) AS object_name,
  FLOAT64(object.score) AS object_score,
  STRING(label.description) AS label_description,
  FLOAT64(label.score) AS label_score,
  (
    SELECT AS STRUCT
      MIN(FLOAT64(v.x)) AS x_min,
      MIN(FLOAT64(v.y)) AS y_min,
      MAX(FLOAT64(v.x)) AS x_max,
      MAX(FLOAT64(v.y)) AS y_max
    FROM
      UNNEST(JSON_QUERY_ARRAY(object.boundingPoly.normalizedVertices)) AS v
  ).*
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
  -- Match the label detection context
  UNNEST(t.contexts) AS label_ctx,
  UNNEST(
    JSON_QUERY_ARRAY(
      label_ctx.extendedData.labelAnnotationGroup.labelAnnotations)) AS label,
  -- Match the object detection context on the same object
  UNNEST(t.contexts) AS object_ctx,
  UNNEST(
    JSON_QUERY_ARRAY(
      object_ctx.extendedData.objectAnnotationGroup.objectAnnotations)) AS object
WHERE
  label_ctx.key = 'google:annotation:label-detector-v1'
  AND LOWER(STRING(label.description)) = 'ristretto'
  AND FLOAT64(label.score) > 0.5
  AND object_ctx.key = 'google:annotation:object-detector-v1'
  AND LOWER(STRING(object.name)) = 'drink'
  AND FLOAT64(object.score) > 0.5
LIMIT 10;

Filter PDF documents by classification category

The pdf-classification model assigns hierarchical categories (such as /Finance/Accounting & Auditing or /Law & Government/Legal Services) to stored PDF documents.

The following query finds PDF objects categorized under the /Finance branch with confidence scores greater than 0.5:

SELECT
  t.project,
  t.bucket,
  t.name,
  t.generation,
  STRING(category.label) AS category_name,
  FLOAT64(category.score) AS confidence_score
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
  UNNEST(t.contexts) AS context,
  UNNEST(
    JSON_QUERY_ARRAY(
      context.extendedData.pdfClassificationCategoryList.categories)) AS category
WHERE
  context.key = 'google:annotation:pdf-classification-v1'
  AND STARTS_WITH(STRING(category.label), '/Finance')
  AND FLOAT64(category.score) > 0.5
LIMIT 10;

For the complete list of supported taxonomy categories, see PDF classification categories.

Aggregate PDF documents by classification category

To analyze document distribution across your archive, aggregate and count your PDF objects by category:

SELECT
  STRING(category.label) AS category_name,
  COUNT(*) AS document_count
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
  UNNEST(t.contexts) AS context,
  UNNEST(
    JSON_QUERY_ARRAY(
      context.extendedData.pdfClassificationCategoryList.categories)) AS category
WHERE
  context.key = 'google:annotation:pdf-classification-v1'
GROUP BY
  category_name
ORDER BY
  document_count DESC
LIMIT 10;

Take action on query results

Querying your metadata is often the first step in automated data curation, compliance auditing, or policy enforcement. After you identify specific sets of objects in BigQuery, you can feed those results into downstream workflows, as follows:

• Curate machine learning datasets: Export your query results to a new BigQuery table or CSV file to build targeted training and validation sets for custom models.
• Quarantine or remove policy-violating objects: Feed the object names and bucket locations identified by safety queries into Storage batch operations to move them to restricted quarantine buckets or delete them in bulk.
• Audit content distribution and optimize costs: Aggregate object classifications, dimensions, and categories to understand storage distribution across your organization and create targeted lifecycle management rules.

What's next

• Read the object enrichments overview.
• Configure object enrichments.
• Manage object enrichments.
• Review supported models and JSON schemas.
• Review the featureConfigs JSON API resource.
• Troubleshoot object enrichments issues.
• View and query Storage Insights linked datasets.