COLUMN_FIELD_PATHS view

TheINFORMATION_SCHEMA.COLUMN_FIELD_PATHS view contains one row for each columnnested within aRECORD (orSTRUCT) column.

Required permissions

To query theINFORMATION_SCHEMA.COLUMN_FIELD_PATHS view, you need the followingIdentity and Access Management (IAM) permissions:

  • bigquery.tables.get
  • bigquery.tables.list

Each of the following predefined IAM roles includes the precedingpermissions:

  • roles/bigquery.admin
  • roles/bigquery.dataViewer
  • roles/bigquery.dataEditor
  • roles/bigquery.metadataViewer

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

Query results contain one row for each columnnested within aRECORD (orSTRUCT) column.

When you query theINFORMATION_SCHEMA.COLUMN_FIELD_PATHS view, the queryresults contain one row for each columnnested within aRECORD(orSTRUCT) column.

TheINFORMATION_SCHEMA.COLUMN_FIELD_PATHS view has the following schema:

Column nameData typeValue
table_catalogSTRINGThe project ID of the project that contains the dataset.
table_schemaSTRINGThe name of the dataset that contains the table also referred to as thedatasetId.
table_nameSTRINGThe name of the table or view also referred to as thetableId.
column_nameSTRINGThe name of the column.
field_pathSTRINGThe path to a columnnested within a `RECORD` or `STRUCT` column.
data_typeSTRINGThe column's GoogleSQLdata type.
descriptionSTRINGThe column's description.
collation_nameSTRING The name of thecollation specification if it exists; otherwise,NULL.

If aSTRING,ARRAY<STRING>, orSTRING field in aSTRUCT is passed in, the collation specification is returned if it exists; otherwise,NULL is returned.
rounding_modeSTRING The mode of rounding that's used when applying precision and scale to+ parameterizedNUMERIC orBIGNUMERIC values; otherwise, the value isNULL.
data_policies.nameSTRING The list of data policies that are attached to the column to control access and masking. This field is in (Preview).
policy_tagsARRAY<STRING> The list of policy tags that are attached to the column.

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include a dataset or a region qualifier. Forqueries with a dataset qualifier, you must have permissions for the dataset.For queries with a region qualifier, you must have permissions for the project.For moreinformation seeSyntax.The following table explains the region and resource scopes for this view:

View nameResource scopeRegion scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.COLUMN_FIELD_PATHSProject levelREGION
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.COLUMN_FIELD_PATHSDataset levelDataset location
Replace the following:
  • Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
  • REGION: anydataset region name. For example,`region-us`.
  • DATASET_ID: the ID of your dataset. For more information, seeDataset qualifier.

    Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Example

The following example retrieves metadata from theINFORMATION_SCHEMA.COLUMN_FIELD_PATHS view for thecommits table in thegithub_repos dataset.This dataset is part of the BigQuerypublic dataset program.

Because the table you're querying is in another project, thebigquery-public-data project, you add the project ID to the dataset in thefollowing format:`project_id`.dataset.INFORMATION_SCHEMA.view;for example,`bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS.

Thecommits table contains the following nested and nested and repeatedcolumns:

  • author: nestedRECORD column
  • committer: nestedRECORD column
  • trailer: nested and repeatedRECORD column
  • difference: nested and repeatedRECORD column

To view metadata about theauthor anddifference columns, run the following query.

Note:INFORMATION_SCHEMA view names are case-sensitive.
SELECT*FROM`bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHSWHEREtable_name='commits'AND(column_name='author'ORcolumn_name='difference');

The result is similar to the following. For readability, some columnsare excluded from the result.

  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+  | table_name | column_name |     field_path      |                                                                      data_type                                                                      | description | policy_tags |  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+  | commits    | author      | author              | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>                                                                  | NULL        | 0 rows      |  | commits    | author      | author.name         | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | author      | author.email        | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | author      | author.time_sec     | INT64                                                                                                                                               | NULL        | 0 rows      |  | commits    | author      | author.tz_offset    | INT64                                                                                                                                               | NULL        | 0 rows      |  | commits    | author      | author.date         | TIMESTAMP                                                                                                                                           | NULL        | 0 rows      |  | commits    | difference  | difference          | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL        | 0 rows      |  | commits    | difference  | difference.old_mode | INT64                                                                                                                                               | NULL        | 0 rows      |  | commits    | difference  | difference.new_mode | INT64                                                                                                                                               | NULL        | 0 rows      |  | commits    | difference  | difference.old_path | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | difference  | difference.new_path | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | difference  | difference.old_sha1 | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | difference  | difference.new_sha1 | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | difference  | difference.old_repo | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | difference  | difference.new_repo | STRING                                                                                                                                              | NULL        | 0 rows      |  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.