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, value AS field_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 is 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
.
message Book {
required string title = 1;
repeated string reviews = 2;
optional BookDetails details = 3;
};
message BookDetails {
optional string author = 1;
optional int32 chapters = 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.
SELECT REPLACE_FIELDS(
NEW Book(
"The Hummingbird" AS title,
NEW BookDetails(10 AS chapters) AS details),
"The Hummingbird II" AS title,
11 AS details.chapters)
AS proto;
/*-----------------------------------------------------------------------------*
| proto |
+-----------------------------------------------------------------------------+
|{title: "The Hummingbird II" details: {chapters: 11 }} |
*-----------------------------------------------------------------------------*/
The function can replace value of repeated fields.
SELECT REPLACE_FIELDS(
NEW Book("The Hummingbird" AS title,
NEW BookDetails(10 AS chapters) AS details),
["A good read!", "Highly recommended."] AS reviews)
AS proto;
/*-----------------------------------------------------------------------------*
| proto |
+-----------------------------------------------------------------------------+
|{title: "The Hummingbird" review: "A good read" review: "Highly recommended."|
| details: {chapters: 10 }} |
*-----------------------------------------------------------------------------*/
The function can also set a field to NULL
.
SELECT REPLACE_FIELDS(
NEW Book("The Hummingbird" AS title,
NEW BookDetails(10 AS chapters) AS details),
NULL AS details)
AS proto;
/*-----------------------------------------------------------------------------*
| proto |
+-----------------------------------------------------------------------------+
|{title: "The Hummingbird" } |
*-----------------------------------------------------------------------------*/