Table functions (built in)

GoogleSQL for Bigtable supports built-in table functions.

This topic includes functions that produce columns of a table.You can only use these functions in theFROM clause.

Function list

NameSummary
Temporal filters Access temporal elements of a table by using the Bigtable table name as a function.
UNPACK Expands the timestamped values in an input row into multiple rows, each row representing a different timestamp; and moves the timestamps into a `_timestamp` column.

Temporal filters

my_table(with_history=>true,after=>TIMESTAMP(),after_or_equal=>TIMESTAMP(),before=>TIMESTAMP(),before_or_equal=>TIMESTAMP(),latest_n=>4)

Description

To apply a temporal filter, use the table name as the function name. Thefunction can be called withnamed arguments.

Each Bigtable cell contains a unique timestamped version of the data forthat row and column. Temporal filters expose the timestamp associated with eachcell. They let you query time series data and filter for timestamps andversions.

Arguments

  • with_history:BOOL WhenTRUE the columns are expanded to includetimestamp. More specifically, the columns becomeMAP<KEY_TYPE, ARRAY<STRUCT<TIMESTAMP, VALUE_TYPE>>>. Default toFALSE.

The following parameters must also includewith_history => true:

  • after:TIMESTAMP Values with timestamps after the input, exclusive.
  • after_or_equal:TIMESTAMP Values with timestamps after the input,inclusive.
  • before:TIMESTAMP Values with timestamps before the input, exclusive.
  • before_or_equal:TIMESTAMP Values with timestamps before the input,inclusive.
  • latest_n:INT64 The number of timestamped values to return per columnqualifier (map key). Must be greater than or equal to 1.

The following parameters mustnot includewith_history => true:

  • as_of:TIMESTAMP Returns the latest values with timestamps less than orequal to the provided timestamp.

Return type

TABLE

Example

The following query reads thestats_summary column in a table calledtest_table withwith_history:

SELECTstats_summaryFROMtest_table(with_history=>true)LIMIT2/*-------------------------------------------------------------------------+ | stats_summary                                                           | +-------------------------------------------------------------------------+ | {                                                                       | |   "connected_cell" : [                                                  | |     {timestamp: 2025-03-28T14:15:00.57-04:00, value:"2"},               | |     {timestamp: 2025-03-28T14:12:56.762-04:00, value:"1"}               | |   ],                                                                    | |   "connected_wifi" : [                                                  | |     {timestamp:2025-03-28T14:15:00.57-04:00, value:"5"},                | |     {timestamp:2025-03-28T14:12:56.762-04:00, value:"1"}                | |   ],                                                                    | |   "os_build" : [                                                        | |     {timestamp:2025-03-28T14:15:00.57-04:00, value:"PQ2A.190405.003"},  | |     {timestamp:2025-03-28T14:12:56.762-04:00, value:"PQ2A.190405.003"}  | |   ]                                                                     | | }                                                                       | +-------------------------------------------------------------------------+ | {                                                                       | |   "connected_cell" : [                                                  | |     {timestamp:2025-03-28T14:15:17.371-04:00, value:"2"},               | |     {timestamp:2025-03-28T14:13:42.018-04:00, value:"1"}                | |   ],                                                                    | |   "connected_wifi" : [                                                  | |     {timestamp:2025-03-28T14:15:17.371-04:00, value:"5"},               | |     {timestamp:2025-03-28T14:13:42.018-04:00, value:"1"}                | |   ],                                                                    | |   "os_build" : [                                                        | |     {timestamp:2025-03-28T14:15:17.371-04:00, value:"PQ2A.190405.004"}, | |     {timestamp:2025-03-28T14:13:42.018-04:00, value:"PQ2A.190405.004"}  | |   ]                                                                     | | }                                                                       | +-------------------------------------------------------------------------*/

You can filter for a column qualifier within a timestamp range:

SELECTstats_summary['connected_wifi']asconnected_wifiFROMtest_table(with_history=>true,after=>TIMESTAMP("2025-03-28 14:13:40-0400"),before_or_equal=>TIMESTAMP("2025-03-28 14:15:10-04:00"))LIMIT2/*--------------------------------------------------------+ | connected_wifi                                         | +--------------------------------------------------------+ | [{timestamp:2025-03-28T14:15:00.57-04:00, value:"5"}]  | +--------------------------------------------------------+ | [{timestamp:2025-03-28T14:13:42.018-04:00, value:"1"}] | +--------------------------------------------------------*/

You can also filter withas_of, which doesn't return timestamps:

SELECTstats_summaryFROMtest_table(as_of=>TIMESTAMP("2025-03-28 14:15:10-04:00"))LIMIT2;/*----------------------------------+ | stats_summary                    | +----------------------------------+ | {                                | |   "connected_cell" : "2",        | |   "connected_wifi" : "5",        | |   "os_build" : "PQ2A.190405.003" | | }                                | +----------------------------------+ | {                                | |   "connected_cell" : "1",        | |   "connected_wifi" : "1",        | |   "os_build" : "PQ2A.190405.004" | | }                                | +----------------------------------*/

UNPACK

UNPACK(TABLEtable(with_history=>true))

Description

This function takes a input table and expands it so that each input timestampedvalue is expanded into its own row representing that timestamp. The timestampsare moved to a separate_timestamp column.

TheUNPACK function has the following behavior and restrictions:

  • Input column familiesMAP<key, ARRAY<STRUCT<timestamp, value>>> aretransformed toMAP<key, value>.
  • Input column qualifiersARRAY<STRUCT<timestamp, value>>> are transformedtovalue.
  • Other column types are unchanged.
  • The input table must use theTemporal filterwith_history => true in order to expand the timestamps.
  • The columns must be selected in the subquery in order to be unpacked andselected.
  • The new_timestamp column doesn't need to be selected in order for thetimestamps to be unpacked.

Return type

TABLE

Example

All the examples are based on this table:

SELECT_key,reportFROMtable(with_history=>true)/*-------------------------------------------------------------------+ | _key  | report                                                    | +-------------------------------------------------------------------+ | Dept1 | "expenses": [                                             | |       |   { timestamp: 2025-04-01T21:00:00Z, value: "3" },        | |       |   { timestamp: 2025-04-01T18:00:00Z, value: "2" }         | |       | ],                                                        | |       | "sales": [                                                | |       |   { timestamp: 2025-04-01T22:00:00Z, value: "12" },       | |       |   { timestamp: 2025-04-01T20:00:00Z, value: "10" }        | |       | ]                                                         |  +------------------------------------------------------------------+ | Dept2 | "expenses": [                                             | |       |   { timestamp: 2025-04-01T19:00:00Z, value: "2" },        | |       |   { timestamp: 2025-04-01T18:00:00Z, value: "1" }         | |       | ],                                                        | |       | "sales": [                                                | |       |   { timestamp: 2025-04-01T23:00:00Z, value: "9" },        | |       |   { timestamp: 2025-04-01T19:00:00Z, value: "5" }         | |       | ]                                                         | +-------------------------------------------------------------------*/

You can unpack the timestamps from thereport column. Each row is expandedinto multiple rows, one per timestamp:

SELECT*FROMUNPACK((SELECT_key,reportFROMtable(with_history=>true)))/*----------------------------------------------------------------+ | _key  | report                          | _timestamp           | +----------------------------------------------------------------+ | Dept1 | { sales: "12" }                 | 2025-04-01T22:00:00Z | | Dept1 | { expenses: "3" }               | 2025-04-01T21:00:00Z | | Dept1 | { expenses: "2" }               | 2025-04-01T18:00:00Z | | Dept1 | { sales: "10" }                 | 2025-04-01T20:00:00Z | | Dept2 | { expenses: "1" }               | 2025-04-01T18:00:00Z | | Dept2 | { expenses: "2", sales: "5" }   | 2025-04-01T19:00:00Z | | Dept2 | { sales: "9" }                  | 2025-04-01T23:00:00Z | +----------------------------------------------------------------*/

You can unpack the column qualifiers individually. Only the timestamps from thesales column qualifier are unpacked:

SELECT_key,_timestamp,salesFROMUNPACK((SELECT_key,report['sales']assalesFROMtable(with_history=>true)))/*--------------------------------------+ | _key  | _timestamp           | sales | +--------------------------------------+ | Dept1 | 2025-04-01T20:00:00Z | "10"  | | Dept1 | 2025-04-01T22:00:00Z | "12"  | | Dept2 | 2025-04-01T19:00:00Z | "5"   | | Dept2 | 2025-04-01T23:00:00Z | "9"   | +--------------------------------------*/

You can unpack the entire column family and only select certain columnqualifiers. This results inNULL values where thesales column doesn't havea value for the timestamp:

SELECT_key,_timestamp,report['sales']assales,FROMUNPACK((SELECT_key,reportFROMtable(with_history=>true)))/*--------------------------------------+ | _key  | _timestamp           | sales | +--------------------------------------+ | Dept1 | 2025-04-01T22:00:00Z | "12"  | | Dept1 | 2025-04-01T21:00:00Z | NULL  | | Dept1 | 2025-04-01T18:00:00Z | NULL  | | Dept1 | 2025-04-01T20:00:00Z | "10"  | | Dept2 | 2025-04-01T18:00:00Z | NULL  | | Dept2 | 2025-04-01T19:00:00Z | "5"   | | Dept2 | 2025-04-01T23:00:00Z | "9"   | +--------------------------------------*/

You don't have to select the_timestamp column. The timestamps are stillunpacked without selecting the column:

SELECT_key,expensesFROMUNPACK((SELECT_key,report['sales']assales,report['expenses']asexpensesFROMtable(with_history=>true)))/*------------------+ | _key  | expenses | +------------------+ | Dept1 | NULL     | | Dept1 | NULL     | | Dept1 | 3        | | Dept1 | 2        | | Dept2 | NULL     | | Dept2 | 2        | | Dept2 | 1        | +------------------*/

You can unpack an input table that doesn't have any timestamps, but it doesn'ttransform any of the columns or unpack any timestamps because there are notimestamps in the input table to unpack:

SELECT_key,_timestampFROMUNPACK((SELECT_keyFROMtable(with_history=>true)))/*--------------------+ | _key  | _timestamp | +--------------------+ | Dept1 | NULL       | | Dept2 | NULL       | +--------------------*/

ORDER BY within theUNPACK subquery is ignored. In order to correctly orderthe result, theORDER BY must be on the outer query:

SELECT_key,_timestamp,expenses,constantFROMUNPACK((SELECT_key,report['expenses']asexpenses,"5"asconstantFROMtable(with_history=>true)ORDERBY_key))-- Does NOT affect the orderingORDERBYexpenses-- Does affect the ordering/*----------------------------------------------------+ | _key  | _timestamp           | expenses | constant | +----------------------------------------------------+ | Dept2 | 2025-04-01T18:00:00Z | "1"      | 5        | | Dept1 | 2025-04-01T18:00:00Z | "2"      | 5        | | Dept2 | 2025-04-01T19:00:00Z | "2"      | 5        | | Dept1 | 2025-04-01T21:00:00Z | "3"      | 5        | +----------------------------------------------------*/

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-17 UTC.