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" } |
 +-----------------------------------------------------------------------------*/

Protocol buffer functions in GoogleSQL Stay organized with collections Save and categorize content based on your preferences.

Function list

REPLACE_FIELDS

Protocol buffer functions in GoogleSQL

`REPLACE_FIELDS`