本页面介绍了如何通过 Spanner 处理 JSON。
JSON 数据类型是一种用于保存 JSON(JavaScript Object Notation,JavaScript 对象表示法)数据的半结构化数据类型。有关 JSON 格式的规范,请参阅 RFC 7159。
JSON 适合用于为数据稀疏或松散定义或更改架构的数据补充关系架构。但是,查询优化器依赖于关系模型来高效地进行大规模过滤、联接、聚合和排序。通过 JSON 进行查询所需的内置优化较少,并且检查和调整性能的能力也较少。
规范
Spanner JSON 类型存储输入 JSON 文档的标准化表示形式。
- JSON 最多可以嵌套 80 层。
- 不保留空格。
- 不支持注释。含有注释的事务或查询将会失败。
- JSON 对象的成员按字典顺序排序。
- JSON 数组元素将保留其顺序。
- 如果 JSON 对象具有重复的键,则仅保留第一个键。
- 基元类型(字符串、布尔值、数字和 null)保留其类型和值。
- 字符串类型值原样保留。
- 数字类型值会保留,但其文本表示法可能会因标准化过程而发生变化。例如,输入数字 10000 的标准化表示法可能为 1e+4。数字值保留语义如下所示:
- 保留 [INT64_MIN, INT64_MAX] 范围内的有符号整数。
- 保留 [0, UINT64_MAX] 范围内的无符号整数。
- 保留可以进行从字符串到双精度再到字符串的往返处理而不会出现精度损失的双精度值。如果双精度值无法以这种方式往返,则事务或查询会失败。
- 例如,
SELECT JSON '2.2412421353246235436'
失败。 - 起作用的解决方法是
PARSE_JSON('2.2412421353246235436', wide_number_mode=>'round')
,它会返回JSON '2.2412421353246237'
。
- 例如,
标准化文档的大小上限为 2621440 个 UTF-8 字符。
是否可为 NULL
JSON null
值被视为 SQL 非 NULL。
例如:
SELECT (JSON '{"a":null}').a IS NULL; -- Returns FALSE
SELECT (JSON '{"a":null}').b IS NULL; -- Returns TRUE
SELECT JSON_QUERY(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_QUERY(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL
编码
JSON 文档必须采用 UTF-8 编码。含有采用其他格式编码的 JSON 文档的事务或查询将返回错误。
创建包含 JSON 列的表
创建表时,可以向表添加 JSON 列。JSON 类型值可以为 null。
CREATE TABLE Venues (
VenueId INT64 NOT NULL,
VenueName STRING(1024),
VenueAddress STRING(1024),
VenueFeatures JSON,
DateOpened DATE,
) PRIMARY KEY(VenueId);
在现有表中添加和移除 JSON 列
您也可以向现有表添加 JSON 列和从现有表中删除 JSON 列。
ALTER TABLE Venues ADD COLUMN VenueDetails JSON;
ALTER TABLE Venues DROP COLUMN VenueDetails;
以下示例演示了如何使用 Spanner 客户端库将名为 VenueDetails
的 JSON
列添加到 Venues
表。
C++
void AddJsonColumn(google::cloud::spanner_admin::DatabaseAdminClient client,
std::string const& project_id,
std::string const& instance_id,
std::string const& database_id) {
google::cloud::spanner::Database database(project_id, instance_id,
database_id);
auto metadata = client
.UpdateDatabaseDdl(database.FullName(), {R"""(
ALTER TABLE Venues ADD COLUMN VenueDetails JSON)"""})
.get();
if (!metadata) throw std::move(metadata).status();
std::cout << "`Venues` table altered, new DDL:\n" << metadata->DebugString();
}
C#
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class AddJsonColumnAsyncSample
{
public async Task AddJsonColumnAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
string alterStatement = "ALTER TABLE Venues ADD COLUMN VenueDetails JSON";
using var connection = new SpannerConnection(connectionString);
using var updateCmd = connection.CreateDdlCommand(alterStatement);
await updateCmd.ExecuteNonQueryAsync();
Console.WriteLine("Added the VenueDetails column.");
}
}
Go
import (
"context"
"fmt"
"io"
"regexp"
database "cloud.google.com/go/spanner/admin/database/apiv1"
adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)
// addJsonColumn creates a column in the database of type JSON
func addJsonColumn(w io.Writer, db string) error {
// db = `projects/<project>/instances/<instance-id>/database/<database-id>`
matches := regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(db)
if matches == nil || len(matches) != 3 {
return fmt.Errorf("addJsonColumn: invalid database id %s", db)
}
ctx := context.Background()
adminClient, err := database.NewDatabaseAdminClient(ctx)
if err != nil {
return err
}
defer adminClient.Close()
op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
Database: db,
Statements: []string{
"ALTER TABLE Venues ADD COLUMN VenueDetails JSON",
},
})
if err != nil {
return err
}
if err := op.Wait(ctx); err != nil {
return err
}
fmt.Fprintf(w, "Added VenueDetails column\n")
return nil
}
Java
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.DatabaseName;
import java.util.concurrent.ExecutionException;
class AddJsonColumnSample {
static void addJsonColumn() throws InterruptedException, ExecutionException {
// TODO(developer): Replace these variables before running the sample.
String projectId = "my-project";
String instanceId = "my-instance";
String databaseId = "my-database";
addJsonColumn(projectId, instanceId, databaseId);
}
static void addJsonColumn(String projectId, String instanceId, String databaseId)
throws InterruptedException, ExecutionException {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService();
DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
// Wait for the operation to finish.
// This will throw an ExecutionException if the operation fails.
databaseAdminClient.updateDatabaseDdlAsync(
DatabaseName.of(projectId, instanceId, databaseId),
ImmutableList.of("ALTER TABLE Venues ADD COLUMN VenueDetails JSON")).get();
System.out.printf("Successfully added column `VenueDetails`%n");
}
}
}
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';
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
// creates a client
const spanner = new Spanner({
projectId: projectId,
});
const databaseAdminClient = spanner.getDatabaseAdminClient();
const request = ['ALTER TABLE Venues ADD COLUMN VenueDetails JSON'];
// Alter existing table to add a column.
const [operation] = await databaseAdminClient.updateDatabaseDdl({
database: databaseAdminClient.databasePath(
projectId,
instanceId,
databaseId
),
statements: request,
});
console.log(`Waiting for operation on ${databaseId} to complete...`);
await operation.promise();
console.log(
`Added VenueDetails column to Venues table in database ${databaseId}.`
);
PHP
use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\UpdateDatabaseDdlRequest;
/**
* Adds a JSON column to a table.
* Example:
* ```
* add_json_column($projectId, $instanceId, $databaseId);
* ```
*
* @param string $projectId The Google Cloud project ID.
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function add_json_column(string $projectId, string $instanceId, string $databaseId): void
{
$databaseAdminClient = new DatabaseAdminClient();
$databaseName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);
$request = new UpdateDatabaseDdlRequest([
'database' => $databaseName,
'statements' => ['ALTER TABLE Venues ADD COLUMN VenueDetails JSON']
]);
$operation = $databaseAdminClient->updateDatabaseDdl($request);
print('Waiting for operation to complete...' . PHP_EOL);
$operation->pollUntilComplete();
printf('Added VenueDetails as a JSON column in Venues table' . PHP_EOL);
}
Python
def add_json_column(instance_id, database_id):
"""Adds a new JSON column to the Venues table in the example database."""
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
from google.cloud.spanner_admin_database_v1.types import \
spanner_database_admin
spanner_client = spanner.Client()
database_admin_api = spanner_client.database_admin_api
request = spanner_database_admin.UpdateDatabaseDdlRequest(
database=database_admin_api.database_path(
spanner_client.project, instance_id, database_id
),
statements=["ALTER TABLE Venues ADD COLUMN VenueDetails JSON"],
)
operation = database_admin_api.update_database_ddl(request)
print("Waiting for operation to complete...")
operation.result(OPERATION_TIMEOUT_SECONDS)
print(
'Altered table "Venues" on database {} on instance {}.'.format(
database_id, instance_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"
require "google/cloud/spanner/admin/database"
db_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
database_path = db_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
statements = ["ALTER TABLE Venues ADD COLUMN VenueDetails JSON"]
job = db_admin_client.update_database_ddl database: database_path,
statements: statements
job.wait_until_done!
puts "Added VenueDetails column to Venues table in database #{database_id}"
修改 JSON 数据
以下示例展示了如何使用 Spanner 客户端库更新 JSON
数据。
C++
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
void UpdateDataWithJson(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
auto venue19_details = spanner::Json(R"""(
{"rating": 9, "open": true}
)"""); // object
auto venue4_details = spanner::Json(R"""(
[
{"name": "room 1", "open": true},
{"name": "room 2", "open": false}
]
)"""); // array
auto venue42_details = spanner::Json(R"""(
{
"name": null,
"open": {"Monday": true, "Tuesday": false},
"tags": ["large", "airy"]
}
)"""); // nested
auto update_venues =
spanner::UpdateMutationBuilder(
"Venues", {"VenueId", "VenueName", "VenueDetails", "LastUpdateTime"})
.EmplaceRow(19, "Venue 19", venue19_details,
spanner::CommitTimestamp())
.EmplaceRow(4, "Venue 4", venue4_details, spanner::CommitTimestamp())
.EmplaceRow(42, "Venue 42", venue42_details,
spanner::CommitTimestamp())
.Build();
auto commit_result = client.Commit(spanner::Mutations{update_venues});
if (!commit_result) throw std::move(commit_result).status();
std::cout << "Insert was successful [spanner_update_data_with_json_column]\n";
}
C#
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
using Google.Cloud.Spanner.Data;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
public class UpdateDataWithJsonAsyncSample
{
public class Venue
{
public int VenueId { get; set; }
public string VenueDetails { get; set; }
}
public async Task UpdateDataWithJsonAsync(string projectId, string instanceId, string databaseId)
{
List<Venue> venues = new List<Venue>
{
// If you are using .NET Core 3.1 or later, you can use System.Text.Json for serialization instead.
new Venue
{
VenueId = 19,
VenueDetails = JsonConvert.SerializeObject(new
{
rating = 9,
open = true,
})
},
new Venue
{
VenueId = 4,
VenueDetails = JsonConvert.SerializeObject(new object[]
{
new
{
name = "room 1",
open = true,
},
new
{
name = "room 2",
open = false,
},
})
},
new Venue
{
VenueId = 42,
VenueDetails = JsonConvert.SerializeObject(new
{
name = "Central Park",
open = new
{
Monday = true,
Tuesday = false,
},
tags = new string[] {"large", "airy" },
}),
},
};
// Create connection to Cloud Spanner.
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
await Task.WhenAll(venues.Select(venue =>
{
// Update rows in the Venues table.
using var cmd = connection.CreateUpdateCommand("Venues", new SpannerParameterCollection
{
{ "VenueId", SpannerDbType.Int64, venue.VenueId },
{ "VenueDetails", SpannerDbType.Json, venue.VenueDetails }
});
return cmd.ExecuteNonQueryAsync();
}));
Console.WriteLine("Data updated.");
}
}
Go
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
import (
"context"
"fmt"
"io"
"regexp"
"cloud.google.com/go/spanner"
)
// updateDataWithJsonColumn updates database with Json type values
func updateDataWithJsonColumn(w io.Writer, db string) error {
// db = `projects/<project>/instances/<instance-id>/database/<database-id>`
matches := regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(db)
if matches == nil || len(matches) != 3 {
return fmt.Errorf("addJsonColumn: invalid database id %s", db)
}
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
type VenueDetails struct {
Name spanner.NullString `json:"name"`
Rating spanner.NullFloat64 `json:"rating"`
Open interface{} `json:"open"`
Tags []spanner.NullString `json:"tags"`
}
details_1 := spanner.NullJSON{Value: []VenueDetails{
{Name: spanner.NullString{StringVal: "room1", Valid: true}, Open: true},
{Name: spanner.NullString{StringVal: "room2", Valid: true}, Open: false},
}, Valid: true}
details_2 := spanner.NullJSON{Value: VenueDetails{
Rating: spanner.NullFloat64{Float64: 9, Valid: true},
Open: true,
}, Valid: true}
details_3 := spanner.NullJSON{Value: VenueDetails{
Name: spanner.NullString{Valid: false},
Open: map[string]bool{"monday": true, "tuesday": false},
Tags: []spanner.NullString{{StringVal: "large", Valid: true}, {StringVal: "airy", Valid: true}},
}, Valid: true}
cols := []string{"VenueId", "VenueDetails"}
_, err = client.Apply(ctx, []*spanner.Mutation{
spanner.Update("Venues", cols, []interface{}{4, details_1}),
spanner.Update("Venues", cols, []interface{}{19, details_2}),
spanner.Update("Venues", cols, []interface{}{42, details_3}),
})
if err != nil {
return err
}
fmt.Fprintf(w, "Updated data to VenueDetails column\n")
return nil
}
Java
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.Mutation;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Value;
import com.google.common.collect.ImmutableList;
class UpdateJsonDataSample {
static void updateJsonData() {
// TODO(developer): Replace these variables before running the sample.
String projectId = "my-project";
String instanceId = "my-instance";
String databaseId = "my-database";
try (Spanner spanner =
SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
DatabaseClient client =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
updateJsonData(client);
}
}
static void updateJsonData(DatabaseClient client) {
client.write(
ImmutableList.of(
Mutation.newInsertOrUpdateBuilder("Venues")
.set("VenueId")
.to(4L)
.set("VenueDetails")
.to(
Value.json(
"[{\"name\":\"room 1\",\"open\":true},"
+ "{\"name\":\"room 2\",\"open\":false}]"))
.build(),
Mutation.newInsertOrUpdateBuilder("Venues")
.set("VenueId")
.to(19L)
.set("VenueDetails")
.to(Value.json("{\"rating\":9,\"open\":true}"))
.build(),
Mutation.newInsertOrUpdateBuilder("Venues")
.set("VenueId")
.to(42L)
.set("VenueDetails")
.to(
Value.json(
"{\"name\":null,"
+ "\"open\":{\"Monday\":true,\"Tuesday\":false},"
+ "\"tags\":[\"large\",\"airy\"]}"))
.build()));
System.out.println("Venues successfully updated");
}
}
Node.js
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
// 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);
// Instantiate Spanner table objects.
const venuesTable = database.table('Venues');
const data = [
{
VenueId: '19',
VenueDetails: {rating: 9, open: true},
LastUpdateTime: 'spanner.commit_timestamp()',
},
{
VenueId: '4',
// VenueDetails must be specified as a string, as it contains a top-level
// array of objects that should be inserted into a JSON column. If we were
// to specify this value as an array instead of a string, the client
// library would encode this value as ARRAY<JSON> instead of JSON.
VenueDetails: `[
{
"name": null,
"open": true
},
{
"name": "room 2",
"open": false
},
{
"main hall": {
"description": "this is the biggest space",
"size": 200
}
}
]`,
LastUpdateTime: 'spanner.commit_timestamp()',
},
{
VenueId: '42',
VenueDetails: {
name: null,
open: {
Monday: true,
Tuesday: false,
},
tags: ['large', 'airy'],
},
LastUpdateTime: 'spanner.commit_timestamp()',
},
];
// Updates rows in the Venues table.
try {
await venuesTable.update(data);
console.log('Updated data.');
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
PHP
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
use Google\Cloud\Spanner\SpannerClient;
/**
* Updates sample data in a table with a JSON column.
*
* Before executing this method, a new column Revenue has to be added to the Venues
* table by applying the DDL statement "ALTER TABLE Venues ADD COLUMN VenueDetails JSON".
*
* Example:
* ```
* update_data_with_json_column($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_data_with_json_column(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->transaction(['singleUse' => true])
->updateBatch('Venues', [
[
'VenueId' => 4,
'VenueDetails' =>
'[{"name":"room 1","open":true},{"name":"room 2","open":false}]'
],
[
'VenueId' => 19,
'VenueDetails' => '{"rating":9,"open":true}'
],
[
'VenueId' => 42,
'VenueDetails' =>
'{"name":null,"open":{"Monday":true,"Tuesday":false},"tags":["large","airy"]}'
],
])
->commit();
print('Updated data.' . PHP_EOL);
}
Python
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
def update_data_with_json(instance_id, database_id):
"""Updates Venues tables in the database with the JSON
column.
This updates the `VenueDetails` column which must be created before
running this sample. You can add the column by running the
`add_json_column` sample or by running this DDL statement
against your database:
ALTER TABLE Venues ADD COLUMN VenueDetails JSON
"""
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.batch() as batch:
batch.update(
table="Venues",
columns=("VenueId", "VenueDetails"),
values=[
(
4,
JsonObject(
[
JsonObject({"name": "room 1", "open": True}),
JsonObject({"name": "room 2", "open": False}),
]
),
),
(19, JsonObject(rating=9, open=True)),
(
42,
JsonObject(
{
"name": None,
"open": {"Monday": True, "Tuesday": False},
"tags": ["large", "airy"],
}
),
),
],
)
print("Updated data.")
Ruby
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
# 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
rows = [{
VenueId: 1,
VenueDetails: { rating: 9, open: true }
}]
client.update "Venues", rows
# VenueDetails must be specified as a string, as it contains a top-level
# array of objects that should be inserted into a JSON column. If we were
# to specify this value as an array instead of a string, the client
# library would encode this value as ARRAY<JSON> instead of JSON.
venue_details_string = [
{
name: "room 1",
open: true
},
{
name: "room 2",
open: false
}
].to_json
rows = [{
VenueId: 2,
VenueDetails: venue_details_string
}]
client.update "Venues", rows
puts "Rows are updated."
查询 JSON 数据
以下示例展示了如何使用 Spanner 客户端库查询 JSON
数据。
C++
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
void QueryWithJsonParameter(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
auto rating9_details = spanner::Json(R"""(
{"rating": 9}
)"""); // object
spanner::SqlStatement select(
"SELECT VenueId, VenueDetails"
" FROM Venues"
" WHERE JSON_VALUE(VenueDetails, '$.rating') ="
" JSON_VALUE(@details, '$.rating')",
{{"details", spanner::Value(std::move(rating9_details))}});
using RowType = std::tuple<std::int64_t, absl::optional<spanner::Json>>;
auto rows = client.ExecuteQuery(std::move(select));
for (auto& row : spanner::StreamOf<RowType>(rows)) {
if (!row) throw std::move(row).status();
std::cout << "VenueId: " << std::get<0>(*row) << ", ";
auto venue_details = std::get<1>(*row).value();
std::cout << "VenueDetails: " << venue_details << "\n";
}
}
C#
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
using Google.Cloud.Spanner.Data;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Threading.Tasks;
public class QueryDataWithJsonParameterAsyncSample
{
public class Venue
{
public int VenueId { get; set; }
public string VenueDetails { get; set; }
}
public async Task<List<Venue>> QueryDataWithJsonParameterAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
// If you are using .NET Core 3.1 or later, you can use System.Text.Json for serialization instead.
var jsonValue = JsonConvert.SerializeObject(new { rating = 9 });
// Get all venues with rating 9.
using var cmd = connection.CreateSelectCommand(
@"SELECT VenueId, VenueDetails
FROM Venues
WHERE JSON_VALUE(VenueDetails, '$.rating') = JSON_VALUE(@details, '$.rating')",
new SpannerParameterCollection
{
{ "details", SpannerDbType.Json, jsonValue }
});
var venues = new List<Venue>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
venues.Add(new Venue
{
VenueId = reader.GetFieldValue<int>("VenueId"),
VenueDetails = reader.GetFieldValue<string>("VenueDetails")
});
}
return venues;
}
}
Go
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
import (
"context"
"fmt"
"io"
"regexp"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
// queryWithJsonParameter queries data on the JSON type column of the database
func queryWithJsonParameter(w io.Writer, db string) error {
// db = `projects/<project>/instances/<instance-id>/database/<database-id>`
matches := regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(db)
if matches == nil || len(matches) != 3 {
return fmt.Errorf("addJsonColumn: invalid database id %s", db)
}
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
type VenueDetails struct {
Name spanner.NullString `json:"name"`
Rating spanner.NullFloat64 `json:"rating"`
Open interface{} `json:"open"`
Tags []spanner.NullString `json:"tags"`
}
stmt := spanner.Statement{
SQL: `SELECT VenueId, VenueDetails FROM Venues WHERE JSON_VALUE(VenueDetails, '$.rating') = JSON_VALUE(@details, '$.rating')`,
Params: map[string]interface{}{
"details": spanner.NullJSON{Value: VenueDetails{
Rating: spanner.NullFloat64{Float64: 9, Valid: true},
}, Valid: true},
},
}
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 venueID int64
var venueDetails spanner.NullJSON
if err := row.Columns(&venueID, &venueDetails); err != nil {
return err
}
fmt.Fprintf(w, "The venue details for venue id %v is %v\n", venueID, venueDetails)
}
}
Java
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
import com.google.cloud.spanner.Value;
class QueryWithJsonParameterSample {
static void queryWithJsonParameter() {
// TODO(developer): Replace these variables before running the sample.
String projectId = "my-project";
String instanceId = "my-instance";
String databaseId = "my-database";
try (Spanner spanner =
SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
DatabaseClient client =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
queryWithJsonParameter(client);
}
}
static void queryWithJsonParameter(DatabaseClient client) {
String exampleJson = "{\"rating\": 9}";
Statement statement =
Statement.newBuilder(
"SELECT VenueId, VenueDetails\n"
+ "FROM Venues\n"
+ "WHERE JSON_VALUE(VenueDetails, '$.rating') = "
+ "JSON_VALUE(@details, '$.rating')")
.bind("details")
.to(Value.json(exampleJson))
.build();
try (ResultSet resultSet = client.singleUse().executeQuery(statement)) {
while (resultSet.next()) {
System.out.printf(
"VenueId: %s, VenueDetails: %s%n",
resultSet.getLong("VenueId"), resultSet.getJson("VenueDetails"));
}
}
}
}
Node.js
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
// 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 fieldType = {
type: 'json',
};
const jsonValue = {rating: 9};
const query = {
sql: `SELECT VenueId, VenueDetails FROM Venues
WHERE JSON_VALUE(VenueDetails, '$.rating') = JSON_VALUE(@details, '$.rating')`,
params: {
details: jsonValue,
},
types: {
details: fieldType,
},
};
// Queries rows from the Venues table.
try {
const [rows] = await database.run(query);
rows.forEach(row => {
const json = row.toJSON();
console.log(
`VenueId: ${json.VenueId}, Details: ${JSON.stringify(
json.VenueDetails
)}`
);
});
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
PHP
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\SpannerClient;
/**
* Queries sample data from the database using SQL with a NUMERIC parameter.
* Example:
* ```
* query_data_with_json_parameter($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function query_data_with_json_parameter(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$exampleJson = [
'rating' => 9,
'open' => true,
];
$results = $database->execute(
'SELECT VenueId, VenueDetails FROM Venues ' .
'WHERE JSON_VALUE(VenueDetails, \'$.rating\') = JSON_VALUE(@venueDetails, \'$.rating\')',
[
'parameters' => [
'venueDetails' => json_encode($exampleJson)
],
'types' => [
'venueDetails' => Database::TYPE_JSON
]
]
);
foreach ($results as $row) {
printf(
'VenueId: %s, VenueDetails: %s' . PHP_EOL,
$row['VenueId'],
$row['VenueDetails']
);
}
}
Python
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
# 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_json = json.dumps({"rating": 9})
param = {"details": example_json}
param_type = {"details": param_types.JSON}
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT VenueId, VenueDetails "
"FROM Venues "
"WHERE JSON_VALUE(VenueDetails, '$.rating') = "
"JSON_VALUE(@details, '$.rating')",
params=param,
param_types=param_type,
)
for row in results:
print("VenueId: {}, VenueDetails: {}".format(*row))
Ruby
如需了解如何安装和使用 Spanner 客户端库,请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
# 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
query = "SELECT VenueId, VenueDetails FROM Venues
WHERE JSON_VALUE(VenueDetails, '$.rating') = JSON_VALUE(@details, '$.rating')"
result = client.execute_query query,
params: { details: { rating: 9 } },
types: { details: :JSON }
result.rows.each do |row|
puts "VenueId: #{row['VenueId']}, VenueDetails: #{row['VenueDetails']}"
end
限制
- JSON 列不能在 ORDER BY 中使用。
- JSON 类型列不支持编入索引。但是,可以针对从 JSON 元素中提取标量值的生成的列创建索引。
在以下示例中,在生成的列 Details
上创建了索引 VenueMisc
,该列从 JSON 元素 VenueDetails
中提取标量值。可修改的 json_path 是一个采用 JSONPath 格式的 STRING 值。
CREATE TABLE Venues (
VenueId INT64 NOT NULL,
VenueName STRING(1024),
VenueAddress STRING(1024),
DateOpened DATE,
VenueDetails JSON,
Details STRING(MAX) AS (JSON_VALUE(VenueDetails, json_path)) STORED
) PRIMARY KEY(VenueId);
CREATE INDEX VenueMisc ON Venues(Details);