> [!WARNING]
>
> **Preview**
>
>
> This feature is
>
> subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the
> [Service Specific
> Terms](https://docs.cloud.google.com/terms/service-terms#1).
>
> Pre-GA features are available "as is" and might have limited support.
>
> For more information, see the
> [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages).

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](https://docs.cloud.google.com/storage/docs/insights/datasets.md.txt), 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](https://docs.cloud.google.com/iam/docs/roles-permissions/bigquery.md.txt#bigquery.jobUser) (`roles/bigquery.jobUser`)
- [BigQuery Data Viewer](https://docs.cloud.google.com/iam/docs/roles-permissions/bigquery.md.txt#bigquery.dataViewer) (`roles/bigquery.dataViewer`)


For more information about granting roles, see [Manage access to projects, folders, and organizations](https://docs.cloud.google.com/iam/docs/granting-changing-revoking-access.md.txt).


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](https://docs.cloud.google.com/iam/docs/creating-custom-roles.md.txt) or
other [predefined roles](https://docs.cloud.google.com/iam/docs/roles-overview.md.txt#predefined).

## How object enrichments integrates with Storage Insights

When you configure an [Storage Insights dataset](https://docs.cloud.google.com/storage/docs/insights/datasets.md.txt) 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](https://docs.cloud.google.com/storage/docs/insights/dataset-tables-and-schemas.md.txt#object-contexts-catalog-schema). 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](https://docs.cloud.google.com/storage/docs/insights/dataset-tables-and-schemas.md.txt#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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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`):

```sql
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:

```sql
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:

```sql
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](https://docs.cloud.google.com/storage/docs/object-enrichments/pdf-categories).

### Aggregate PDF documents by classification category

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

```sql
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](https://docs.cloud.google.com/storage/docs/batch-operations/overview.md.txt) 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](https://docs.cloud.google.com/storage/docs/object-enrichments/overview.md.txt).
- [Configure object enrichments](https://docs.cloud.google.com/storage/docs/object-enrichments/configure-object-enrichments.md.txt).
- [Manage object enrichments](https://docs.cloud.google.com/storage/docs/object-enrichments/manage-object-enrichments.md.txt).
- [Review supported models and JSON schemas](https://docs.cloud.google.com/storage/docs/object-enrichments/models.md.txt).
- [Review the `featureConfigs` JSON API resource](https://docs.cloud.google.com/storage/docs/json_api/v1/featureConfigs.md.txt).
- [Troubleshoot object enrichments issues](https://docs.cloud.google.com/storage/docs/storage-intelligence/troubleshooting.md.txt#object-enrichments-errors).
- [View and query Storage Insights linked datasets](https://docs.cloud.google.com/storage/docs/insights/manage-datasets.md.txt#query-linked-datasets).