使用 JSON 資料

本頁說明如何使用 Spanner 處理 JSON。

JSON 資料類型是一種半結構化資料類型，用於保存 JSON (JavaScript 物件表示法) 資料。JSON 格式的規格請參閱 RFC 7159。

對於稀疏資料，或結構定義寬鬆或會變更的資料，JSON 可用來補充關聯式結構定義。不過，查詢最佳化工具會依據關聯式模型，大規模有效率地篩選、聯結、彙整及排序。透過 JSON 進行查詢時，內建最佳化功能較少，可檢查及調整效能的輔助功能也較少。

規格

Spanner JSON 類型會儲存輸入 JSON 文件的正規化表示法。

  • JSON 最多可巢狀結構化 80 層。
  • 系統不會保留空白字元。
  • 不支援留言。如果交易或查詢包含註解，就會失敗。
  • JSON 物件的成員會依字典順序排序。
  • JSON 陣列元素會保留順序。
  • 如果 JSON 物件有重複的鍵，系統只會保留第一個。
  • 原始型別 (字串、布林值、數字和空值) 會保留型別和值。
    • 系統會完全保留字串類型的值。
    • 系統會保留數字類型的值，但可能會因正規化程序而變更文字表示法。舉例來說，輸入數字 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'。
  • 使用 TO_JSON()、JSON_OBJECT() 和 JSON_ARRAY() 函式，在 SQL 中建構 JSON 文件。這些函式會實作必要的引號和逸出字元。

標準化文件的大小上限為 10 MB。

是否可以為空值

JSON null 值會視為 SQL 非空值。

例如：

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 類型值可為可為空值。

CREATE TABLE Venues (
  VenueId   INT64 NOT NULL,
  VenueName  STRING(1024),
  VenueAddress STRING(1024),
  VenueFeatures JSON,
  DateOpened  DATE,
) PRIMARY KEY(VenueId);

在現有資料表中新增及移除 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 "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
)

// 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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。

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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。


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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。

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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。


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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。

// 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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。

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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。

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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。

# 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 資料建立索引

您可以搭配使用次要索引和搜尋索引與 JSON 資料，加快查詢 JSON 資料的速度。Spanner 不支援將 JSON 類型資料欄做為次要索引中的鍵。

使用次要索引

如果想根據 JSON 文件中的純量值進行篩選，次要索引就非常實用。如要在 JSON 中使用次要索引，請建立產生資料欄，擷取相關純量資料，並將資料轉換為適當的 SQL 類型。然後，您可以在這個產生的資料欄上建立次要索引。索引會加快對所產生資料欄執行的合格查詢。

在下列範例中，您會建立 VenuesByCapacity 索引，資料庫會使用該索引尋找容量大於 1000 的場地。Spanner 會使用索引找出相關資料列，而非檢查每個資料列，因此可提升查詢效能，尤其是大型資料表。

ALTER TABLE Venues
ADD COLUMN VenueCapacity INT64 AS (INT64(VenueDetails.capacity));

CREATE INDEX VenuesByCapacity ON Venue (VenueCapacity);

SELECT VenueName
FROM Venues
WHERE VenueCapacity > 1000;

使用搜尋索引

當您查詢動態或多樣的 JSON 文件時，搜尋索引就很有用。與次要索引不同，您可以針對儲存在 JSON 欄中的任何 JSON 文件建立搜尋索引。搜尋索引會自動適應 JSON 文件、不同資料列之間和一段時間內的變化。

在下列範例中，您會建立 VenuesByVenueDetails 搜尋索引，資料庫會使用該索引尋找具有特定詳細資料 (例如大小和營業時間) 的場地。Spanner 不會檢查每個資料列，而是使用索引找出相關資料列，藉此提升查詢效能，尤其是大型資料表。

ALTER TABLE Venues
ADD COLUMN VenueDetails_Tokens TOKENLIST AS (TOKENIZE_JSON(VenueDetails)) HIDDEN;

CREATE SEARCH INDEX VenuesByVenueDetails
ON Venue (VenueDetails_Tokens);

SELECT VenueName
FROM Venues
WHERE JSON_CONTAINS(VenueDetails, JSON '{"labels": ["large"], "open": {"Friday": true}}');

詳情請參閱「JSON 搜尋索引」。

查詢 JSON 資料

以下範例顯示如何使用 Spanner 用戶端程式庫查詢 JSON 資料。

C++

如要瞭解如何安裝及使用 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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。


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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。


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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。

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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。

// 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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。

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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。

# 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 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。

# 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

限制

  • 您無法在 ORDER BY 子句中使用 JSON 資料欄。
  • 您無法使用 JSON 類型資料欄做為主鍵，或做為次要索引中的鍵。詳情請參閱「為 JSON 資料建立索引」。

後續步驟