Structured row key queries

If a table has a row key schema that defines structured row keys, you can use SQL to query the individual segments – or columns – of the row key.

Continuous materialized views, including asynchronous secondary indexes, generate structured row keys by default. You can also define a structured row key for any Bigtable table that you want to query with SQL by creating a row key schema for the table. For more information, see Manage row key schemas.

Keys

In a Bigtable table without a structured row key schema, each row is indexed by a single row key. When you query the table with SQL, the row key is a column named _key that SQL uses as the primary key. It's not a pseudocolumn, so values in the _key column are returned when you execute a SELECT * query on the table.

On the other hand, in a table that has a row key schema, the row key (primary key) is the combination of all the columns named as fields in the row key schema. When you add a row key schema to a table, the _key column becomes a pseudocolumn, which means it won't show up in a SELECT * query, but you can select it explicitly with a SELECT _key statement.

Sample queries

The examples in this section assume that a table named sales has the following schema:

 field { field_name: "user_id" type: { bytes_type { encoding { raw {} } } } } field { field_name: "purchase_date" type: { string_type { encoding { utf8_bytes {} } } } } field { field_name: "order_number" type: { string_type { encoding { utf8_bytes {} } } } } encoding { delimited_bytes { delimiter "#" } } 

The sales table contains the following data. The product column family has two columns.

Structured row key query results

Because the sales table has structured row keys, if you query the table with a SELECT * statement, the query returns each segment of the row key as a separate column. As in any SQL query to a Bigtable table, columns in a column family are expressed as maps.

 SELECT * from sales 

Results look like the following:

You can also specify the row key columns in your query, as shown in the following example:

 SELECT  product[product_type] AS product_type,  product[product_name] AS product_name  FROM sales  WHERE user_id = b"user1" 

The results look like the following:

Filters

You can filter on the row key schema columns using SQL functions. The following example assumes that CURRENT_DATE() returns 2025-05-24:

 SELECT  user_id,  product["product_name"] AS product_name  FROM  sales  WHERE  PARSE_DATE("YYYY-MM-DD", purchase_date) = CURRENT_DATE()  AND user_id = b"user2" 

The results are as follows:

Aggregate queries

The following example shows how use an aggregate query on structured row key fields:

 SELECT  user_id,  product[product_type] AS product_type,  count(*) AS count  FROM sales  GROUP BY 1, 2 

The query results are as follows:

Original row key

To retrieve the original row key in a table with structured row keys, specify the _key column in your query.

 SELECT  _key, user_id  FROM sales 

The query returns the following:

What's next

• GoogleSQL for Bigtable overview
• Schema design best practices