Bekerja dengan data JSON di GoogleSQL

Dokumen ini menjelaskan cara membuat tabel dengan kolom JSON, menyisipkan data JSON ke dalam tabel BigQuery, dan membuat kueri data JSON.

BigQuery secara native mendukung data JSON menggunakan jenis data JSON.

JSON adalah format yang banyak digunakan dan memungkinkan data semi-terstruktur, karena tidak memerlukan skema. Aplikasi dapat menggunakan pendekatan "skema-saat-dibaca", yang mengharuskan aplikasi menyerap data, lalu membuat kueri berdasarkan asumsi tentang skema data tersebut. Pendekatan ini berbeda dengan jenis STRUCT di BigQuery, yang memerlukan skema tetap yang diterapkan untuk semua nilai yang disimpan dalam kolom jenis STRUCT.

Dengan menggunakan jenis data JSON, Anda dapat memuat JSON semi-terstruktur ke BigQuery tanpa memberikan skema untuk data JSON di awal. Tindakan ini memungkinkan Anda menyimpan dan mengkueri data yang tidak selalu mematuhi skema dan jenis data tetap. Dengan menyerap data JSON sebagai jenis data JSON, BigQuery dapat mengenkode dan memproses setiap kolom JSON satu per satu. Anda kemudian dapat membuat kueri nilai kolom dan elemen array dalam data JSON menggunakan operator akses kolom, yang membuat kueri JSON menjadi intuitif dan hemat biaya.

Batasan

  • Jika Anda menggunakan tugas pemuatan batch untuk menyerap data JSON ke dalam tabel, data sumber harus dalam format CSV, Avro, atau JSON. Format pemuatan batch lainnya tidak didukung.
  • Jenis data JSON memiliki batas bertingkat sebanyak 500.
  • Anda tidak dapat menggunakan legacy SQL untuk membuat kueri tabel yang berisi jenis JSON.
  • Kebijakan akses level baris tidak dapat diterapkan pada JSON kolom.

Untuk mempelajari properti jenis data JSON, lihat jenis JSON.

Buat tabel dengan kolom JSON

Anda dapat membuat tabel kosong dengan kolom JSON menggunakan SQL atau alat command line bq.

SQL

Gunakan pernyataan CREATE TABLE dan deklarasikan kolom dengan jenis JSON.

  1. Di konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, masukkan pernyataan berikut:

    CREATE TABLE mydataset.table1(
  id INT64,
  cart JSON
);

  3. Klik Run.

Untuk informasi selengkapnya tentang cara menjalankan kueri, lihat Menjalankan kueri interaktif.

bq

Gunakan perintah bq mk dan berikan skema tabel dengan jenis data JSON.

bq mk --table mydataset.table1 id:INT64,cart:JSON

Anda tidak dapat mempartisi atau mengelompokkan tabel di kolom JSON, karena operator kesetaraan dan perbandingan tidak ditentukan pada jenis JSON.

Buat nilai JSON

Anda dapat membuat nilai JSON dengan cara berikut:

Buat nilai JSON

Contoh berikut menyisipkan nilai JSON ke dalam tabel:

INSERT INTO mydataset.table1 VALUES
(1, JSON '{"name": "Alice", "age": 30}'),
(2, JSON_ARRAY(10, ['foo', 'bar'], [20, 30])),
(3, JSON_OBJECT('foo', 10, 'bar', ['a', 'b']));

Mengonversi jenis STRING menjadi jenis JSON

Contoh berikut mengonversi nilai STRING berformat JSON menggunakan fungsi PARSE_JSON. Contoh ini mengonversi kolom dari tabel yang ada ke jenis JSON dan menyimpan hasilnya ke tabel baru.

CREATE OR REPLACE TABLE mydataset.table_new
AS (
  SELECT
    id, SAFE.PARSE_JSON(cart) AS cart_json
  FROM
    mydataset.old_table
);

Awalan SAFE yang digunakan dalam contoh ini memastikan bahwa setiap error konversi ditampilkan sebagai nilai NULL.

Mengonversi data berskema ke JSON

Contoh berikut mengonversi key-value pair menjadi JSON menggunakan fungsi JSON_OBJECT.

WITH Fruits AS (
SELECT 0 AS id, 'color' AS k, 'Red' AS v UNION ALL
SELECT 0, 'fruit', 'apple' UNION ALL
SELECT 1, 'fruit','banana' UNION ALL
SELECT 1, 'ripe', 'true'
)

SELECT JSON_OBJECT(ARRAY_AGG(k), ARRAY_AGG(v)) AS json_data
FROM Fruits
GROUP BY id

Hasilnya adalah sebagai berikut:

+----------------------------------+
| json_data                        |
+----------------------------------+
| {"color":"Red","fruit":"apple"}  |
| {"fruit":"banana","ripe":"true"} |
+----------------------------------+

Mengonversi jenis SQL menjadi jenis JSON

Contoh berikut mengonversi nilai STRUCT SQL menjadi nilai JSON menggunakan fungsi TO_JSON:

SELECT TO_JSON(STRUCT(1 AS id, [10,20] AS coordinates)) AS pt;

Hasilnya adalah sebagai berikut:

+--------------------------------+
| pt                             |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
+--------------------------------+

Menyerap data JSON

Anda dapat menyerap data JSON ke dalam tabel BigQuery dengan cara berikut:

Muat dari file CSV

Contoh berikut mengasumsikan bahwa Anda memiliki file CSV bernama file1.csv yang berisi data berikut:

1,20
2,"""This is a string"""
3,"{""id"": 10, ""name"": ""Alice""}"

Perhatikan bahwa kolom kedua berisi data JSON yang dienkode sebagai string. Cara ini melibatkan escape tanda kutip dengan benar untuk format CSV. Dalam format CSV, tanda kutip di-escape dengan menggunakan urutan dua karakter "".

Untuk memuat file ini menggunakan alat command line bq, gunakan perintah bq load:

bq load --source_format=CSV mydataset.table1 file1.csv id:INTEGER,json_data:JSON

bq show mydataset.table1

Last modified          Schema         Total Rows   Total Bytes
----------------- -------------------- ------------ -------------
 22 Dec 22:10:32   |- id: integer       3            63
                   |- json_data: json

Muat dari file JSON yang dibatasi baris baru

Contoh berikut mengasumsikan bahwa Anda memiliki file bernama file1.jsonl yang berisi data berikut:

{"id": 1, "json_data": 20}
{"id": 2, "json_data": "This is a string"}
{"id": 3, "json_data": {"id": 10, "name": "Alice"}}

Untuk memuat file ini menggunakan alat command line bq, gunakan perintah bq load:

bq load --source_format=NEWLINE_DELIMITED_JSON mydataset.table1 file1.jsonl id:INTEGER,json_data:JSON

bq show mydataset.table1

Last modified          Schema         Total Rows   Total Bytes
----------------- -------------------- ------------ -------------
 22 Dec 22:10:32   |- id: integer       3            63
                   |- json_data: json

Menggunakan Storage Write API

Anda dapat menggunakan Storage Write API untuk menyerap data JSON. Contoh berikut menggunakan klien Python Storage Write API untuk menulis data ke dalam tabel dengan kolom jenis data JSON.

Menentukan buffering protokol untuk menyimpan data streaming serial. Data JSON dienkode sebagai string. Dalam contoh berikut, kolom json_col menyimpan data JSON.

message SampleData {
  optional string string_col = 1;
  optional int64 int64_col = 2;
  optional string json_col = 3;
}

Format data JSON untuk setiap baris sebagai nilai STRING:

row.json_col = '{"a": 10, "b": "bar"}'
row.json_col = '"This is a string"' # The double-quoted string is the JSON value.
row.json_col = '10'

Tambahkan baris ke aliran data tulis seperti yang ditunjukkan dalam contoh kode. Library klien menangani serialisasi ke format buffering protokol.

Jika tidak dapat memformat data JSON yang masuk, Anda harus menggunakan metode json.dumps() dalam kode. Berikut ini contohnya:

import json

...

row.json_col = json.dumps({"a": 10, "b": "bar"})
row.json_col = json.dumps("This is a string") # The double-quoted string is the JSON value.
row.json_col = json.dumps(10)

...

Menggunakan API streaming lama

Contoh berikut memuat data JSON dari file lokal dan melakukan streaming ke tabel BigQuery dengan kolom jenis data JSON bernama json_data menggunakan API streaming lama.

from google.cloud import bigquery
import json

# TODO(developer): Replace these variables before running the sample.
project_id = 'MY_PROJECT_ID'
table_id = 'MY_TABLE_ID'

client = bigquery.Client(project=project_id)
table_obj = client.get_table(table_id)

# The column json_data is represented as a JSON data-type column.
rows_to_insert = [
    {"id": 1, "json_data": 20},
    {"id": 2, "json_data": "This is a string"},
    {"id": 3, "json_data": {"id": 10, "name": "Alice"}}
]

# If the column json_data is represented as a String data type, modify the rows_to_insert values:
#rows_to_insert = [
#    {"id": 1, "json_data": json.dumps(20)},
#    {"id": 2, "json_data": json.dumps("This is a string")},
#    {"id": 3, "json_data": json.dumps({"id": 10, "name": "Alice"})}
#]

# Throw errors if encountered.
# https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client#google_cloud_bigquery_client_Client_insert_rows

errors = client.insert_rows(table=table_obj, rows=rows_to_insert)
if errors == []:
    print("New rows have been added.")
else:
    print("Encountered errors while inserting rows: {}".format(errors))

Untuk informasi lebih lanjut, lihat Streaming data ke BigQuery.

Membuat kueri data JSON

Bagian ini menjelaskan cara menggunakan GoogleSQL untuk mengekstrak nilai dari JSON. JSON peka huruf besar/kecil dan mendukung UTF-8 baik di kolom maupun nilai.

Contoh di bagian ini menggunakan tabel berikut:

CREATE OR REPLACE TABLE mydataset.table1(id INT64, cart JSON);

INSERT INTO mydataset.table1 VALUES
(1, JSON """{
        "name": "Alice",
        "items": [
            {"product": "book", "price": 10},
            {"product": "food", "price": 5}
        ]
    }"""),
(2, JSON """{
        "name": "Bob",
        "items": [
            {"product": "pen", "price": 20}
        ]
    }""");

Mengekstrak nilai sebagai JSON

Dengan mempertimbangkan jenis JSON di BigQuery, Anda dapat mengakses kolom dalam ekspresi JSON menggunakan operator akses kolom. Contoh berikut menampilkan kolom name dari kolom cart.

SELECT cart.name
FROM mydataset.table1;
 
+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

Untuk mengakses elemen array, gunakan operator subskrip JSON. Contoh berikut menampilkan elemen pertama dari array items:

SELECT
  cart.items[0] AS first_item
FROM mydataset.table1
 
+-------------------------------+
|          first_item           |
+-------------------------------+
| {"price":10,"product":"book"} |
| {"price":20,"product":"pen"}  |
+-------------------------------+

Anda juga dapat menggunakan operator subskrip JSON untuk mereferensikan anggota objek JSON berdasarkan nama:

SELECT cart['name']
FROM mydataset.table1;
 
+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

Untuk operasi subskrip, ekspresi di dalam tanda kurung dapat berupa ekspresi bilangan bulat atau string arbitrer, termasuk ekspresi non-konstanta:

DECLARE int_val INT64 DEFAULT 0;

SELECT
  cart[CONCAT('it','ems')][int_val + 1].product AS item
FROM mydataset.table1;
 
+--------+
|  item  |
+--------+
| "food" |
| NULL   |
+--------+

Operator subskrip dan akses kolom menampilkan jenis JSON, sehingga Anda dapat membuat rantai ekspresi yang menggunakannya atau meneruskan hasilnya ke fungsi lain yang menggunakan jenis JSON.

Operator ini adalah sugar sintaksis untuk fungsi JSON_QUERY. Misalnya, ekspresi cart.name setara dengan JSON_QUERY(cart, "$.name").

Jika anggota dengan nama yang ditentukan tidak ditemukan dalam objek JSON, atau jika array JSON tidak memiliki elemen dengan posisi yang ditentukan, operator ini akan menampilkan NULL SQL.

SELECT
  cart.address AS address,
  cart.items[1].price AS item1_price
FROM
  mydataset.table1;
 
+---------+-------------+
| address | item1_price |
+---------+-------------+
| NULL    | NULL        |
| NULL    | 5           |
+---------+-------------+

Operator kesetaraan dan perbandingan tidak ditentukan pada jenis data JSON. Oleh karena itu, Anda tidak dapat menggunakan nilai JSON secara langsung dalam klausa seperti GROUP BY atau ORDER BY. Sebagai gantinya, gunakan fungsi JSON_VALUE untuk mengekstrak nilai kolom sebagai string SQL, seperti yang dijelaskan di bagian berikutnya.

Mengekstrak nilai sebagai string

Fungsi JSON_VALUE mengekstrak nilai skalar dan menampilkannya sebagai string SQL. Metode ini menampilkan SQL NULL jika cart.name tidak mengarah ke nilai skalar di JSON.

SELECT JSON_VALUE(cart.name) AS name
FROM mydataset.table1;
 
+-------+
| name  |
+-------+
| Alice |
+-------+

Anda dapat menggunakan fungsi JSON_VALUE dalam konteks yang memerlukan kesetaraan atau perbandingan, seperti klausa WHERE dan klausa GROUP BY. Contoh berikut menunjukkan klausa WHERE yang memfilter nilai JSON:

SELECT
  cart.items[0] AS first_item
FROM
  mydataset.table1
WHERE
  JSON_VALUE(cart.name) = 'Alice';
 
+-------------------------------+
| first_item                    |
+-------------------------------+
| {"price":10,"product":"book"} |
+-------------------------------+

Atau, Anda dapat menggunakan fungsi STRING yang mengekstrak string JSON dan menampilkan nilai tersebut sebagai STRING SQL. Contoh:

SELECT STRING(JSON '"purple"') AS color;
 
+--------+
| color  |
+--------+
| purple |
+--------+

Selain STRING, Anda mungkin harus mengekstrak nilai JSON dan menampilkannya sebagai jenis data SQL lainnya. Fungsi ekstraksi nilai berikut tersedia:

Untuk mendapatkan jenis nilai JSON, Anda dapat menggunakan fungsi JSON_TYPE.

Konversi JSON secara fleksibel

Anda dapat mengonversi nilai JSON menjadi nilai SQL skalar secara fleksibel dan bebas error dengan fungsi LAX Conversion.

Contoh berikut menunjukkan kekuatan fungsi-fungsi ini. LAX_IN64 secara otomatis menyimpulkan dan memproses input dengan benar. 

SELECT LAX_INT64(JSON '"10"') AS id;
 
+----+
| id |
+----+
| 10 |
+----+

Selain LAX_IN64, Anda dapat melakukan konversi ke jenis SQL lainnya secara fleksibel ke JSON dengan fungsi berikut:

Mengekstrak array dari JSON

JSON dapat berisi array JSON, yang tidak secara langsung setara dengan jenis ARRAY<JSON> di BigQuery. Anda dapat menggunakan fungsi berikut untuk mengekstrak ARRAY BigQuery dari JSON:

  • JSON_QUERY_ARRAY: mengekstrak array dan menampilkannya sebagai ARRAY<JSON> dari JSON.
  • JSON_VALUE_ARRAY: mengekstrak array nilai skalar dan menampilkannya sebagai ARRAY<STRING> dari nilai skalar.

Contoh berikut menggunakan JSON_QUERY_ARRAY untuk mengekstrak array JSON.

SELECT JSON_QUERY_ARRAY(cart.items) AS items
FROM mydataset.table1;
 
+----------------------------------------------------------------+
| items                                                          |
+----------------------------------------------------------------+
| [{"price":10,"product":"book"}","{"price":5,"product":"food"}] |
| [{"price":20,"product":"pen"}]                                 |
+----------------------------------------------------------------+

Untuk memisahkan array menjadi masing-masing elemennya, gunakan operator UNNEST, yang menampilkan tabel berisi satu baris untuk setiap elemen dalam array. Contoh berikut memilih anggota product dari setiap anggota array items:

SELECT
  id,
  JSON_VALUE(item.product) AS product
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
ORDER BY id;
 
+----+---------+
| id | product |
+----+---------+
|  1 | book    |
|  1 | food    |
|  2 | pen     |
+----+---------+

Contoh berikutnya serupa, tetapi menggunakan fungsi ARRAY_AGG untuk menggabungkan kembali nilai ke dalam array SQL.

SELECT
  id,
  ARRAY_AGG(JSON_VALUE(item.product)) AS products
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
GROUP BY id
ORDER BY id;
 
+----+-----------------+
| id | products        |
+----+-----------------+
|  1 | ["book","food"] |
|  2 | ["pen"]         |
+----+-----------------+

Untuk mengetahui informasi selengkapnya tentang array, baca artikel Bekerja dengan array di GoogleSQL.

Null JSON

Jenis JSON memiliki nilai null khusus yang berbeda dari NULL SQL. null JSON tidak diperlakukan sebagai nilai NULL SQL, seperti yang ditunjukkan contoh berikut:

SELECT JSON 'null' IS NULL;
 
+-------+
| f0_   |
+-------+
| false |
+-------+

Saat Anda mengekstrak kolom JSON dengan nilai null, perilakunya bergantung pada fungsi:

  • Fungsi JSON_QUERY menampilkan null JSON, karena merupakan nilai JSON yang valid.
  • Fungsi JSON_VALUE menampilkan SQL NULL, karena null JSON bukan nilai skalar.

Contoh berikut menunjukkan berbagai perilaku:

SELECT
  json.a AS json_query, -- Equivalent to JSON_QUERY(json, '$.a')
  JSON_VALUE(json, '$.a') AS json_value
FROM (SELECT JSON '{"a": null}' AS json);
 
+------------+------------+
| json_query | json_value |
+------------+------------+
| null       | NULL       |
+------------+------------+