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's replaced.
When using replace_fields
, the following limitations apply:
- If
valueisNULL, it un-setsfield_pathor returns an error if the last component offield_pathis 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
ARRAYvalue.
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" } |
*-----------------------------------------------------------------------------*/
