Protocol buffer functions in GoogleSQL

GoogleSQL for Spanner supports the following protocol buffer functions.

Function list

Name Summary
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 using replace_fields, the following limitations apply:

  • If value is NULL, it un-sets field_path or returns an error if the last component of field_path is a required field.
  • Replacing subfields will succeed only if the message containing the field is set.
  • Replacing subfields of repeated field isn't allowed.
  • A repeated field can be replaced with an ARRAY value.

Return type

Type of proto_expression

Examples

The following example uses protocol buffer messages Book and BookDetails.

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

This statement replaces the values of the field title and subfield chapters of proto type Book. Note that field details must be set for the statement to 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 to NULL.

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 the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025年10月13日 UTC.