Trabaja con objetos STRUCT

Cloud Spanner te permite crear objetos STRUCT a partir de datos, así como usar objetos como parámetros vinculados cuando ejecutas una consulta de SQL con una de las bibliotecas cliente de Cloud Spanner.

Para obtener más información sobre el tipo STRUCT en Cloud Spanner, consulta la página sobre tipos de datos.

Declara un tipo de objeto STRUCT definido por el usuario

Puedes declarar un objeto STRUCT en las consultas con la sintaxis descrita en la sección sobre cómo declarar un tipo .

Puedes definir un tipo de objeto STRUCT como una secuencia de nombres de campo y sus tipos de datos. Luego, puedes proporcionar este tipo junto con consultas que contengan vinculaciones de parámetros de tipo STRUCT, y Cloud Spanner las usará para verificar que los valores del parámetro STRUCT en tu consulta sean válidos.

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

Crea objetos STRUCT

En el siguiente ejemplo, se muestra cómo crear objetos STRUCT con las bibliotecas cliente de Cloud Spanner.

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

También puedes usar las bibliotecas cliente para crear un arreglo de objetos STRUCT, como se muestra en el siguiente ejemplo:

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"])]

Muestra de objetos STRUCT en los resultados de consultas de SQL

Una consulta de SQL de Cloud Spanner puede mostrar un arreglo de objetos STRUCT como una columna para determinadas consultas. Para obtener más información, consulta la sección sobre cómo usar STRUCTS con SELECT.

Usa objetos STRUCT como parámetros vinculados en consultas de SQL

Puedes usar objetos STRUCT como parámetros vinculados en una consulta de SQL. Para obtener más información sobre los parámetros, lee Parámetros de consulta.

Consulta datos con un objeto STRUCT

En el siguiente ejemplo, se muestra cómo vincular valores en un objeto STRUCT con los parámetros de una instrucción de consulta de SQL, ejecutar la consulta y mostrar los resultados.

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

Consulta datos con un arreglo de objetos STRUCT

En el siguiente ejemplo, se muestra cómo ejecutar una consulta que usa un arreglo de objetos STRUCT. Usa el operador UNNEST para compactar un arreglo de objetos STRUCT en filas:

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) "
                + "ORDER BY SingerId DESC")
        .bind("names")
        .toStructArray(nameType, bandMembers)
        .build();
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) ' +
    'ORDER BY SingerId',
  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

Modifica datos con DML

En el siguiente ejemplo de código, se usa un STRUCT con parámetros vinculados y lenguaje de manipulación de datos (DML) para actualizar un solo valor en filas que coincidan con la condición de cláusula WHERE. Para las filas en las que FirstName es Timothy y LastName es Campbell, LastName se actualiza a 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."

Accede a los valores de los campos STRUCT

Puedes acceder a los campos dentro de un objeto STRUCT por nombre.

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

def create_table_with_datatypes(instance_id, database_id):
    """Creates a table with supported dataypes. """
    # 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)

    operation = database.update_ddl([
        """CREATE TABLE Venues (
            VenueId         INT64 NOT NULL,
            VenueName       STRING(100),
            VenueInfo       BYTES(MAX),
            Capacity        INT64,
            AvailableDates  ARRAY<DATE>,
            LastContactDate DATE,
            OutdoorVenue    BOOL,
            PopularityScore FLOAT64,
            LastUpdateTime  TIMESTAMP NOT NULL
            OPTIONS(allow_commit_timestamp=true)
        ) PRIMARY KEY (VenueId)"""
    ])

    print('Waiting for operation to complete...')
    operation.result()

    print('Created Venues table on database {} on instance {}'.format(
        database_id, instance_id))

def insert_datatypes_data(instance_id, database_id):
    """Inserts data with supported datatypes into a table. """
    # 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)

    exampleBytes1 = base64.b64encode(u'Hello World 1'.encode())
    exampleBytes2 = base64.b64encode(u'Hello World 2'.encode())
    exampleBytes3 = base64.b64encode(u'Hello World 3'.encode())
    available_dates1 = ['2020-12-01', '2020-12-02', '2020-12-03']
    available_dates2 = ['2020-11-01', '2020-11-05', '2020-11-15']
    available_dates3 = ['2020-10-01', '2020-10-07']
    with database.batch() as batch:
        batch.insert(
            table='Venues',
            columns=(
                'VenueId', 'VenueName', 'VenueInfo', 'Capacity',
                'AvailableDates', 'LastContactDate', 'OutdoorVenue',
                'PopularityScore', 'LastUpdateTime'),
            values=[
                (4, u'Venue 4', exampleBytes1, 1800, available_dates1,
                    '2018-09-02', False, 0.85543, spanner.COMMIT_TIMESTAMP),
                (19, u'Venue 19', exampleBytes2, 6300, available_dates2,
                    '2019-01-15', True, 0.98716, spanner.COMMIT_TIMESTAMP),
                (42, u'Venue 42', exampleBytes3, 3000, available_dates3,
                    '2018-10-01', False, 0.72598, spanner.COMMIT_TIMESTAMP)])

    print('Inserted data.')

def query_data_with_array(instance_id, database_id):
    """Queries sample data using SQL with an ARRAY 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)

    exampleArray = ['2020-10-01', '2020-11-01']
    param = {
        'available_dates': exampleArray
    }
    param_type = {
        'available_dates': param_types.Array(param_types.DATE)
    }

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            'SELECT VenueId, VenueName, AvailableDate FROM Venues v,'
            'UNNEST(v.AvailableDates) as AvailableDate '
            'WHERE AvailableDate in UNNEST(@available_dates)',
            params=param, param_types=param_type)

        for row in results:
            print(u"VenueId: {}, VenueName: {}, AvailableDate: {}".format(
                *row))

def query_data_with_bool(instance_id, database_id):
    """Queries sample data using SQL with a BOOL 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)

    exampleBool = True
    param = {
        'outdoor_venue': exampleBool
    }
    param_type = {
        'outdoor_venue': param_types.BOOL
    }

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            'SELECT VenueId, VenueName, OutdoorVenue FROM Venues '
            'WHERE OutdoorVenue = @outdoor_venue',
            params=param, param_types=param_type)

        for row in results:
            print(u"VenueId: {}, VenueName: {}, OutdoorVenue: {}".format(*row))

def query_data_with_bytes(instance_id, database_id):
    """Queries sample data using SQL with a BYTES 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)

    exampleBytes = base64.b64encode(u'Hello World 1'.encode())
    param = {
        'venue_info': exampleBytes
    }
    param_type = {
        'venue_info': param_types.BYTES
    }

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            'SELECT VenueId, VenueName FROM Venues '
            'WHERE VenueInfo = @venue_info',
            params=param, param_types=param_type)

        for row in results:
            print(u"VenueId: {}, VenueName: {}".format(*row))

def query_data_with_date(instance_id, database_id):
    """Queries sample data using SQL with a DATE 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)

    exampleDate = '2019-01-01'
    param = {
        'last_contact_date': exampleDate
    }
    param_type = {
        'last_contact_date': param_types.DATE
    }

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            'SELECT VenueId, VenueName, LastContactDate FROM Venues '
            'WHERE LastContactDate < @last_contact_date',
            params=param, param_types=param_type)

        for row in results:
            print(u"VenueId: {}, VenueName: {}, LastContactDate: {}".format(
                *row))

def query_data_with_float(instance_id, database_id):
    """Queries sample data using SQL with a FLOAT64 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)

    exampleFloat = 0.8
    param = {
        'popularity_score': exampleFloat
    }
    param_type = {
        'popularity_score': param_types.FLOAT64
    }

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            'SELECT VenueId, VenueName, PopularityScore FROM Venues '
            'WHERE PopularityScore > @popularity_score',
            params=param, param_types=param_type)

        for row in results:
            print(u"VenueId: {}, VenueName: {}, PopularityScore: {}".format(
                *row))

def query_data_with_int(instance_id, database_id):
    """Queries sample data using SQL with a INT64 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)

    exampleInt = 3000
    param = {
        'capacity': exampleInt
    }
    param_type = {
        'capacity': param_types.INT64
    }

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            'SELECT VenueId, VenueName, Capacity FROM Venues '
            'WHERE Capacity >= @capacity',
            params=param, param_types=param_type)

        for row in results:
            print(u"VenueId: {}, VenueName: {}, Capacity: {}".format(*row))

def query_data_with_string(instance_id, database_id):
    """Queries sample data using SQL with a STRING 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)

    exampleString = "Venue 42"
    param = {
        'venue_name': exampleString
    }
    param_type = {
        'venue_name': param_types.STRING
    }

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            'SELECT VenueId, VenueName FROM Venues '
            'WHERE VenueName = @venue_name',
            params=param, param_types=param_type)

        for row in results:
            print(u"VenueId: {}, VenueName: {}".format(*row))

def query_data_with_timestamp_parameter(instance_id, database_id):
    """Queries sample data using SQL with a TIMESTAMP 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)

    example_timestamp = datetime.datetime.utcnow().isoformat() + "Z"
    param = {
        'last_update_time': example_timestamp
    }
    param_type = {
        'last_update_time': param_types.TIMESTAMP
    }

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            'SELECT VenueId, VenueName, LastUpdateTime FROM Venues '
            'WHERE LastUpdateTime < @last_update_time',
            params=param, param_types=param_type)

        for row in results:
            print(u"VenueId: {}, VenueName: {}, LastUpdateTime: {}".format(
                *row))

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('delete_data', help=delete_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__)
    subparsers.add_parser(
        'create_table_with_datatypes',
        help=create_table_with_datatypes.__doc__)
    subparsers.add_parser(
        'insert_datatypes_data',
        help=insert_datatypes_data.__doc__)
    subparsers.add_parser(
        'query_data_with_array',
        help=query_data_with_array.__doc__)
    subparsers.add_parser(
        'query_data_with_bool',
        help=query_data_with_bool.__doc__)
    subparsers.add_parser(
        'query_data_with_bytes',
        help=query_data_with_bytes.__doc__)
    subparsers.add_parser(
        'query_data_with_date',
        help=query_data_with_date.__doc__)
    subparsers.add_parser(
        'query_data_with_float',
        help=query_data_with_float.__doc__)
    subparsers.add_parser(
        'query_data_with_int',
        help=query_data_with_int.__doc__)
    subparsers.add_parser(
        'query_data_with_string',
        help=query_data_with_string.__doc__)
    subparsers.add_parser(
        'query_data_with_timestamp_parameter',
        help=query_data_with_timestamp_parameter.__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)
    elif args.command == 'create_table_with_datatypes':
        create_table_with_datatypes(args.instance_id, args.database_id)
    elif args.command == 'insert_datatypes_data':
        insert_datatypes_data(args.instance_id, args.database_id)
    elif args.command == 'query_data_with_array':
        query_data_with_array(args.instance_id, args.database_id)
    elif args.command == 'query_data_with_bool':
        query_data_with_bool(args.instance_id, args.database_id)
    elif args.command == 'query_data_with_bytes':
        query_data_with_bytes(args.instance_id, args.database_id)
    elif args.command == 'query_data_with_date':
        query_data_with_date(args.instance_id, args.database_id)
    elif args.command == 'query_data_with_float':
        query_data_with_float(args.instance_id, args.database_id)
    elif args.command == 'query_data_with_int':
        query_data_with_int(args.instance_id, args.database_id)
    elif args.command == 'query_data_with_string':
        query_data_with_string(args.instance_id, args.database_id)
    elif args.command == 'query_data_with_timestamp_parameter':
        query_data_with_timestamp_parameter(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

Incluso puedes tener campos de tipo STRUCT o ARRAY<STRUCT> dentro de los valores y acceder a ellos de manera similar:

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