Protocol buffer functions in GoogleSQL
Stay organized with collections
Save and categorize content based on your preferences.
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
isNULL
, it un-setsfield_path
or returns an error if the last component offield_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" } |
*-----------------------------------------------------------------------------*/