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

במאמר הזה מוסבר איך ליצור טבלה עם עמודת JSON, להוסיף נתוני JSON לטבלה ב-BigQuery ולשלוח שאילתות לנתוני JSON.

ב-BigQuery יש תמיכה מובנית בנתוני JSON באמצעות סוג הנתונים JSON.

‫JSON הוא פורמט נפוץ שמאפשר נתונים חצי-מובְנים, כי הוא לא דורש סכימה. אפליקציות יכולות להשתמש בגישה של "סכימה בקריאה", שבה האפליקציה מטמיעה את הנתונים ואז שולחת שאילתות על סמך הנחות לגבי הסכימה של הנתונים האלה. הגישה הזו שונה מהסוג STRUCT ב-BigQuery, שדורש סכימה קבועה שנאכפת על כל הערכים שמאוחסנים בעמודה מסוג STRUCT.

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

מגבלות

  • אם אתם משתמשים במשימת טעינה באצווה כדי להטמיע נתוני JSON בטבלה, נתוני המקור צריכים להיות בפורמט CSV,‏ Avro או JSON. אין תמיכה בפורמטים אחרים של טעינה בכמות גדולה.
  • בסוג הנתונים JSON יש מגבלת הטמעה של 500.
  • אי אפשר להשתמש ב-SQL מדור קודם כדי לשלוח שאילתה לטבלה שמכילה סוגי JSON.
  • אי אפשר להחיל כללי מדיניות גישה ברמת השורה על עמודות JSON.

למידע על המאפיינים של סוג הנתונים JSON, קראו את המאמר סוג JSON.

צור טבלה עם עמודה של JSON

אפשר ליצור טבלה ריקה עם עמודת JSON באמצעות SQL או באמצעות כלי שורת הפקודה של BigQuery.

SQL

משתמשים בהצהרה CREATE TABLE ומצהירים על עמודה עם הסוג JSON.

  1. נכנסים לדף BigQuery במסוף Google Cloud .

    כניסה ל-BigQuery

  2. בעורך השאילתות, מזינים את ההצהרה הבאה:

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

  3. לוחצים על הפעלה.

מידע נוסף על הרצת שאילתות זמין במאמר הרצת שאילתה אינטראקטיבית.

BQ

משתמשים בפקודה bq mk ומספקים סכימת טבלה עם סוג הנתונים JSON.

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

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

יצירת JSON ערכים

אפשר ליצור ערכי JSON בדרכים הבאות:

  • משתמשים ב-SQL כדי ליצור JSON ליטרלי.
  • משתמשים בפונקציה PARSE_JSON כדי להמיר ערך STRING לערך JSON.
  • משתמשים בפונקציה TO_JSON כדי להמיר ערך SQL לערך JSON.
  • משתמשים בפונקציה JSON_ARRAY כדי ליצור מערך JSON מערכי SQL.
  • משתמשים בפונקציה JSON_OBJECT כדי ליצור אובייקט JSON מצמדי מפתח/ערך.

יצירת ערך JSON

בדוגמה הבאה מוסיפים ערכי JSON לטבלה:

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']));

המרת סוג STRING לסוג JSON

בדוגמה הבאה, הערך של STRING בפורמט JSON מומר באמצעות הפונקציה PARSE_JSON. בדוגמה הזו, עמודה מטבלה קיימת מומרת לסוג JSON והתוצאות נשמרות בטבלה חדשה.

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

הקידומת SAFE שבה השתמשנו בדוגמה הזו מבטיחה שכל שגיאות ההמרה יוחזרו כערכי NULL.

המרת נתונים עם סכימה ל-JSON

בדוגמה הבאה, צמדי מפתח/ערך מומרים ל-JSON באמצעות הפונקציה 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

התוצאה היא:

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

המרת סוג SQL לסוג JSON

בדוגמה הבאה, הערך STRUCT של SQL מומר לערך JSON באמצעות הפונקציה TO_JSON:

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

התוצאה היא:

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

הטמעת נתוני JSON

אפשר להטמיע נתוני JSON בטבלה ב-BigQuery בדרכים הבאות:

טעינה מקובצי CSV

בדוגמה הבאה, נניח שיש לכם קובץ CSV בשם file1.csv שמכיל את הרשומות הבאות:

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

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

כדי לטעון את הקובץ באמצעות כלי שורת הפקודה של BigQuery, משתמשים בפקודה 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

טעינה מקובצי JSON שמופרדים בשורה חדשה

בדוגמה הבאה, נניח שיש לכם קובץ בשם file1.jsonl שמכיל את הרשומות הבאות:

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

כדי לטעון את הקובץ באמצעות כלי שורת הפקודה של BigQuery, משתמשים בפקודה 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

שימוש ב-Storage Write API ‏ (gRPC)

אתם יכולים להשתמש ב-Storage Write API (gRPC)‎ כדי להטמיע נתוני JSON. בדוגמה הבאה משתמשים בלקוח Python של Storage Write API ‏ (gRPC) כדי לכתוב נתונים בטבלה עם עמודה מסוג נתוני JSON.

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

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

צריך להגדיר את הפורמט של נתוני ה-JSON לכל שורה כערך 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'

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

אם אתם לא יכולים לשנות את הפורמט של נתוני ה-JSON הנכנסים, אתם צריכים להשתמש בשיטה json.dumps() בקוד שלכם. לדוגמה:

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)

...

שימוש ב-Storage Write API ‏ (REST)

בדוגמה הבאה נתונים בפורמט JSON נטענים מקובץ מקומי ומועברים בסטרימינג לטבלה ב-BigQuery, עם עמודה מסוג נתונים JSON בשם json_data, באמצעות Storage Write API (REST).

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://docs.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))

למידע נוסף, קראו את המאמר הזרמת נתונים ל-BigQuery.

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

בקטע הזה מוסבר איך משתמשים ב-GoogleSQL כדי לחלץ ערכים מ-JSON. ‫JSON תלוי אותיות רישיות (case-sensitive) ותומך ב-UTF-8 גם בשדות וגם בערכים.

בדוגמאות שבקטע הזה נעשה שימוש בטבלה הבאה:

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}
        ]
    }""");

חילוץ ערכים כ-JSON

אם יש לכם סוג JSON ב-BigQuery, אתם יכולים לגשת לשדות בביטוי JSON באמצעות אופרטור הגישה לשדה. בדוגמה הבאה מוחזר השדה name של העמודה cart.

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

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

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

אפשר גם להשתמש באופרטור של אינדקס משנה ב-JSON כדי להפנות לאיברים של אובייקט JSON לפי שם:

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

בפעולות של אינדקס תחתי, הביטוי בתוך הסוגריים יכול להיות כל מחרוזת או ביטוי של מספר שלם, כולל ביטויים לא קבועים:

DECLARE int_val INT64 DEFAULT 0;

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

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

האופרטורים האלה משפרים את הקריאוּת של הפונקציונליות הבסיסית של הפונקציה JSON_QUERY. לדוגמה, הביטוי cart.name שווה ערך ל-JSON_QUERY(cart, "$.name").

אם לא נמצא איבר עם השם שצוין באובייקט ה-JSON, או אם במערך ה-JSON אין רכיב עם המיקום שצוין, האופרטורים האלה מחזירים את הערך NULL ב-SQL.

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

האופרטורים של השוויון וההשוואה לא מוגדרים בסוג הנתונים JSON. לכן, אי אפשר להשתמש בערכי JSON ישירות בסעיפים כמו GROUP BY או ORDER BY. במקום זאת, השתמשו בפונקציה JSON_VALUE כדי לחלץ ערכי שדות כמחרוזות SQL, כפי שמתואר בקטע הבא.

חילוץ ערכים כמחרוזות

הפונקציה JSON_VALUE מחלצת ערך סקלרי ומחזירה אותו כמחרוזת SQL. הפונקציה מחזירה את הערך NULL ב-SQL אם cart.name לא מצביע על ערך סקלרי ב-JSON.

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

אפשר להשתמש בפונקציה JSON_VALUE בהקשרים שדורשים שוויון או השוואה, כמו משפטי WHERE ומשפטי GROUP BY. בדוגמה הבאה מוצג תנאי WHERE שמסנן לפי ערך JSON:

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

לחלופין, אפשר להשתמש בפונקציה STRING שמחלצת מחרוזת JSON ומחזירה את הערך הזה בתור STRING ב-SQL. לדוגמה:

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

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

כדי לקבל את הסוג של הערך JSON, אפשר להשתמש בפונקציה JSON_TYPE.

המרת JSON גמישה

אפשר להמיר ערך JSON לערך SQL סקלרי בצורה גמישה באמצעות פונקציות LAX conversion.

בדוגמה הבאה נעשה שימוש בפונקציה LAX_INT64 כדי לחלץ ערך INT64 מערך JSON. 

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

בנוסף ל-LAX_INT64, אפשר להמיר סוגי SQL אחרים ל-JSON באופן גמיש באמצעות הפונקציות הבאות:

חילוץ מערכים מ-JSON

‫JSON יכול להכיל מערכי JSON, שלא מקבילים ישירות לסוג ARRAY<JSON> ב-BigQuery. אפשר להשתמש בפונקציות הבאות כדי לחלץ ARRAY של BigQuery מ-JSON:

  • ‫JSON_QUERY_ARRAY: חילוץ מערך והחזרה שלו בתור ARRAY<JSON> של JSON.
  • ‫JSON_VALUE_ARRAY: מחלץ מערך של ערכים סקלריים ומחזיר אותו כ-ARRAY<STRING> של ערכים סקלריים.

בדוגמה הבאה משתמשים ב-JSON_QUERY_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"}]                                 |
+----------------------------------------------------------------+

כדי לפצל מערך לרכיבים נפרדים, משתמשים באופרטור UNNEST, שמחזיר טבלה עם שורה אחת לכל רכיב במערך. בדוגמה הבאה נבחר האיבר product מכל איבר במערך 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     |
+----+---------+

הדוגמה הבאה דומה, אבל משתמשת בפונקציה ARRAY_AGG כדי לצבור את הערכים בחזרה למערך 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"]         |
+----+-----------------+

מידע נוסף על מערכים זמין במאמר עבודה עם מערכים ב-GoogleSQL.

ערכי null ב-JSON

לסוג JSON יש ערך null מיוחד ששונה מהערך NULL של SQL. ערך JSON null לא מטופל כערך SQL NULL, כמו בדוגמה הבאה.

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

כשמחלצים שדה JSON עם ערך null, ההתנהגות תלויה בפונקציה:

  • הפונקציה JSON_QUERY מחזירה null בפורמט JSON, כי זה ערך JSON תקין.
  • הפונקציה JSON_VALUE מחזירה את הערך NULL של SQL, כי הערך null של JSON הוא לא ערך סקלרי.

בדוגמה הבאה אפשר לראות את ההתנהגויות השונות:

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