עבודה עם נתוני 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. במסוף Google Cloud , עוברים לדף BigQuery.

   כניסה ל-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 literal.
• משתמשים בפונקציה 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 בדרכים הבאות:

• משתמשים בעבודת טעינה באצווה כדי לטעון לתוך JSON עמודות מהפורמטים הבאים.
  • CSV
  • Avro
  • JSON
• משתמשים ב-BigQuery Storage Write API.
• שימוש ב-tabledata.insertAll streaming API מדור קודם

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

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

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

שימו לב שהעמודה השנייה מכילה נתוני JSON שמקודדים כמחרוזת. הפעולה הזו כוללת הוספת תו בריחה למירכאות בפורמט 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

אפשר להשתמש ב-Storage Write API כדי להטמיע נתונים בפורמט JSON. בדוגמה הבאה נעשה שימוש ב-Storage Write API Python client כדי לכתוב נתונים לטבלה עם עמודה מסוג נתונים 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)

...

שימוש ב-Legacy streaming API

בדוגמה הבאה נטען קובץ JSON מקומי ומוזרם לטבלה ב-BigQuery עם עמודה מסוג נתונים JSON בשם json_data באמצעות Legacy streaming API.

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

מידע נוסף זמין במאמר בנושא הזרמת נתונים ל-BigQuery.

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

בקטע הזה מוסבר איך להשתמש ב-GoogleSQL כדי לחלץ ערכים מ-JSON. ‫JSON הוא פורמט תלוי-אותיות-רישיות, והוא תומך ב-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 אין רכיב עם המיקום שצוין, האופרטורים האלה מחזירים SQL NULL.

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. הפונקציה מחזירה SQLNULL אם 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 ולהחזיר את הערך הזה כ-SQL STRING. לדוגמה:

SELECT STRING(JSON '"purple"') AS color;

+--------+
| color  |
+--------+
| purple |
+--------+

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

• STRING
• BOOL
• INT64
• FLOAT64

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

המרת JSON בצורה גמישה

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

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

SELECT LAX_INT64(JSON '"10"') AS id;

+----+
| id |
+----+
| 10 |
+----+

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

• LAX_STRING
• LAX_BOOL
• LAX_INT64
• LAX_FLOAT64

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

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

• ‫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 ששונה מ-SQL NULL. ‫JSON null לא נחשב כערך SQL NULL, כפי שניתן לראות בדוגמה הבאה.

SELECT JSON 'null' IS NULL;

+-------+
| f0_   |
+-------+
| false |
+-------+

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

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

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

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

שימו לב: ההתנהגות הזו לא חלה על ערכי JSON שמאוחסנים בסוג STRING. כשמעבירים ערך STRING, הפונקציה JSON_QUERY מחזירה ערכי SQL NULL במקום ערכי JSON null.