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, 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 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.

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