Working with STRUCT Objects

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();

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
database
  .run(query)
  .then(results => {
    const rows = results[0];

    rows.forEach(row => {
      const json = row.toJSON();
      console.log(`SingerId: ${json.SingerId}`);
    });
  })
  .catch(err => {
    console.error('ERROR:', err);
  })
  .then(() => {
    // Close the database when finished
    return 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]}"
  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();

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
database
  .run(query)
  .then(results => {
    const rows = results[0];

    rows.forEach(row => {
      const json = row.toJSON();
      console.log(`SingerId: ${json.SingerId}`);
    });
  })
  .catch(err => {
    console.error('ERROR:', err);
  })
  .then(() => {
    // Close the database when finished
    return 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]}"
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((err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  transaction
    .runUpdate({
      sql: `UPDATE Singers SET LastName = 'Grant'
      WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name`,
      params: {
        name: nameStruct,
      },
    })
    .then(rowCount => {
      console.log(`Successfully updated ${rowCount} record.`);
      return transaction.commit();
    })
    .catch(err => {
      console.error('ERROR:', err);
    })
    .then(() => {
      // Close the database when finished.
      return 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();

  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
database
  .run(query)
  .then(results => {
    const rows = results[0];

    rows.forEach(row => {
      const json = row.toJSON();
      console.log(`SingerId: ${json.SingerId}`);
    });
  })
  .catch(err => {
    console.error('ERROR:', err);
  })
  .then(() => {
    // Close the database when finished
    return 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 read_then_write(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(read_then_write)


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 write_with_dml_transaction(instance_id, database_id):
    """ Transfers 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.
        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]

        transfer_amount = 300000

        # 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 first_album_budget >= transfer_amount:
            second_album_result = transaction.execute_sql(
                "SELECT MarketingBudget from Albums "
                "WHERE SingerId = 1 and AlbumId = 1"
            )
            second_album_row = list(second_album_result)[0]
            second_album_budget = second_album_row[0]

            first_album_budget -= transfer_amount
            second_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 Album1's budget to Album2'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))


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('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(
        '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__)

    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 == '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 == '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)

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]}"
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();

  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
database
  .run(query)
  .then(results => {
    const rows = results[0];

    rows.forEach(row => {
      const json = row.toJSON();
      console.log(`SingerId: ${json.SingerId}, SongName: ${json.SongName}`);
    });
  })
  .catch(err => {
    console.error('ERROR:', err);
  })
  .then(() => {
    // Close the database when finished
    return 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]}", "#{row[:SongName]}"
end

Was this page helpful? Let us know how we did:

Send feedback about...

Cloud Spanner Documentation