Cloud Spanner allows you to create STRUCT objects from data, as well as to use
STRUCT objects as bound parameters when running a SQL query with one of the
Cloud Spanner client libraries.
For more information about the STRUCT type in Cloud Spanner, see Data
types.
Declaring a user-defined type of STRUCT object
You can declare a STRUCT object in queries using the syntax described in
Declaring a STRUCT type.
You can define a type of STRUCT object as a sequence of field names and their
data types. You can then supply this type along with queries containing
STRUCT-typed parameter bindings and Cloud Spanner will use it to check that
the STRUCT parameter values in your query are valid.
C#
var nameType = new SpannerStruct {
{ "FirstName", SpannerDbType.String, null},
{ "LastName", SpannerDbType.String, null}
};
Go
type nameType struct {
FirstName string
LastName string
}
Java
Type nameType =
Type.struct(
Arrays.asList(
StructField.of("FirstName", Type.string()),
StructField.of("LastName", Type.string())));
Node.js
const nameType = {
type: 'struct',
fields: [
{
name: 'FirstName',
type: 'string',
},
{
name: 'LastName',
type: 'string',
},
],
};
PHP
$nameType = new ArrayType(
(new StructType)
->add('FirstName', Database::TYPE_STRING)
->add('LastName', Database::TYPE_STRING)
);
Python
name_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)])
Ruby
name_type = client.fields FirstName: :STRING, LastName: :STRING
Creating STRUCT objects
The following sample shows how to create STRUCT objects using the
Cloud Spanner client libraries.
C#
var nameStruct = new SpannerStruct
{
{ "FirstName", SpannerDbType.String, "Elena" },
{ "LastName", SpannerDbType.String, "Campbell" },
};
Go
type name struct {
FirstName string
LastName string
}
var singerInfo = name{"Elena", "Campbell"}
Java
Struct name =
Struct.newBuilder().set("FirstName").to("Elena").set("LastName").to("Campbell").build();
Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
const nameStruct = Spanner.struct({
FirstName: 'Elena',
LastName: 'Campbell',
});
PHP
$nameValue = (new StructValue)
->add('FirstName', 'Elena')
->add('LastName', 'Campbell');
$nameType = (new StructType)
->add('FirstName', Database::TYPE_STRING)
->add('LastName', Database::TYPE_STRING);
Python
record_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
record_value = ('Elena', 'Campbell')
Ruby
name_struct = { FirstName: "Elena", LastName: "Campbell" }
You can also use the client libraries to create an array of STRUCT objects, as
seen in the following sample:
C#
var bandMembers = new List<SpannerStruct>
{
new SpannerStruct { { "FirstName", SpannerDbType.String, "Elena" },
{ "LastName", SpannerDbType.String, "Campbell" } },
new SpannerStruct { { "FirstName", SpannerDbType.String, "Gabriel" },
{ "LastName", SpannerDbType.String, "Wright" } },
new SpannerStruct { { "FirstName", SpannerDbType.String, "Benjamin" },
{ "LastName", SpannerDbType.String, "Martinez" } },
};
Go
var bandMembers = []nameType{
{"Elena", "Campbell"},
{"Gabriel", "Wright"},
{"Benjamin", "Martinez"},
}
Java
List<Struct> bandMembers = new ArrayList<>();
bandMembers.add(
Struct.newBuilder().set("FirstName").to("Elena").set("LastName").to("Campbell").build());
bandMembers.add(
Struct.newBuilder().set("FirstName").to("Gabriel").set("LastName").to("Wright").build());
bandMembers.add(
Struct.newBuilder().set("FirstName").to("Benjamin").set("LastName").to("Martinez").build());
Node.js
const bandMembersType = {
type: 'array',
child: nameType,
};
const bandMembers = [
Spanner.struct({
FirstName: 'Elena',
LastName: 'Campbell',
}),
Spanner.struct({
FirstName: 'Gabriel',
LastName: 'Wright',
}),
Spanner.struct({
FirstName: 'Benjamin',
LastName: 'Martinez',
}),
];
PHP
$bandMembers = [
(new StructValue)
->add('FirstName', 'Elena')
->add('LastName', 'Campbell'),
(new StructValue)
->add('FirstName', 'Gabriel')
->add('LastName', 'Wright'),
(new StructValue)
->add('FirstName', 'Benjamin')
->add('LastName', 'Martinez')
];
Python
band_members = [("Elena", "Campbell"),
("Gabriel", "Wright"),
("Benjamin", "Martinez")]
Ruby
band_members = [name_type.struct(["Elena", "Campbell"]),
name_type.struct(["Gabriel", "Wright"]),
name_type.struct(["Benjamin", "Martinez"])]
Returning STRUCT objects in SQL query results
A Cloud Spanner SQL query can return an array of STRUCT objects as a column
for certain queries. For more information, see Using STRUCTS with SELECT.
Using STRUCT objects as bound parameters in SQL queries
You can use STRUCT objects as bound parameters in a SQL query. For more
information about parameters, see Query parameters.
Querying data with a STRUCT object
The following sample shows how to bind values in a STRUCT object to
parameters in a SQL query statement, execute the query, and output the results.
C#
string connectionString =
$"Data Source=projects/{projectId}/instances/"
+ $"{instanceId}/databases/{databaseId}";
using (var connection = new SpannerConnection(connectionString))
{
using (var cmd = connection.CreateSelectCommand(
"SELECT SingerId FROM Singers "
+ "WHERE STRUCT<FirstName STRING, LastName STRING>"
+ "(FirstName, LastName) = @name"))
{
cmd.Parameters.Add("name", nameStruct.GetSpannerDbType(), nameStruct);
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine(
reader.GetFieldValue<string>("SingerId"));
}
}
}
}
Go
stmt := spanner.Statement{
SQL: `SELECT SingerId FROM SINGERS
WHERE (FirstName, LastName) = @singerinfo`,
Params: map[string]interface{}{"singerinfo": singerInfo},
}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
var singerID int64
if err := row.Columns(&singerID); err != nil {
return err
}
fmt.Fprintf(w, "%d\n", singerID)
}
Java
Statement s =
Statement.newBuilder(
"SELECT SingerId FROM Singers "
+ "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+ "= @name")
.bind("name")
.to(name)
.build();
// We use a try-with-resource block to automatically release resources held by ResultSet.
try (ResultSet resultSet = dbClient.singleUse().executeQuery(s)) {
while (resultSet.next()) {
System.out.printf("%d\n", resultSet.getLong("SingerId"));
}
}
Node.js
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
const query = {
sql:
'SELECT SingerId FROM Singers WHERE ' +
'STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name',
params: {
name: nameStruct,
},
};
// Queries rows from the Singers table
try {
const [rows] = await database.run(query);
rows.forEach(row => {
const json = row.toJSON();
console.log(`SingerId: ${json.SingerId}`);
});
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
PHP
$results = $database->execute(
'SELECT SingerId FROM Singers ' .
'WHERE STRUCT<FirstName STRING, LastName STRING>' .
'(FirstName, LastName) = @name',
[
'parameters' => [
'name' => $nameValue
],
'types' => [
'name' => $nameType
]
]
);
foreach ($results as $row) {
printf('SingerId: %s' . PHP_EOL,
$row['SingerId']);
}
Python
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId FROM Singers WHERE "
"(FirstName, LastName) = @name",
params={'name': record_value},
param_types={'name': record_type})
for row in results:
print(u'SingerId: {}'.format(*row))
Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.execute(
"SELECT SingerId FROM Singers WHERE " +
"(FirstName, LastName) = @name",
params: { name: name_struct }
).rows.each do |row|
puts row[:SingerId].to_s
end
Querying data with an array of STRUCT objects
The following sample shows how to execute a query that uses an array of STRUCT
objects. Use the UNNEST operator to flatten an array of STRUCT objects
into rows:
C#
string connectionString =
$"Data Source=projects/{projectId}/instances/"
+ $"{instanceId}/databases/{databaseId}";
using (var connection = new SpannerConnection(connectionString))
{
using (var cmd = connection.CreateSelectCommand(
"SELECT SingerId FROM Singers "
+ "WHERE STRUCT<FirstName STRING, LastName STRING>"
+ "(FirstName, LastName) IN UNNEST(@names)"))
{
cmd.Parameters.Add("names",
SpannerDbType.ArrayOf(nameType.GetSpannerDbType()),
bandMembers);
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine(
reader.GetFieldValue<string>("SingerId"));
}
}
}
}
Go
stmt := spanner.Statement{
SQL: `SELECT SingerId FROM SINGERS
WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName)
IN UNNEST(@names)`,
Params: map[string]interface{}{"names": bandMembers},
}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
var singerID int64
if err := row.Columns(&singerID); err != nil {
return err
}
fmt.Fprintf(w, "%d\n", singerID)
}
Java
Statement s =
Statement.newBuilder(
"SELECT SingerId FROM Singers WHERE "
+ "STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+ "IN UNNEST(@names)")
.bind("names")
.toStructArray(nameType, bandMembers)
.build();
// We use a try-with-resource block to automatically release resources held by ResultSet.
try (ResultSet resultSet = dbClient.singleUse().executeQuery(s)) {
while (resultSet.next()) {
System.out.printf("%d\n", resultSet.getLong("SingerId"));
}
}
Node.js
const query = {
sql:
'SELECT SingerId FROM Singers ' +
'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) ' +
'IN UNNEST(@names)',
params: {
names: bandMembers,
},
types: {
names: bandMembersType,
},
};
// Queries rows from the Singers table
try {
const [rows] = await database.run(query);
rows.forEach(row => {
const json = row.toJSON();
console.log(`SingerId: ${json.SingerId}`);
});
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
PHP
$results = $database->execute(
'SELECT SingerId FROM Singers ' .
'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) ' .
'IN UNNEST(@names)',
[
'parameters' => [
'names' => $bandMembers
],
'types' => [
'names' => $nameType
]
]
);
foreach ($results as $row) {
printf('SingerId: %s' . PHP_EOL,
$row['SingerId']);
}
Python
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId FROM Singers WHERE "
"STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) IN UNNEST(@names)",
params={'names': band_members},
param_types={'names': param_types.Array(name_type)})
for row in results:
print(u'SingerId: {}'.format(*row))
Ruby
client.execute(
"SELECT SingerId FROM Singers WHERE " +
"STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) IN UNNEST(@names)",
params: { names: band_members }
).rows.each do |row|
puts row[:SingerId].to_s
end
Modifying data with DML
The following code example uses a STRUCT with bound parameters and Data
Manipulation Language (DML) to update a single value in rows that match the
WHERE clause condition. For rows where the FirstName is Timothy and the
LastName is Campbell, the LastName is updated to Grant.
C#
public static async Task UpdateUsingDmlWithStructCoreAsync(
string projectId,
string instanceId,
string databaseId)
{
var nameStruct = new SpannerStruct
{
{ "FirstName", SpannerDbType.String, "Timothy" },
{ "LastName", SpannerDbType.String, "Campbell" },
};
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection =
new SpannerConnection(connectionString))
{
await connection.OpenAsync();
SpannerCommand cmd = connection.CreateDmlCommand(
"UPDATE Singers SET LastName = 'Grant' "
+ "WHERE STRUCT<FirstName STRING, LastName STRING>"
+ "(FirstName, LastName) = @name");
cmd.Parameters.Add("name", nameStruct.GetSpannerDbType(), nameStruct);
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) updated...");
}
}
Go
func updateUsingDMLStruct(ctx context.Context, w io.Writer, client *spanner.Client) error {
_, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
type name struct {
FirstName string
LastName string
}
var singerInfo = name{"Timothy", "Campbell"}
stmt := spanner.Statement{
SQL: `Update Singers Set LastName = 'Grant'
WHERE STRUCT<FirstName String, LastName String>(Firstname, LastName) = @name`,
Params: map[string]interface{}{"name": singerInfo},
}
rowCount, err := txn.Update(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
return nil
})
return err
}
Java
static void updateUsingDmlWithStruct(DatabaseClient dbClient) {
Struct name =
Struct.newBuilder().set("FirstName").to("Timothy").set("LastName").to("Campbell").build();
Statement s =
Statement.newBuilder(
"UPDATE Singers SET LastName = 'Grant' "
+ "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+ "= @name")
.bind("name")
.to(name)
.build();
dbClient
.readWriteTransaction()
.run(
new TransactionCallable<Void>() {
@Override
public Void run(TransactionContext transaction) throws Exception {
long rowCount = transaction.executeUpdate(s);
System.out.printf("%d record updated.\n", rowCount);
return null;
}
});
}
Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
const nameStruct = Spanner.struct({
FirstName: 'Timothy',
LastName: 'Campbell',
});
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rowCount] = await transaction.runUpdate({
sql: `UPDATE Singers SET LastName = 'Grant'
WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name`,
params: {
name: nameStruct,
},
});
console.log(`Successfully updated ${rowCount} record.`);
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
PHP
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\Transaction;
use Google\Cloud\Spanner\StructType;
use Google\Cloud\Spanner\StructValue;
/**
* Update data with a DML statement using Structs.
*
* The database and table must already exist and can be created using
* `create_database`.
* Example:
* ```
* insert_data($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_data_with_dml_structs($instanceId, $databaseId)
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) use ($spanner) {
$nameValue = (new StructValue)
->add('FirstName', 'Timothy')
->add('LastName', 'Campbell');
$nameType = (new StructType)
->add('FirstName', Database::TYPE_STRING)
->add('LastName', Database::TYPE_STRING);
$rowCount = $t->executeUpdate(
"UPDATE Singers SET LastName = 'Grant' "
. "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
. "= @name",
[
'parameters' => [
'name' => $nameValue
],
'types' => [
'name' => $nameType
]
]);
$t->commit();
printf('Updated %d row(s).' . PHP_EOL, $rowCount);
});
}
Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
record_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
record_value = ('Timothy', 'Campbell')
def write_with_struct(transaction):
row_ct = transaction.execute_update(
"UPDATE Singers SET LastName = 'Grant' "
"WHERE STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) = @name",
params={'name': record_value},
param_types={'name': record_type}
)
print("{} record(s) updated.".format(row_ct))
database.run_in_transaction(write_with_struct)
Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
name_struct = { FirstName: "Timothy", LastName: "Campbell" }
client.transaction do |transaction|
row_count = transaction.execute_update(
"UPDATE Singers SET LastName = 'Grant'
WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name",
params: { name: name_struct }
)
end
puts "#{row_count} record updated."
Accessing STRUCT field values
You can access fields inside a STRUCT object by name.
C#
string connectionString =
$"Data Source=projects/{projectId}/instances/"
+ $"{instanceId}/databases/{databaseId}";
var structParam = new SpannerStruct
{
{ "FirstName", SpannerDbType.String, "Elena" },
{ "LastName", SpannerDbType.String, "Campbell" },
};
using (var connection = new SpannerConnection(connectionString))
{
using (var cmd = connection.CreateSelectCommand(
"SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName"))
{
cmd.Parameters.Add("name", structParam.GetSpannerDbType(), structParam);
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine(
reader.GetFieldValue<string>("SingerId"));
}
}
}
}
Go
func queryWithStructField(ctx context.Context, w io.Writer, client *spanner.Client) error {
type structParam struct {
FirstName string
LastName string
}
var singerInfo = structParam{"Elena", "Campbell"}
stmt := spanner.Statement{
SQL: `SELECT SingerId FROM SINGERS
WHERE FirstName = @name.FirstName`,
Params: map[string]interface{}{"name": singerInfo},
}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
var singerID int64
if err := row.Columns(&singerID); err != nil {
return err
}
fmt.Fprintf(w, "%d\n", singerID)
}
}
Java
static void queryStructField(DatabaseClient dbClient) {
Statement s =
Statement.newBuilder("SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName")
.bind("name")
.to(
Struct.newBuilder()
.set("FirstName")
.to("Elena")
.set("LastName")
.to("Campbell")
.build())
.build();
// We use a try-with-resource block to automatically release resources held by ResultSet.
try (ResultSet resultSet = dbClient.singleUse().executeQuery(s)) {
while (resultSet.next()) {
System.out.printf("%d\n", resultSet.getLong("SingerId"));
}
}
}
Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
const nameStruct = Spanner.struct({
FirstName: 'Elena',
LastName: 'Campbell',
});
const query = {
sql: 'SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName',
params: {
name: nameStruct,
},
};
// Queries rows from the Singers table
try {
const [rows] = await database.run(query);
rows.forEach(row => {
const json = row.toJSON();
console.log(`SingerId: ${json.SingerId}`);
});
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
PHP
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\StructType;
/**
* Queries sample data from the database using a struct field value.
* Example:
* ```
* query_data_with_struct_field($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function query_data_with_struct_field($instanceId, $databaseId)
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$nameType = (new StructType)
->add('FirstName', Database::TYPE_STRING)
->add('LastName', Database::TYPE_STRING);
$results = $database->execute(
'SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName',
[
'parameters' => [
'name' => [
'FirstName' => 'Elena',
'LastName' => 'Campbell'
]
],
'types' => [
'name' => $nameType
]
]
);
foreach ($results as $row) {
printf('SingerId: %s' . PHP_EOL,
$row['SingerId']);
}
}
Python
def query_struct_field(instance_id, database_id):
"""Query a table using field access on a STRUCT parameter. """
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
name_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId FROM Singers "
"WHERE FirstName = @name.FirstName",
params={'name': ("Elena", "Campbell")},
param_types={'name': name_type})
for row in results:
print(u'SingerId: {}'.format(*row))
def query_nested_struct_field(instance_id, database_id):
"""Query a table using nested field access on a STRUCT parameter. """
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
song_info_type = param_types.Struct([
param_types.StructField('SongName', param_types.STRING),
param_types.StructField(
'ArtistNames', param_types.Array(
param_types.Struct([
param_types.StructField(
'FirstName', param_types.STRING),
param_types.StructField(
'LastName', param_types.STRING)
])
)
)
])
song_info = ('Imagination', [('Elena', 'Campbell'), ('Hannah', 'Harris')])
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId, @song_info.SongName "
"FROM Singers WHERE "
"STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) "
"IN UNNEST(@song_info.ArtistNames)",
params={
'song_info': song_info
},
param_types={
'song_info': song_info_type
}
)
for row in results:
print(u'SingerId: {} SongName: {}'.format(*row))
def insert_data_with_dml(instance_id, database_id):
"""Inserts sample data into the given database using a DML statement. """
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
def insert_singers(transaction):
row_ct = transaction.execute_update(
"INSERT Singers (SingerId, FirstName, LastName) "
" VALUES (10, 'Virginia', 'Watson')"
)
print("{} record(s) inserted.".format(row_ct))
database.run_in_transaction(insert_singers)
def update_data_with_dml(instance_id, database_id):
"""Updates sample data from the database using a DML statement. """
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
def update_albums(transaction):
row_ct = transaction.execute_update(
"UPDATE Albums "
"SET MarketingBudget = MarketingBudget * 2 "
"WHERE SingerId = 1 and AlbumId = 1"
)
print("{} record(s) updated.".format(row_ct))
database.run_in_transaction(update_albums)
def delete_data_with_dml(instance_id, database_id):
"""Deletes sample data from the database using a DML statement. """
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
def delete_singers(transaction):
row_ct = transaction.execute_update(
"DELETE Singers WHERE FirstName = 'Alice'"
)
print("{} record(s) deleted.".format(row_ct))
database.run_in_transaction(delete_singers)
def update_data_with_dml_timestamp(instance_id, database_id):
"""Updates data with Timestamp from the database using a DML statement. """
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
def update_albums(transaction):
row_ct = transaction.execute_update(
"UPDATE Albums "
"SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() "
"WHERE SingerId = 1"
)
print("{} record(s) updated.".format(row_ct))
database.run_in_transaction(update_albums)
def dml_write_read_transaction(instance_id, database_id):
"""First inserts data then reads it from within a transaction using DML."""
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
def write_then_read(transaction):
# Insert record.
row_ct = transaction.execute_update(
"INSERT Singers (SingerId, FirstName, LastName) "
" VALUES (11, 'Timothy', 'Campbell')"
)
print("{} record(s) inserted.".format(row_ct))
# Read newly inserted record.
results = transaction.execute_sql(
"SELECT FirstName, LastName FROM Singers WHERE SingerId = 11"
)
for result in results:
print("FirstName: {}, LastName: {}".format(*result))
database.run_in_transaction(write_then_read)
def update_data_with_dml_struct(instance_id, database_id):
"""Updates data with a DML statement and STRUCT parameters. """
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
record_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
record_value = ('Timothy', 'Campbell')
def write_with_struct(transaction):
row_ct = transaction.execute_update(
"UPDATE Singers SET LastName = 'Grant' "
"WHERE STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) = @name",
params={'name': record_value},
param_types={'name': record_type}
)
print("{} record(s) updated.".format(row_ct))
database.run_in_transaction(write_with_struct)
def insert_with_dml(instance_id, database_id):
"""Inserts data with a DML statement into the database. """
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
def insert_singers(transaction):
row_ct = transaction.execute_update(
"INSERT Singers (SingerId, FirstName, LastName) VALUES "
"(12, 'Melissa', 'Garcia'), "
"(13, 'Russell', 'Morales'), "
"(14, 'Jacqueline', 'Long'), "
"(15, 'Dylan', 'Shaw')"
)
print("{} record(s) inserted.".format(row_ct))
database.run_in_transaction(insert_singers)
def query_data_with_parameter(instance_id, database_id):
"""Queries sample data from the database using SQL with a parameter."""
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId, FirstName, LastName FROM Singers "
"WHERE LastName = @lastName",
params={"lastName": "Garcia"},
param_types={"lastName": spanner.param_types.STRING})
for row in results:
print(u"SingerId: {}, FirstName: {}, LastName: {}".format(*row))
def write_with_dml_transaction(instance_id, database_id):
""" Transfers part of a marketing budget from one album to another. """
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
def transfer_budget(transaction):
# Transfer marketing budget from one album to another. Performed in a
# single transaction to ensure that the transfer is atomic.
second_album_result = transaction.execute_sql(
"SELECT MarketingBudget from Albums "
"WHERE SingerId = 2 and AlbumId = 2"
)
second_album_row = list(second_album_result)[0]
second_album_budget = second_album_row[0]
transfer_amount = 200000
# Transaction will only be committed if this condition still holds at
# the time of commit. Otherwise it will be aborted and the callable
# will be rerun by the client library
if second_album_budget >= transfer_amount:
first_album_result = transaction.execute_sql(
"SELECT MarketingBudget from Albums "
"WHERE SingerId = 1 and AlbumId = 1"
)
first_album_row = list(first_album_result)[0]
first_album_budget = first_album_row[0]
second_album_budget -= transfer_amount
first_album_budget += transfer_amount
# Update first album
transaction.execute_update(
"UPDATE Albums "
"SET MarketingBudget = @AlbumBudget "
"WHERE SingerId = 1 and AlbumId = 1",
params={"AlbumBudget": first_album_budget},
param_types={"AlbumBudget": spanner.param_types.INT64}
)
# Update second album
transaction.execute_update(
"UPDATE Albums "
"SET MarketingBudget = @AlbumBudget "
"WHERE SingerId = 2 and AlbumId = 2",
params={"AlbumBudget": second_album_budget},
param_types={"AlbumBudget": spanner.param_types.INT64}
)
print("Transferred {} from Album2's budget to Album1's".format(
transfer_amount))
database.run_in_transaction(transfer_budget)
def update_data_with_partitioned_dml(instance_id, database_id):
""" Update sample data with a partitioned DML statement. """
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
row_ct = database.execute_partitioned_dml(
"UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
)
print("{} records updated.".format(row_ct))
def delete_data_with_partitioned_dml(instance_id, database_id):
""" Delete sample data with a partitioned DML statement. """
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
row_ct = database.execute_partitioned_dml(
"DELETE Singers WHERE SingerId > 10"
)
print("{} record(s) deleted.".format(row_ct))
def update_with_batch_dml(instance_id, database_id):
"""Updates sample data in the database using Batch DML. """
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
insert_statement = (
"INSERT INTO Albums "
"(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
"VALUES (1, 3, 'Test Album Title', 10000)"
)
update_statement = (
"UPDATE Albums "
"SET MarketingBudget = MarketingBudget * 2 "
"WHERE SingerId = 1 and AlbumId = 3"
)
def update_albums(transaction):
row_cts = transaction.batch_update([
insert_statement,
update_statement,
])
print("Executed {} SQL statements using Batch DML.".format(
len(row_cts)))
database.run_in_transaction(update_albums)
if __name__ == '__main__': # noqa: C901
parser = argparse.ArgumentParser(
description=__doc__,
formatter_class=argparse.RawDescriptionHelpFormatter)
parser.add_argument(
'instance_id', help='Your Cloud Spanner instance ID.')
parser.add_argument(
'--database-id', help='Your Cloud Spanner database ID.',
default='example_db')
subparsers = parser.add_subparsers(dest='command')
subparsers.add_parser('create_database', help=create_database.__doc__)
subparsers.add_parser('delete_data', help=delete_data.__doc__)
subparsers.add_parser('insert_data', help=insert_data.__doc__)
subparsers.add_parser('query_data', help=query_data.__doc__)
subparsers.add_parser('read_data', help=read_data.__doc__)
subparsers.add_parser('read_stale_data', help=read_stale_data.__doc__)
subparsers.add_parser('add_column', help=add_column.__doc__)
subparsers.add_parser('update_data', help=update_data.__doc__)
subparsers.add_parser(
'query_data_with_new_column', help=query_data_with_new_column.__doc__)
subparsers.add_parser(
'read_write_transaction', help=read_write_transaction.__doc__)
subparsers.add_parser(
'read_only_transaction', help=read_only_transaction.__doc__)
subparsers.add_parser('add_index', help=add_index.__doc__)
query_data_with_index_parser = subparsers.add_parser(
'query_data_with_index', help=query_data_with_index.__doc__)
query_data_with_index_parser.add_argument(
'--start_title', default='Aardvark')
query_data_with_index_parser.add_argument(
'--end_title', default='Goo')
subparsers.add_parser('read_data_with_index', help=insert_data.__doc__)
subparsers.add_parser('add_storing_index', help=add_storing_index.__doc__)
subparsers.add_parser(
'read_data_with_storing_index', help=insert_data.__doc__)
subparsers.add_parser(
'create_table_with_timestamp',
help=create_table_with_timestamp.__doc__)
subparsers.add_parser(
'insert_data_with_timestamp', help=insert_data_with_timestamp.__doc__)
subparsers.add_parser(
'add_timestamp_column', help=add_timestamp_column.__doc__)
subparsers.add_parser(
'update_data_with_timestamp', help=update_data_with_timestamp.__doc__)
subparsers.add_parser(
'query_data_with_timestamp', help=query_data_with_timestamp.__doc__)
subparsers.add_parser('write_struct_data', help=write_struct_data.__doc__)
subparsers.add_parser('query_with_struct', help=query_with_struct.__doc__)
subparsers.add_parser(
'query_with_array_of_struct', help=query_with_array_of_struct.__doc__)
subparsers.add_parser(
'query_struct_field', help=query_struct_field.__doc__)
subparsers.add_parser(
'query_nested_struct_field', help=query_nested_struct_field.__doc__)
subparsers.add_parser(
'insert_data_with_dml', help=insert_data_with_dml.__doc__)
subparsers.add_parser(
'update_data_with_dml', help=update_data_with_dml.__doc__)
subparsers.add_parser(
'delete_data_with_dml', help=delete_data_with_dml.__doc__)
subparsers.add_parser(
'update_data_with_dml_timestamp',
help=update_data_with_dml_timestamp.__doc__)
subparsers.add_parser(
'dml_write_read_transaction',
help=dml_write_read_transaction.__doc__)
subparsers.add_parser(
'update_data_with_dml_struct',
help=update_data_with_dml_struct.__doc__)
subparsers.add_parser('insert_with_dml', help=insert_with_dml.__doc__)
subparsers.add_parser(
'query_data_with_parameter', help=query_data_with_parameter.__doc__)
subparsers.add_parser(
'write_with_dml_transaction', help=write_with_dml_transaction.__doc__)
subparsers.add_parser(
'update_data_with_partitioned_dml',
help=update_data_with_partitioned_dml.__doc__)
subparsers.add_parser(
'delete_data_with_partitioned_dml',
help=delete_data_with_partitioned_dml.__doc__)
subparsers.add_parser(
'update_with_batch_dml',
help=update_with_batch_dml.__doc__)
args = parser.parse_args()
if args.command == 'create_database':
create_database(args.instance_id, args.database_id)
elif args.command == 'insert_data':
insert_data(args.instance_id, args.database_id)
elif args.command == 'delete_data':
delete_data(args.instance_id, args.database_id)
elif args.command == 'query_data':
query_data(args.instance_id, args.database_id)
elif args.command == 'read_data':
read_data(args.instance_id, args.database_id)
elif args.command == 'read_stale_data':
read_stale_data(args.instance_id, args.database_id)
elif args.command == 'add_column':
add_column(args.instance_id, args.database_id)
elif args.command == 'update_data':
update_data(args.instance_id, args.database_id)
elif args.command == 'query_data_with_new_column':
query_data_with_new_column(args.instance_id, args.database_id)
elif args.command == 'read_write_transaction':
read_write_transaction(args.instance_id, args.database_id)
elif args.command == 'read_only_transaction':
read_only_transaction(args.instance_id, args.database_id)
elif args.command == 'add_index':
add_index(args.instance_id, args.database_id)
elif args.command == 'query_data_with_index':
query_data_with_index(
args.instance_id, args.database_id,
args.start_title, args.end_title)
elif args.command == 'read_data_with_index':
read_data_with_index(args.instance_id, args.database_id)
elif args.command == 'add_storing_index':
add_storing_index(args.instance_id, args.database_id)
elif args.command == 'read_data_with_storing_index':
read_data_with_storing_index(args.instance_id, args.database_id)
elif args.command == 'create_table_with_timestamp':
create_table_with_timestamp(args.instance_id, args.database_id)
elif args.command == 'insert_data_with_timestamp':
insert_data_with_timestamp(args.instance_id, args.database_id)
elif args.command == 'add_timestamp_column':
add_timestamp_column(args.instance_id, args.database_id)
elif args.command == 'update_data_with_timestamp':
update_data_with_timestamp(args.instance_id, args.database_id)
elif args.command == 'query_data_with_timestamp':
query_data_with_timestamp(args.instance_id, args.database_id)
elif args.command == 'write_struct_data':
write_struct_data(args.instance_id, args.database_id)
elif args.command == 'query_with_struct':
query_with_struct(args.instance_id, args.database_id)
elif args.command == 'query_with_array_of_struct':
query_with_array_of_struct(args.instance_id, args.database_id)
elif args.command == 'query_struct_field':
query_struct_field(args.instance_id, args.database_id)
elif args.command == 'query_nested_struct_field':
query_nested_struct_field(args.instance_id, args.database_id)
elif args.command == 'insert_data_with_dml':
insert_data_with_dml(args.instance_id, args.database_id)
elif args.command == 'update_data_with_dml':
update_data_with_dml(args.instance_id, args.database_id)
elif args.command == 'delete_data_with_dml':
delete_data_with_dml(args.instance_id, args.database_id)
elif args.command == 'update_data_with_dml_timestamp':
update_data_with_dml_timestamp(args.instance_id, args.database_id)
elif args.command == 'dml_write_read_transaction':
dml_write_read_transaction(args.instance_id, args.database_id)
elif args.command == 'update_data_with_dml_struct':
update_data_with_dml_struct(args.instance_id, args.database_id)
elif args.command == 'insert_with_dml':
insert_with_dml(args.instance_id, args.database_id)
elif args.command == 'query_data_with_parameter':
query_data_with_parameter(args.instance_id, args.database_id)
elif args.command == 'write_with_dml_transaction':
write_with_dml_transaction(args.instance_id, args.database_id)
elif args.command == 'update_data_with_partitioned_dml':
update_data_with_partitioned_dml(args.instance_id, args.database_id)
elif args.command == 'delete_data_with_partitioned_dml':
delete_data_with_partitioned_dml(args.instance_id, args.database_id)
elif args.command == 'update_with_batch_dml':
update_with_batch_dml(args.instance_id, args.database_id)
Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
name_struct = { FirstName: "Elena", LastName: "Campbell" }
client.execute(
"SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName",
params: { name: name_struct }
).rows.each do |row|
puts row[:SingerId].to_s
end
You can even have fields of STRUCT or ARRAY<STRUCT> type inside STRUCT
values and access them similarly:
C#
string connectionString =
$"Data Source=projects/{projectId}/instances/"
+ $"{instanceId}/databases/{databaseId}";
SpannerStruct name1 = new SpannerStruct
{
{ "FirstName", SpannerDbType.String, "Elena" },
{ "LastName", SpannerDbType.String, "Campbell" }
};
SpannerStruct name2 = new SpannerStruct
{
{ "FirstName", SpannerDbType.String, "Hannah" },
{ "LastName", SpannerDbType.String, "Harris" }
};
SpannerStruct songInfo = new SpannerStruct
{
{ "song_name", SpannerDbType.String, "Imagination" },
{ "artistNames", SpannerDbType.ArrayOf(name1.GetSpannerDbType()), new[] { name1, name2 } }
};
using (var connection = new SpannerConnection(connectionString))
{
using (var cmd = connection.CreateSelectCommand(
"SELECT SingerId, @song_info.song_name "
+ "FROM Singers WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+ "IN UNNEST(@song_info.artistNames)"))
{
cmd.Parameters.Add("song_info",
songInfo.GetSpannerDbType(),
songInfo);
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine(
reader.GetFieldValue<string>("SingerId"));
Console.WriteLine(
reader.GetFieldValue<string>(1));
}
}
}
}
Go
func queryWithNestedStructField(ctx context.Context, w io.Writer, client *spanner.Client) error {
type nameType struct {
FirstName string
LastName string
}
type songInfoStruct struct {
SongName string
ArtistNames []nameType
}
var songInfo = songInfoStruct{
SongName: "Imagination",
ArtistNames: []nameType{
{FirstName: "Elena", LastName: "Campbell"},
{FirstName: "Hannah", LastName: "Harris"},
},
}
stmt := spanner.Statement{
SQL: `SELECT SingerId, @songinfo.SongName FROM Singers
WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName)
IN UNNEST(@songinfo.ArtistNames)`,
Params: map[string]interface{}{"songinfo": songInfo},
}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
var singerID int64
var songName string
if err := row.Columns(&singerID, &songName); err != nil {
return err
}
fmt.Fprintf(w, "%d %s\n", singerID, songName)
}
}
Java
static void queryNestedStructField(DatabaseClient dbClient) {
Type nameType =
Type.struct(
Arrays.asList(
StructField.of("FirstName", Type.string()),
StructField.of("LastName", Type.string())));
Struct songInfo =
Struct.newBuilder()
.set("song_name")
.to("Imagination")
.set("artistNames")
.toStructArray(
nameType,
Arrays.asList(
Struct.newBuilder()
.set("FirstName")
.to("Elena")
.set("LastName")
.to("Campbell")
.build(),
Struct.newBuilder()
.set("FirstName")
.to("Hannah")
.set("LastName")
.to("Harris")
.build()))
.build();
Statement s =
Statement.newBuilder(
"SELECT SingerId, @song_info.song_name "
+ "FROM Singers WHERE "
+ "STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+ "IN UNNEST(@song_info.artistNames)")
.bind("song_info")
.to(songInfo)
.build();
// We use a try-with-resource block to automatically release resources held by ResultSet.
try (ResultSet resultSet = dbClient.singleUse().executeQuery(s)) {
while (resultSet.next()) {
System.out.printf("%d %s\n", resultSet.getLong("SingerId"), resultSet.getString(1));
}
}
}
Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
const nameType = {
type: 'struct',
fields: [
{
name: 'FirstName',
type: 'string',
},
{
name: 'LastName',
type: 'string',
},
],
};
// Creates Song info STRUCT with a nested ArtistNames array
const songInfoType = {
type: 'struct',
fields: [
{
name: 'SongName',
type: 'string',
},
{
name: 'ArtistNames',
type: 'array',
child: nameType,
},
],
};
const songInfoStruct = Spanner.struct({
SongName: 'Imagination',
ArtistNames: [
Spanner.struct({FirstName: 'Elena', LastName: 'Campbell'}),
Spanner.struct({FirstName: 'Hannah', LastName: 'Harris'}),
],
});
const query = {
sql:
'SELECT SingerId, @songInfo.SongName FROM Singers ' +
'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) ' +
'IN UNNEST(@songInfo.ArtistNames)',
params: {
songInfo: songInfoStruct,
},
types: {
songInfo: songInfoType,
},
};
// Queries rows from the Singers table
try {
const [rows] = await database.run(query);
rows.forEach(row => {
const json = row.toJSON();
console.log(`SingerId: ${json.SingerId}, SongName: ${json.SongName}`);
});
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
PHP
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\StructType;
use Google\Cloud\Spanner\StructValue;
use Google\Cloud\Spanner\ArrayType;
/**
* Queries sample data from the database using a nested struct field value.
* Example:
* ```
* query_data_with_nested_struct_field($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function query_data_with_nested_struct_field($instanceId, $databaseId)
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$nameType = new ArrayType(
(new StructType)
->add('FirstName', Database::TYPE_STRING)
->add('LastName', Database::TYPE_STRING)
);
$songInfoType = (new StructType)
->add('SongName', Database::TYPE_STRING)
->add('ArtistNames', $nameType);
$nameStructValue1 = (new StructValue)
->add('FirstName', 'Elena')
->add('LastName', 'Campbell');
$nameStructValue2 = (new StructValue)
->add('FirstName', 'Hannah')
->add('LastName', 'Harris');
$songInfoValues = (new StructValue)
->add('SongName', 'Imagination')
->add('ArtistNames', [$nameStructValue1, $nameStructValue2]);
$results = $database->execute(
'SELECT SingerId, @song_info.SongName FROM Singers ' .
'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) ' .
'IN UNNEST(@song_info.ArtistNames)',
[
'parameters' => [
'song_info' => $songInfoValues
],
'types' => [
'song_info' => $songInfoType
]
]
);
foreach ($results as $row) {
printf('SingerId: %s SongName: %s' . PHP_EOL,
$row['SingerId'], $row['SongName']);
}
}
Python
def query_nested_struct_field(instance_id, database_id):
"""Query a table using nested field access on a STRUCT parameter. """
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
song_info_type = param_types.Struct([
param_types.StructField('SongName', param_types.STRING),
param_types.StructField(
'ArtistNames', param_types.Array(
param_types.Struct([
param_types.StructField(
'FirstName', param_types.STRING),
param_types.StructField(
'LastName', param_types.STRING)
])
)
)
])
song_info = ('Imagination', [('Elena', 'Campbell'), ('Hannah', 'Harris')])
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId, @song_info.SongName "
"FROM Singers WHERE "
"STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) "
"IN UNNEST(@song_info.ArtistNames)",
params={
'song_info': song_info
},
param_types={
'song_info': song_info_type
}
)
for row in results:
print(u'SingerId: {} SongName: {}'.format(*row))
Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
name_type = client.fields FirstName: :STRING, LastName: :STRING
song_info_struct = {
SongName: "Imagination",
ArtistNames: [name_type.struct(["Elena", "Campbell"]), name_type.struct(["Hannah", "Harris"])]
}
client.execute(
"SELECT SingerId, @song_info.SongName " +
"FROM Singers WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) " +
"IN UNNEST(@song_info.ArtistNames)",
params: { song_info: song_info_struct }
).rows.each do |row|
puts (row[:SingerId]).to_s, (row[:SongName]).to_s
end
