JSON 데이터 작업

PostgreSQL 인터페이스 참고: JSON 데이터 유형은 PostgreSQL 언어 데이터베이스에서 지원되지 않습니다. PostgreSQL 언어 데이터베이스에 JSONB 데이터 유형을 사용합니다.

이 페이지에서는 Spanner를 사용하여 JSON 작업을 수행하는 방법을 설명합니다.

JSON 데이터 유형은 JSON(JavaScript Object Notation) 데이터를 보관하는 데 사용되는 반구조화된 데이터 유형입니다. JSON 형식의 사양은 RFC 7159에 설명되어 있습니다.

JSON은 희소 데이터이거나 느슨하게 정의되거나 변경되는 구조가 있는 데이터의 관계형 스키마를 보완하는 데 유용합니다. 그러나 쿼리 최적화 도구는 관계형 모델을 사용하여 규모에 맞게 효율적으로 필터링, 조인, 집계, 정렬합니다. JSON을 통한 쿼리는 기본 제공되는 최적화가 줄어들고 성능을 검사하고 조정하기 위한 어포던스를 줄일 수 있습니다.

사양

Spanner JSON 유형은 입력 JSON 문서의 정규화된 표현을 저장합니다.

JSON은 최대 80개 수준까지 중첩될 수 있습니다.
공백은 유지되지 않습니다.
주석은 지원되지 않습니다. 주석이 있는 트랜잭션 또는 쿼리는 실패합니다.
JSON 객체의 구성원은 사전순으로 정렬됩니다.
JSON 배열 요소의 순서는 유지됩니다.
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 문서를 구성합니다. 이러한 함수는 필요한 따옴표와 이스케이프 문자를 구현합니다.

정규화된 문서의 최대 허용 크기는 10MB입니다.

null 허용 여부

JSON null 값은 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 유형 값은 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 클라이언트 라이브러리를 사용하여 Venues 테이블에 VenueDetails라는 JSON 열을 추가하는 방법을 보여줍니다.

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
}

자바


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