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

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

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

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

מפרטים

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

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

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

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

ערכי 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;

בדוגמה הבאה מוסבר איך להוסיף עמודת 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.

הגבלות

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