JSON データの操作

PostgreSQL Interface に関する注: PostgreSQL 言語データベースで、JSON データ型はサポートされていません。PostgreSQL 言語データベースには、JSONB データ型を使用します。

このページでは、Spanner で JSON を操作する方法について説明します。

JSON データ型は、JSON（JavaScript Object Notation）データを保持するために使用される半構造化データタイプです。JSON 形式の仕様については、RFC 7159 をご覧ください。

JSON は、スパースであるか、大まかに定義されているか、構造が変化しているデータのリレーショナルスキーマを補足するのに便利です。ただし、クエリオプティマイザーはリレーショナルモデルに依存して、大規模なフィルタリング、結合、集計、並べ替えを効率的に行います。JSON を介したクエリでは、組み込みの最適化が少なくなり、パフォーマンスを検査および調整するためのアフォーダンスが少なくなります。

仕様

Spanner JSON 型は、入力 JSON ドキュメントの正規化表現を格納します。

JSON は最大で 80 レベルまでネストできます。
空白文字は保持されません。
コメントはサポートされません。コメント付きのトランザクションやクエリは失敗します。
JSON オブジェクトのメンバーは辞書順に並べ替えられます。
JSON 配列要素の順序は保持されます。
1 つの JSON オブジェクトに重複するキーがある場合、最初のキーだけが保持されます。
プリミティブ型（文字列、ブール値、数値、null）は、その型と値を保持します。
- 文字列型の値はそのまま保持されます。
- 数値型の値は保持されますが、正規化プロセスの結果としてテキスト表現が変更される場合があります。たとえば、入力値 10000 は、正規化され 1e+4 で表現される場合があります。数値の保存セマンティクスは次のとおりです。
  - [INT64_MIN, INT64_MAX] の範囲の符号付き整数は保持されます。
  - [0, UINT64_MAX] の範囲の符号なし整数は保持されます。
  - 精度を失うことなく文字列、double、文字列へのラウンドトリップが可能な double 値は保持されます。double 値をこのような方法でラウンドトリップできない場合、トランザクションまたはクエリは失敗します。
    - たとえば、SELECT JSON '2.2412421353246235436' は失敗します。
    - 実用的な回避策は、JSON '2.2412421353246237' を返す PARSE_JSON('2.2412421353246235436', wide_number_mode=>'round') です。
TO_JSON()、JSON_OBJECT() 、JSON_ARRAY() 関数を使用して、SQL で JSON ドキュメントを作成します。これらの関数は、必要な引用符とエスケープ文字を実装します。

正規化されたドキュメントの最大許容サイズは 10 MB です。

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 列を追加することも、削除することもできます。

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