COLUMN_FIELD_PATHS view

The INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view contains one row for each column nested within a RECORD (or STRUCT) column.

Required permissions

To query the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view, you need the following Identity and Access Management (IAM) permissions:

bigquery.tables.get
bigquery.tables.list

Each of the following predefined IAM roles includes the preceding permissions:

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

For more information about BigQuery permissions, see Access control with IAM.

Schema

Query results contain one row for each column nested within a RECORD (or STRUCT) column.

When you query the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view, the query results contain one row for each column nested within a RECORD (or STRUCT) column.

The INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view has the following schema:

Column name	Data type	Value
`TABLE_CATALOG`	`STRING`	The project ID of the project that contains the dataset
`TABLE_SCHEMA`	`STRING`	The name of the dataset that contains the table also referred to as the `datasetId`
`TABLE_NAME`	`STRING`	The name of the table or view also referred to as the `tableId`
`COLUMN_NAME`	`STRING`	The name of the column
`FIELD_PATH`	`STRING`	The path to a column nested within a `RECORD` or `STRUCT` column
`DATA_TYPE`	`STRING`	The column's GoogleSQL data type
`DESCRIPTION`	`STRING`	The column's description
`COLLATION_NAME`	`STRING`	The name of the collation specification if it exists; otherwise, `NULL` If a `STRING`, `ARRAY<STRING>`, or `STRING` field in a `STRUCT` is passed in, the collation specification is returned if it exists; otherwise, `NULL` is returned
`ROUNDING_MODE`	`STRING`	The mode of rounding that's used when applying precision and scale to parameterized `NUMERIC` or `BIGNUMERIC` values; otherwise, the value is `NULL`

Scope and syntax

Queries against this view must include a dataset or a region qualifier. For queries 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 more information see Syntax. The following table explains the region and resource scopes for this view:

View name	Resource scope	Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS	Project level	`REGION`
`[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`	Dataset level	Dataset location

Replace the following:

Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.

REGION: any dataset region name. For example, `region-us`.
DATASET_ID: the ID of your dataset. For more information, see Dataset qualifier.

Note: You must use a region qualifier to query INFORMATION_SCHEMA views. The location of the query execution must match the region of the INFORMATION_SCHEMA view.

Example

The following example retrieves metadata from the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view for the commits table in the github_repos dataset. This dataset is part of the BigQuery public dataset program.

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

The commits table contains the following nested and nested and repeated columns:

author: nested RECORD column
committer: nested RECORD column
trailer: nested and repeated RECORD column
difference: nested and repeated RECORD column

To view metadata about the author and difference columns, run the following query.

SELECT
  *
FROM
  `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE
  table_name = 'commits'
  AND (column_name = 'author' OR column_name = 'difference');

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

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