עבודה עם נתוני JSON

בדף הזה מוסבר איך לעבוד עם JSON באמצעות Spanner.

סוג הנתונים JSON הוא סוג נתונים חצי מובנה שמשמש לשמירת נתוני JSON ‏(JavaScript Object Notation). המפרטים של פורמט JSON מתוארים ב-RFC 7159.

‫JSON שימושי להשלמת סכימה יחסית לנתונים חסכוניים (sparse) או לנתונים עם מבנה שמוגדר באופן רופף או משתנה. עם זאת, הכלי לאופטימיזציה של שאילתות מסתמך על המודל הרלציוני כדי לסנן, לאחד, לצבור ולמיין ביעילות בקנה מידה נרחב. בשאילתות על JSON יהיו פחות אופטימיזציות מובנות ופחות אפשרויות לבדוק ולכוונן את הביצועים.

מפרטים

הסוג Spanner JSON מאחסן ייצוג מנורמל של מסמך ה-JSON שהוזן.

  • אפשר להוסיף עד 80 רמות היררכיה של JSON.
  • הרווחים הלבנים לא נשמרים.
  • אין תמיכה בתגובות. טרנזקציות או שאילתות עם הערות ייכשלו.
  • האיברים של אובייקט JSON ממוינים לפי סדר לקסיקוגרפי.
  • הסדר של רכיבי מערך JSON נשמר.
  • אם לאובייקט JSON יש מפתחות כפולים, רק הראשון נשמר.
  • הסוג והערך של טיפוסים פרימיטיביים (מחרוזת, ערך בוליאני, מספר ו-null) נשמרים.
    • ערכים מסוג מחרוזת נשמרים בדיוק כמו שהם.
    • ערכים מסוג מספר נשמרים, אבל הייצוג הטקסטואלי שלהם עשוי להשתנות כתוצאה מתהליך הנורמליזציה. לדוגמה, המספר 10000 יכול להיות מיוצג באופן מנורמל כ-1e+4. הסמנטיקה של שמירת הערכים המספריים היא:
      • מספרים שלמים עם סימן בטווח [INT64_MIN, INT64_MAX] נשמרים.
      • מספרים שלמים לא חתומים בטווח [0, UINT64_MAX] נשמרים.
      • ערכים מסוג Double שאפשר להמיר ממחרוזת ל-Double ואז למחרוזת בלי לאבד את הדיוק העשרוני נשמרים. אם אי אפשר להמיר ערך מסוג double הלוך ושוב באופן הזה, העסקה או השאילתה נכשלות.
        • לדוגמה, SELECT JSON '2.2412421353246235436' נכשל.
        • פתרון עקיף שעובד הוא PARSE_JSON('2.2412421353246235436', wide_number_mode=>'round'), שמחזיר את הערך JSON '2.2412421353246237'.
  • כדי ליצור מסמכי JSON ב-SQL, משתמשים בפונקציות TO_JSON(),‏ JSON_OBJECT() ו-JSON_ARRAY(). הפונקציות האלה מטמיעות את התווים הנחוצים להוספת מירכאות ותווי בריחה (escape).

הגודל המקסימלי המותר של המסמך המנורמל הוא 10MB.

מאפיין המציין אם ערך יכול להיות ריק (nullability)

ערכי null ב-JSON נחשבים לערכי non-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 יכולים להיות ניתנים לאיפוס (nullable).

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;

בדוגמה הבאה אפשר לראות איך מוסיפים עמודת JSON בשם VenueDetails לטבלה Venues באמצעות ה-CLI של gcloud וספריות הלקוח של Spanner.

gcloud

gcloud spanner databases ddl update DATABASE_ID \ --instance=INSTANCE_ID \
--ddl="ALTER TABLE Venues ADD COLUMN 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
}

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

בדוגמה הבאה מוסבר איך לעדכן נתונים ב-JSON באמצעות ה-CLI של gcloud וספריות הלקוח של Spanner.

gcloud

gcloud spanner databases execute-sql DATABASE_ID --instance=INSTANCE_ID \
--sql="UPDATE Venues SET VenueDetails = JSON '{\"rating\": 9, \"open\": true}' WHERE VenueId = 1"

C++‎

C++

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

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#‎

C#

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 


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, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

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

Java

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 


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

Node.js

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

// 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

PHP

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

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

Python

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

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

Ruby

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

# 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 שבו מסד הנתונים משתמש כדי למצוא את המקומות עם קיבולת גדולה מ-1,000. במקום לבדוק כל שורה, 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}}');

SELECT VenueName
FROM Venues
WHERE VenueDetails.open.Friday = true
  AND VenueDetails.type IN ("stadium", "arena");

מידע נוסף זמין במאמר אינדקסים של חיפוש ב-JSON.

שאילתות על נתוני JSON

בדוגמה הבאה מוסבר איך לשלוח שאילתה לנתוני JSON באמצעות ה-CLI של gcloud וספריות הלקוח של Spanner.

gcloud

gcloud spanner databases execute-sql DATABASE_ID --instance=INSTANCE_ID \
--sql="SELECT VenueId, VenueDetails FROM Venues \
WHERE JSON_VALUE(VenueDetails, '$.rating') = '9'"

C++‎

C++

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

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, std::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#‎

C#

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 


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, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 


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

Java

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

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

Node.js

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

// 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

PHP

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

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

Python

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

# 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

Ruby

במאמר ספריות הלקוח של Spanner מוסבר איך להתקין את ספריית הלקוח ולהשתמש בה ב-Spanner.

כדי לבצע אימות ב-Spanner, צריך להגדיר את Application Default Credentials. למידע נוסף, ראו הגדרת אימות לסביבת פיתוח מקומית. 

# 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 כדי לבדוק אם הם שווים או כדי לסדר אותם. אפשר גם להשתמש בערכי JSON בסעיפים GROUP BY ו-ORDER BY. כדי להאיץ השוואות שוויון בעמודות JSON או בנתיבי המשנה שלהן בעסקאות לקריאה בלבד, אפשר להשתמש באינדקס חיפוש.

לכללים ולסמנטיקה מפורטים, ראו השוואה ומיון של JSON.

הגבלות

המאמרים הבאים