Interroger les données avec un tableau d'objets STRUCT

Interroger les données à l'aide d'un tableau d'objets STRUCT.

En savoir plus

Pour obtenir une documentation détaillée incluant cet exemple de code, consultez les articles suivants :

Utiliser des objets STRUCT

Exemple de code

C++

Pour savoir comment installer et utiliser la bibliothèque cliente pour Spanner, consultez la page Bibliothèques clientes Spanner.

Pour vous authentifier auprès de Spanner, configurez les Identifiants par défaut de l'application. Pour en savoir plus, consultez Configurer l'authentification pour un environnement de développement local.

void QueryDataWithArrayOfStruct(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  // Cloud Spanner STRUCT<> types with named fields are represented by
  // std::tuple<std::pair<std::string, T>...>, create an alias to make it easier
  // to follow this code.
  using SingerName = std::tuple<std::pair<std::string, std::string>,
                                std::pair<std::string, std::string>>;
  auto make_name = [](std::string first_name, std::string last_name) {
    return std::make_tuple(std::make_pair("FirstName", std::move(first_name)),
                           std::make_pair("LastName", std::move(last_name)));
  };
  std::vector<SingerName> singer_info{
      make_name("Elena", "Campbell"),
      make_name("Gabriel", "Wright"),
      make_name("Benjamin", "Martinez"),
  };

  auto rows = client.ExecuteQuery(spanner::SqlStatement(
      "SELECT SingerId FROM Singers"
      " WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName)"
      "    IN UNNEST(@names)",
      {{"names", spanner::Value(singer_info)}}));

  for (auto& row : spanner::StreamOf<std::tuple<std::int64_t>>(rows)) {
    if (!row) throw std::move(row).status();
    std::cout << "SingerId: " << std::get<0>(*row) << "\n";
  }
  std::cout << "Query completed for"
            << " [spanner_query_data_with_array_of_struct]\n";
}

C#

Pour savoir comment installer et utiliser la bibliothèque cliente pour Spanner, consultez la page Bibliothèques clientes Spanner.


using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QueryDataWithArrayOfStructAsyncSample
{
    public async Task<List<int>> QueryDataWithArrayOfStructAsync(string projectId, string instanceId, string databaseId)
    {
        var nameType = new SpannerStruct
        {
            { "FirstName", SpannerDbType.String, null},
            { "LastName", SpannerDbType.String, null}
        };

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

        var singerIds = new List<int>();
        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())
        {
            singerIds.Add(reader.GetFieldValue<int>("SingerId"));
        }
        return singerIds;
    }
}

Go

Pour savoir comment installer et utiliser la bibliothèque cliente pour Spanner, consultez la page Bibliothèques clientes Spanner.


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

Pour savoir comment installer et utiliser la bibliothèque cliente pour Spanner, consultez la page Bibliothèques clientes Spanner.

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

Pour savoir comment installer et utiliser la bibliothèque cliente pour Spanner, consultez la page Bibliothèques clientes Spanner.

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

Pour savoir comment installer et utiliser la bibliothèque cliente pour Spanner, consultez la page Bibliothèques clientes Spanner.

$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

Pour savoir comment installer et utiliser la bibliothèque cliente pour Spanner, consultez la page Bibliothèques clientes Spanner.

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("SingerId: {}".format(*row))

Ruby

Pour savoir comment installer et utiliser la bibliothèque cliente pour Spanner, consultez la page Bibliothèques clientes Spanner.

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

Étapes suivantes

Pour rechercher et filtrer des exemples de code pour d'autres produits Google Cloud, consultez l'exemple de navigateur Google Cloud.