Protocol buffer functions in GoogleSQL

GoogleSQL for Spanner supports the following protocol buffer functions.

Function list

NameSummary
REPLACE_FIELDS Replaces the values in one or more protocol buffer fields.

REPLACE_FIELDS

REPLACE_FIELDS(proto_expression,valueASfield_path[,...])

Description

Returns a copy of a protocol buffer, replacing the values in one or more fields.field_path is a delimited path to the protocol buffer field that's replaced.When usingreplace_fields, the following limitations apply:

  • Ifvalue isNULL, it un-setsfield_path or returns an error if thelast component offield_path is a required field.
  • Replacing subfields will succeed only if the message containing the field isset.
  • Replacing subfields of repeated field isn't allowed.
  • A repeated field can be replaced with anARRAY value.

Return type

Type ofproto_expression

Examples

The following example uses protocol buffer messagesBook andBookDetails.

messageBook{requiredstringtitle=1;repeatedstringreviews=2;optionalBookDetailsdetails=3;};messageBookDetails{optionalstringauthor=1;optionalint32chapters=2;};

This statement replaces the values of the fieldtitle and subfieldchaptersof proto typeBook. Note that fielddetails must be set for the statementto succeed.

SELECTREPLACE_FIELDS(NEWBook("The Hummingbird"AStitle,NEWBookDetails(10ASchapters)ASdetails),"The Hummingbird II"AStitle,11ASdetails.chapters)ASproto;/*-----------------------------------------------------------------------------+ | proto                                                                       | +-----------------------------------------------------------------------------+ |{title: "The Hummingbird II" details: {chapters: 11 }}                       | +-----------------------------------------------------------------------------*/

The function can replace value of repeated fields.

SELECTREPLACE_FIELDS(NEWBook("The Hummingbird"AStitle,NEWBookDetails(10ASchapters)ASdetails),["A good read!","Highly recommended."]ASreviews)ASproto;/*-----------------------------------------------------------------------------+ | proto                                                                       | +-----------------------------------------------------------------------------+ |{title: "The Hummingbird" review: "A good read" review: "Highly recommended."| | details: {chapters: 10 }}                                                   | +-----------------------------------------------------------------------------*/

The function can also set a field toNULL.

SELECTREPLACE_FIELDS(NEWBook("The Hummingbird"AStitle,NEWBookDetails(10ASchapters)ASdetails),NULLASdetails)ASproto;/*-----------------------------------------------------------------------------+ | proto                                                                       | +-----------------------------------------------------------------------------+ |{title: "The Hummingbird" }                                                  | +-----------------------------------------------------------------------------*/

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.