Query syntax

Query statements scan one or more tables or expressions and return the computedresult rows. This topic describes the syntax for SQL queries inGoogleSQL for Bigtable.

SQL syntax notation rules

The following table lists and describes the syntax notation rules that GoogleSQLdocumentation commonly uses.

NotationExampleDescription
Square brackets[ ]Optional clauses
Parentheses( )Literal parentheses
Vertical bar|LogicalXOR (exclusiveOR)
Curly braces{ }A set of options, such as{ a | b | c }. Select one option.
Ellipsis...The preceding item can repeat.
Comma,Literal comma
Comma followed by an ellipsis, ...The preceding item can repeat in a comma-separated list.
Item listitem [, ...]One or more items
[item, ...]Zero or more items
Double quotes""The enclosed syntax characters (for example,"{"..."}") are literal and required.
Angle brackets<>Literal angle brackets

SQL syntax

query_statement:query_exprquery_expr:  {select | (query_expr ) }  [ORDERBYexpression [ ASC ] ]  [LIMITcount ]select:SELECTselect_list  [FROMfrom_clause[, ...] ]  [WHEREbool_expression ]  [GROUP BYgroup_by_specification ]

SELECT statement

SELECTselect_listselect_list:  {select_all |select_expression } [, ...]select_all:  [expression. ]*  [ EXCEPT (column_name [, ...] ) ]  [ REPLACE (expression AScolumn_name [, ...] ) ]select_expression:expression [ [ AS ]alias ]

TheSELECT list defines the columns that the query will return. Expressions intheSELECT list can refer to columns in any of thefrom_items in itscorrespondingFROM clause.

Each item in theSELECT list is one of:

  • *
  • expression
  • expression.*

SELECT *

SELECT *, often referred to asselect star, produces one output column foreach column that's visible after executing the full query.

SELECT*FROMexample_table;/*-------------------------------------------------------------------------------+ | _key | f1               | f2                                 |    f3          | +-------------------------------------------------------------------------------+ | a#01 | {                | {                                  | {              | |      |   "c1": "xyz",   |   "col1": "def",                   |   "cl1": "{    | |      |   "c2": "nop"    |   "col2": "125",                   |     "n": "Def",| |      | }                |   "col3": "2008-12-25 15:30:00+00" |     "g": 40,   | |      |                  | }                                  |     "k": 3     | |      |                  |                                    |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------+ | a#02 | {                | {                                  | {              | |      |   "c1": "zyx",   |   "col1": "cba",                   |   "cl1": "{    | |      | }                |   "col2": "123",                   |     "n": "Gih",| |      |                  |   "col3": "2018-10-15 15:30:00+00" |     "g": 20,   | |      |                  | }                                  |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------+ | a#03 | {                | {                                  | {              | |      |   "c1": "abc",   |   "col1": "cba",                   |   "cl1": "{    | |      |   "c2": "def"    |   "col2": "543",                   |     "n": "Xyz",| |      | }                |   "col3": "2020-11-10 13:21:00+00" |     "g": 15,   | |      |                  | }                                  |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------+ | b#01 | {                | {                                  | NULL           | |      |   "c1": "jkl",   |   "col1": "qrs",                   |                | |      |   "c2": "mno"    |   "col2": "654",                   |                | |      | }                |   "col3": "2021-10-10 14:18:03+07" |                | |      |                  | }                                  |                | +-------------------------------------------------------------------------------+ | b#02 | {                | {                                  | NULL           | |      |   "c1": "hi",    |   "col1": "tu",                    |                | |      |   "c2": "no"     |   "col3": "2023-10-10 11:28:09+05",|                | |      | }                | }                                  |                | +-------------------------------------------------------------------------------+ | c#03 | {                | {                                  | {              | |      |   "c1": "j",     |   "col1": "u",                     |   "cl1": "{    | |      |   "c2": "l"      |  }                                 |     "n": "T",  | |      | }                | }                                  |     "g": 22,   | |      |                  |                                    |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------*/

SELECT expression

Items in aSELECT list can be expressions. These expressions evaluate to asingle value and produce one output column, with an optional explicitalias.

If the expression doesn't have an explicit alias, it receives an implicit aliasaccording to the rules forimplicit aliases, if possible.Otherwise, the column is anonymous and you can't refer to it by name elsewherein the query.

In the following example, the first expression has an explicit aliasforf1 and the second expression has an implicit alias forf1:

SELECTexample_table.f1FROMexample_table;SELECTf1FROMexample_table;/*-------------------------------------+ | _key | f1                           | +-------------------------------------+ | a#01 | { "c1": "xyz", "c2": "nop" } | | a#02 | { "c1": "zyx" }              | | a#03 | { "c1": "abc", "c2": "def" } | | b#01 | { "c1": "jkl", "c2": "mno" } | | b#02 | { "c1": "hi", "c2": "no" }   | | c#03 | { "c1": "j", "c2": "l" }     | +-------------------------------------*/

SELECT expression.*

An item in aSELECT list can also take the form ofexpression.*. Thisproduces one output column for each column or top-level field ofexpression.The expression must either be a table alias or evaluate to a single value of adata type with fields, such as a STRUCT.

SELECTexample_table.*FROMexample_table;/*-------------------------------------------------------------------------------+ | _key | f1               | f2                                 |    f3          | +-------------------------------------------------------------------------------+ | a#01 | {                | {                                  | {              | |      |   "c1": "xyz",   |   "col1": "def",                   |   "cl1": "{    | |      |   "c2": "nop"    |   "col2": "125",                   |     "n": "Def",| |      | }                |   "col3": "2008-12-25 15:30:00+00" |     "g": 40,   | |      |                  | }                                  |     "k": 3     | |      |                  |                                    |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------+ | a#02 | {                | {                                  | {              | |      |   "c1": "zyx",   |   "col1": "cba",                   |   "cl1": "{    | |      | }                |   "col2": "123",                   |     "n": "Gih",| |      |                  |   "col3": "2018-10-15 15:30:00+00" |     "g": 20,   | |      |                  | }                                  |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------+ | a#03 | {                | {                                  | {              | |      |   "c1": "abc",   |   "col1": "cba",                   |   "cl1": "{    | |      |   "c2": "def"    |   "col2": "543",                   |     "n": "Xyz",| |      | }                |   "col3": "2020-11-10 13:21:00+00" |     "g": 15,   | |      |                  | }                                  |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------+ | b#01 | {                | {                                  | NULL           | |      |   "c1": "jkl",   |   "col1": "qrs",                   |                | |      |   "c2": "mno"    |   "col2": "654",                   |                | |      | }                |   "col3": "2021-10-10 14:18:03+07" |                | |      |                  | }                                  |                | +-------------------------------------------------------------------------------+ | b#02 | {                | {                                  | NULL           | |      |   "c1": "hi",    |   "col1": "tu",                    |                | |      |   "c2": "no"     |   "col3": "2023-10-10 11:28:09+05",|                | |      | }                | }                                  |                | +-------------------------------------------------------------------------------+ | c#03 | {                | {                                  | {              | |      |   "c1": "j",     |   "col1": "u",                     |   "cl1": "{    | |      |   "c2": "l"      |  }                                 |     "n": "T",  | |      | }                | }                                  |     "g": 22,   | |      |                  |                                    |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------*/

SELECT * EXCEPT

ASELECT * EXCEPT statement specifies the names of one or more columns toexclude from the result. All matching column names are omitted from the output.

SELECT*EXCEPT(f2)FROMexample_table;/*---------------------------------------------------------------------------------+ | _key | f1                           | f3                                        | +---------------------------------------------------------------------------------+ | a#01 | { "c1": "xyz", "c2": "nop" } | { "cl1": "{"n": "Def", "g": 40,"k": 3}" } | | a#02 | { "c1": "zyx" }              | { "cl1": "{"n": "Gih", "g": 20}" }        | | a#03 | { "c1": "abc", "c2": "def" } | { "cl1": "{"n": "Xyz", "g": 15}" }        | | b#01 | { "c1": "jkl", "c2": "mno" } | NULL                                      | | b#02 | { "c1": "hi", "c2": "no" }   | NULL                                      | | c#03 | { "c1": "j", "c2": "l" }     | { "cl1": "{"n": "T", "g": 22}" }          | +---------------------------------------------------------------------------------*/
Note:SELECT * EXCEPT doesn't exclude columns that don't have names.

SELECT * REPLACE

ASELECT * REPLACE statement specifies one or moreexpression AS identifier clauses. Each identifier must match a column namefrom theSELECT * statement. In the output column list, the column thatmatches the identifier in aREPLACE clause is replaced by the expression inthatREPLACE clause.

ASELECT * REPLACE statement doesn't change the names or order of columns.However, it can change the value and the value type.

SELECT*REPLACE(f1ASf2)FROMsample1;/*----------------------------------------------------------------------------------------------------------------+ | _key | f1                           | f2                           | f3                                        | +----------------------------------------------------------------------------------------------------------------+ | a#01 | { "c1": "xyz", "c2": "nop" } | { "c1": "xyz", "c2": "nop" } | { "cl1": "{"n": "Def", "g": 40,"k": 3}" } | | a#02 | { "c1": "zyx" }              | { "c1": "zyx" }              | { "cl1": "{"n": "Gih", "g": 20}" }        | | a#03 | { "c1": "abc", "c2": "def" } | { "c1": "abc", "c2": "def" } | { "cl1": "{"n": "Xyz", "g": 15}" }        | | b#01 | { "c1": "jkl", "c2": "mno" } | { "c1": "jkl", "c2": "mno" } | NULL                                      | | b#02 | { "c1": "hi", "c2": "no" }   | { "c1": "hi", "c2": "no" }   | NULL                                      | | c#03 | { "c1": "j", "c2": "l" }     | { "c1": "j", "c2": "l" }     | { "cl1": "{"n": "T", "g": 22}" }          | +----------------------------------------------------------------------------------------------------------------*/
Note:SELECT * REPLACE doesn't replace columns that don't have names.

FROM clause

FROMfrom_clause[, ...]from_clause:from_itemfrom_item:table_name [as_alias ]as_alias:  [ AS ]alias

TheFROM clause indicates the table or tables from which to retrieve rows,and specifies how to join those rows together to produce a single stream ofrows for processing in the rest of the query.

table_name

The name of an existing table.

SELECT * FROM Roster;

unnest_operator

SeeUNNEST operator.

UNNEST operator

unnest_operator:  {UNNEST(array ) [as_alias ]    |array_path [as_alias ]  }  [ WITH OFFSET [as_alias ] ]array:  {array_expression |array_path }as_alias:  [AS]alias

TheUNNEST operator takes an array and returns a table with one row for eachelement in the array. The output ofUNNEST is one [value table][value-tables] column.For theseARRAY element types,SELECT * against the value table columnreturns multiple columns:

  • STRUCT

Input values:

  • array_expression: An expression that produces an array and that's not anarray path.

  • array_path: Thepath to anARRAY type.

    • In an implicitUNNEST operation, the pathmuststart witharange variable name.
    • In an explicitUNNEST operation, the path can optionally start with arange variable name.

    TheUNNEST operation with anycorrelatedarray_path mustbe on the right side of aCROSS JOIN,LEFT JOIN, orINNER JOIN operation.

  • as_alias: If specified, defines the explicit name of the value tablecolumn containing the array element values. It can be used to refer tothe column elsewhere in the query.

  • WITH OFFSET:UNNEST destroys the order of elements in the inputarray. Use this optional clause to return an additional column withthe array element indexes, oroffsets. Offset counting starts at zero foreach row produced by theUNNEST operation. This column has anoptional alias; If the optional alias isn't used, the default column name isoffset.

    Example:

    SELECT*FROMUNNEST([10,20,30])asnumbersWITHOFFSET;/*---------+--------+ | numbers | offset | +---------+--------+ | 10      | 0      | | 20      | 1      | | 30      | 2      | +---------+--------*/

To learn more about the ways you can useUNNEST explicitly and implicitly,seeExplicit and implicitUNNEST.

UNNEST and structs

For an input array of structs,UNNESTreturns a row for each struct, with a separate column for each field in thestruct. The alias for each column is the name of the corresponding structfield.

Example:

SELECT*FROMUNNEST(ARRAY<STRUCT<xINT64,ySTRING,zSTRUCT<aINT64,bINT64>>>[(1,'foo',(10,11)),(3,'bar',(20,21))]);/*---+-----+----------+ | x | y   | z        | +---+-----+----------+ | 1 | foo | {10, 11} | | 3 | bar | {20, 21} | +---+-----+----------*/

Explicit and implicitUNNEST

Array unnesting can be either explicit or implicit. To learn more, see thefollowing sections.

Explicit unnesting

TheUNNEST keyword is required in explicit unnesting. For example:

SELECTresultsFROM(SELECT[1,2]ASposition)asCoordinates,UNNEST(Coordinates.position)ASresults;

This example and the following examples use thearray_path calledCoordinates.position to illustrate unnesting.

Implicit unnesting

TheUNNEST keyword isn't used in implicit unnesting.

For example:

SELECTresultsFROM(SELECT[1,2]ASposition)asCoordinates,Coordinates.positionASresults;
Tables and implicit unnesting

When you usearray_path with implicitUNNEST,array_path must be prependedwith the table. For example:

SELECTresultsFROM(SELECT[1,2]ASposition)asCoordinates,Coordinates.positionASresults;

UNNEST andNULL values

UNNEST treatsNULL values as follows:

  • NULL and empty arrays produce zero rows.
  • An array containingNULL values produces rows containingNULL values.

WHERE clause

WHERE bool_expression

TheWHERE clause filters the results of theFROM clause.

Only rows whosebool_expression evaluates toTRUE are included. Rowswhosebool_expression evaluates toNULL orFALSE arediscarded.

The evaluation of a query with aWHERE clause is typically completed in thisorder:

  • FROM
  • WHERE
  • GROUP BY and aggregation
  • ORDER BY
  • LIMIT

Evaluation order doesn't always match syntax order.

TheWHERE clause only references columns available via theFROM clause;it can't referenceSELECT list aliases.

Examples

This query returns returns all rows from theexample_table tablewheref2['col2'] starts with1:

SELECTf2FROMexample_tableWHERESTARTS_WITH(CAST(f2['col2']ASSTRING),"1");/*--------------------------------------------------------------------+ | f2                                                                 | +--------------------------------------------------------------------+ | { "col1": "def", "col2": "125", "col3": "2008-12-25 15:30:00+00" } | | { "col1": "cba", "col2": "123", "col3": "2018-10-15 15:30:00+00" } | +--------------------------------------------------------------------*/

Thebool_expression can contain multiple sub-conditions:

SELECTf2FROMexample_tableWHERESTARTS_WITH(CAST(f2['col1']ASSTRING),"c")ORSTARTS_WITH(CAST(f2['col2']ASSTRING),"1");/*--------------------------------------------------------------------+ | f2                                                                 | +--------------------------------------------------------------------+ | { "col1": "def", "col2": "125", "col3": "2008-12-25 15:30:00+00" } | | { "col1": "cba", "col2": "123", "col3": "2018-10-15 15:30:00+00" } | | { "col1": "cba", "col2": "543", "col3": "2020-11-10 13:21:00+00" } | +--------------------------------------------------------------------*/

GROUP BY clause

GROUP BYgroupable_items

Description

TheGROUP BY clause groups together rows in a table that share common valuesfor certain columns. For a group of rows in the source table withnon-distinct values, theGROUP BY clause aggregates them into a singlecombined row. This clause is commonly used when aggregate functions arepresent in theSELECT list, or to eliminate redundancy in the output.

Definitions

Group rows by groupable items

GROUP BYgroupable_item[, ...]groupable_item:  {value    |value_alias    |column_ordinal  }

Description

TheGROUP BY clause can includegroupable expressionsand their ordinals.

Definitions

Group rows by values

TheGROUP BY clause can group rows in a table with non-distinctvalues in theGROUP BY clause. For example:

SELECTSUBSTR(name['first'],0,1),COUNT(*)AScountFROMexample_tableGROUPBY$col1

GROUP BY clauses may also refer to aliases. If a query contains aliases intheSELECT clause, those aliases override names in the correspondingFROMclause. For example:

SELECTSUBSTR(name['first'],0,1)ASfirst_letter,COUNT(*)AScountFROMexample_tableGROUPBYfirst_letter

To learn more about the data types that are supported for values in theGROUP BY clause, seeGroupable data types.

Group rows by column ordinals

TheGROUP BY clause can refer to expression names in theSELECT list. TheGROUP BY clause also allows ordinal references to expressions in theSELECTlist, using integer values.1 refers to the first value in theSELECT list,2 the second, and so forth. The value list can combineordinals and value names. The following queries are equivalent:

SELECTSUBSTR(name['first'],0,1)ASfirst_letter,COUNT(*)AScountFROMexample_tableGROUPBY1

ORDER BY clause

ORDER BY _key [ ASC ]

TheORDER BY clause specifies a column or expression as the sort criterion forthe result set. If anORDER BY clause isn't present, the order of the resultsof a query isn't defined. Only the_key generated column can be used with theORDER BY clause.

Optional Clauses

  • ASC: Sort the results in ascending order of_key values.ASC is thedefault value.

Examples

Use the default sort order (ascending).

SELECT_key,f1FROMexample_tableORDERBY_key;SELECT_key,f1FROMexample_tableORDERBY_keyASC;/*-------------------------------------+ | _key | f1                           | +-------------------------------------+ | a#01 | { "c1": "xyz", "c2": "nop" } | | a#02 | { "c1": "zyx" }              | | a#03 | { "c1": "abc", "c2": "def" } | | b#01 | { "c1": "jkl", "c2": "mno" } | | b#02 | { "c1": "hi", "c2": "no" }   | | c#03 | { "c1": "j", "c2": "l" }     | +-------------------------------------*/

LIMIT clause

LIMITcount

Limits the number of rows to return in a query.

Definitions

  • LIMIT: Limits the number of rows to produce.

    count is anINT64 constant expression that represents thenon-negative, non-NULL limit. No more thancount rows are produced.LIMIT 0 returns 0 rows.

Details

The rows that are returned byLIMIT have undefined order unlessthese clauses are used afterORDER BY.

A constant expression can be represented by a general expression, literal, orparameter value.

Note: Although theLIMIT clause limits the rows that a query produces, itdoesn't limit the amount of data processed by that query.

Examples

SELECT*FROMUNNEST(ARRAY<STRING>['a','b','c','d','e'])ASletterORDERBYletterASCLIMIT2;/*---------+ | letter  | +---------+ | a       | | b       | +---------*/
SELECTf1FROMexample_tableLIMIT2;/*-------------------------------------+ | _key | f1                           | +-------------------------------------+ | a#01 | { "c1": "xyz", "c2": "nop" } | | a#02 | { "c1": "zyx" }              | +-------------------------------------*/

Using aliases

An alias is a temporary name given to a table, column, or expression present ina query. You can introduce explicit aliases in theSELECT list orFROMclause, or GoogleSQL infers an implicit alias for some expressions.Expressions with neither an explicit nor implicit alias are anonymous and thequery can't reference them by name.

Explicit aliases

You can introduce explicit aliases in either theFROM clause or theSELECTlist.

In aFROM clause, you can introduce explicit aliases for any item, includingtables, arrays, andUNNEST clauses, using[AS] alias. TheASkeyword is optional.

Example:

SELECTbar.f1FROMexample_tableASbar;

You can introduce explicit aliases for any expression in theSELECT list using[AS] alias. TheAS keyword is optional.

Example:

SELECTf1ASfooFROMexample_table;

Implicit aliases

In theSELECT list, if there is an expression that doesn't have an explicitalias, GoogleSQL assigns an implicit alias according to the followingrules. There can be multiple columns with the same alias in theSELECT list.

  • For identifiers, the alias is the identifier. For example,SELECT abcimpliesAS abc.
  • For path expressions, the alias is the last identifier in the path. Forexample,SELECT abc.def.ghi impliesAS ghi.
  • For field access using the "dot" member field access operator, the alias isthe field name. For example,SELECT (struct_function()).fname impliesASfname.

In all other cases, there is no implicit alias, so the column is anonymous andcan't be referenced by name. The data from that column will still be returnedand the displayed query results may have a generated label for that column, butthe label can't be used like an alias.

In aFROM clause,from_items aren't required to have an alias. Thefollowing rules apply:

  • If there is an expression that doesn't have an explicit alias, GoogleSQL assigns an implicit alias in these cases:
    • For identifiers, the alias is the identifier. For example,FROM abc impliesAS abc.
    • For path expressions, the alias is the last identifier in the path. For example,FROM abc.def.ghi impliesAS ghi
    • The column produced usingWITH OFFSET has the implicit aliasoffset.
  • FROM UNNEST(x) doesn't have an implicit alias.

Alias visibility

After you introduce an explicit alias in a query, there are restrictions onwhere else in the query you can reference that alias. These restrictions onalias visibility are the result of GoogleSQL name scoping rules.

Visibility in theFROM clause

GoogleSQL processes aliases in aFROM clause from left to right,and aliases are visible only to subsequent path expressions in aFROMclause.

You can use any column name from a table in theFROM as an alias anywhere inthe query, with or without qualification with the table name.

Example:

SELECTf1,s.f2FROMexample_tableASsWHERESTARTS_WITH(CAST(f2['col1']ASSTRING),"1");

If theFROM clause contains an explicit alias, you must use the explicit aliasinstead of the implicit alias for the remainder of the query (seeImplicit Aliases). A table alias is useful for brevity orto eliminate ambiguity in cases such as self-joins, where the same table isscanned multiple times during query processing.

Example:

SELECT*FROMexample_tableASs,ORDERBYs._key

Invalid —ORDER BY doesn't use the table alias:

SELECT*FROMexample_tableASsORDERBYexample_table._key;//INVALID.

Visibility in theSELECT list

Aliases in theSELECT list are visible only to the following clauses:

  • GROUP BY clause

Example:

Duplicate aliases

ASELECT list containing multiple explicit or implicit aliasesof the same name is allowed, as long as the alias name isn't referencedelsewhere in the query, since the reference would beambiguous.

Example:

SELECT1ASa,2ASa;/*---+---+ | a | a | +---+---+ | 1 | 2 | +---+---*/

Ambiguous aliases

GoogleSQL provides an error if accessing a name is ambiguous, meaningit can resolve to more than one unique object in the query or in a table schema,including the schema of a destination table.

The following query contains aliases that are ambiguous in theGROUP BY clausebecause they are duplicated in theSELECT list:

SELECTFirstNameASname,LastNameASname,FROMSingersGROUPBYname;

The following query contains aliases that are ambiguous in theSELECT list andFROM clause because they share a column and field with same name.

  • Assume thePerson table has three columns:FirstName,LastName, andPrimaryContact.
  • Assume thePrimaryContact column represents a struct with these fields:FirstName andLastName.

The aliasP is ambiguous and will produce an error becauseP.FirstName intheGROUP BY clause could refer to eitherPerson.FirstName orPerson.PrimaryContact.FirstName.

SELECTFirstName,LastName,PrimaryContactASPFROMPersonASPGROUPBYP.FirstName;

A name isnot ambiguous inGROUP BY,ORDER BY if it's botha column name and aSELECT list alias, as long as the name resolves to thesame underlying object.

Range variables

In GoogleSQL, a range variable is a table expression alias in theFROM clause. Sometimes a range variable is known as atable alias. Arange variable lets you reference rows being scanned from a table expression.A table expression represents an item in theFROM clause that returns a table.Common items that this expression can represent includetables.

In general, a range variable provides a reference to the rows of a tableexpression. A range variable can be used to qualify a column reference andunambiguously identify the related table, for examplerange_variable.column_1.

When referencing a range variable on its own without a specified column suffix,the result of a table expression is the row type of the related table.Value tables have explicit row types, so for range variables relatedto value tables, the result type is the value table's row type. Other tablesdon't have explicit row types, and for those tables, the range variabletype is a dynamically defined struct that includes all of thecolumns in the table.

Examples

The following example selects columnf1 from range variablebar,which in effect selects columnf1 from tableexample_table.

SELECTbar.f1FROMexample_tableASbar;

Appendix A: examples with sample data

These examples include statements which perform queries on theRoster andTeamMascot,andPlayerStats tables.

Sample table with column families

The following table calledexample_table is used to illustrate thebehavior of different query clauses in this reference:

/*-------------------------------------------------+ |              example_table                      | +-------------------------------------------------+ | column family name | column names (BYTES)       | +-------------------------------------------------+ | f1                 | { "c1", "c2" }             | | f2                 | { "col1", "col2", "col3" } | | f3                 | { "cl1" }                  | +-------------------------------------------------*/

When queried,example_table produces the following results:

/*-------------------------------------------------------------------------------+ | _key | f1               | f2                                 |    f3          | +-------------------------------------------------------------------------------+ | a#01 | {                | {                                  | {              | |      |   "c1": "xyz",   |   "col1": "def",                   |   "cl1": "{    | |      |   "c2": "nop"    |   "col2": "125",                   |     "n": "Def",| |      | }                |   "col3": "2008-12-25 15:30:00+00" |     "g": 40,   | |      |                  | }                                  |     "k": 3     | |      |                  |                                    |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------+ | a#02 | {                | {                                  | {              | |      |   "c1": "zyx",   |   "col1": "cba",                   |   "cl1": "{    | |      | }                |   "col2": "123",                   |     "n": "Gih",| |      |                  |   "col3": "2018-10-15 15:30:00+00" |     "g": 20,   | |      |                  | }                                  |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------+ | a#03 | {                | {                                  | {              | |      |   "c1": "abc",   |   "col1": "cba",                   |   "cl1": "{    | |      |   "c2": "def"    |   "col2": "543",                   |     "n": "Xyz",| |      | }                |   "col3": "2020-11-10 13:21:00+00" |     "g": 15,   | |      |                  | }                                  |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------+ | b#01 | {                | {                                  | NULL           | |      |   "c1": "jkl",   |   "col1": "qrs",                   |                | |      |   "c2": "mno"    |   "col2": "654",                   |                | |      | }                |   "col3": "2021-10-10 14:18:03+07" |                | |      |                  | }                                  |                | +-------------------------------------------------------------------------------+ | b#02 | {                | {                                  | NULL           | |      |   "c1": "hi",    |   "col1": "tu",                    |                | |      |   "c2": "no"     |   "col3": "2023-10-10 11:28:09+05",|                | |      | }                | }                                  |                | +-------------------------------------------------------------------------------+ | c#03 | {                | {                                  | {              | |      |   "c1": "j",     |   "col1": "u",                     |   "cl1": "{    | |      |   "c2": "l"      |  }                                 |     "n": "T",  | |      | }                | }                                  |     "g": 22,   | |      |                  |                                    |   }"           | |      |                  |                                    | }              | +-------------------------------------------------------------------------------*/

In the preceding results,f1,f2, andf3 each represent acolumn family, and_key represents a key assigned to a group ofcolumn families. Each column family contains its own columns. For example,for keya#01, the column familyf1 contains the columnsc1 andc2.c1contains the value"xyz" andc2 contains the value"nop".

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.