‫Google משתמשת בטכנולוגיית AI כדי לתרגם תוכן לשפה המועדפת עליך. בתרגומים כאלו עשויות להיות שגיאות.

עבודה עם מערכים

ב-GoogleSQL ל-BigQuery, מערך הוא רשימה מסודרת שמכילה אפס או יותר ערכים מאותו סוג נתונים. אפשר ליצור מערכים של סוג נתונים פשוט, כמו INT64, או של סוג נתונים מורכב, כמו STRUCT. עם זאת, אין תמיכה במערכים של מערכים. למידע נוסף על סוג הנתונים ARRAY, כולל טיפול ב-NULL, קראו את המאמר סוג מערך.

באמצעות GoogleSQL, אתם יכולים ליצור ייצוגים מילוליים של מערכים, לבנות מערכים משאילתות משנה באמצעות הפונקציה ARRAY ולצבור ערכים במערך באמצעות הפונקציה ARRAY_AGG.

אפשר לשלב מערכים באמצעות פונקציות כמו ARRAY_CONCAT(), ולהמיר מערכים למחרוזות באמצעות ARRAY_TO_STRING().

גישה לאלמנטים של מערך

נבחן את הטבלה הבאה שנקראת Sequences. הטבלה הזו מכילה את העמודה some_numbers מסוג הנתונים ARRAY.

WITH
  Sequences AS (
    SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL
    SELECT [2, 4, 8, 16, 32] UNION ALL
    SELECT [5, 10]
  )
SELECT * FROM Sequences;

/*---------------------+
 | some_numbers        |
 +---------------------+
 | [0, 1, 1, 2, 3, 5]  |
 | [2, 4, 8, 16, 32]   |
 | [5, 10]             |
 +---------------------*/

כדי לגשת לאלמנטים של מערך בעמודה some_numbers, צריך לציין את סוג ההוספה לאינדקס שבו רוצים להשתמש: index או OFFSET(index) לאינדקסים שמבוססים על אפס, או ORDINAL(index) לאינדקסים שמבוססים על אחד:

SELECT
  some_numbers,
  some_numbers[0] AS index_0,
  some_numbers[OFFSET(1)] AS offset_1,
  some_numbers[ORDINAL(1)] AS ordinal_1
FROM Sequences;

/*--------------------+---------+----------+-----------+
 | some_numbers       | index_0 | offset_1 | ordinal_1 |
 +--------------------+---------+----------+-----------+
 | [0, 1, 1, 2, 3, 5] | 0       | 1        | 0         |
 | [2, 4, 8, 16, 32]  | 2       | 4        | 2         |
 | [5, 10]            | 5       | 10       | 5         |
 +--------------------+---------+----------+-----------*/

כדי לגשת לאלמנט הראשון או האחרון במערך, משתמשים בפונקציה ARRAY_FIRST או ARRAY_LAST:

SELECT
  some_numbers,
  ARRAY_FIRST(some_numbers) AS first_element,
  ARRAY_LAST(some_numbers) AS last_element
FROM Sequences;

/*--------------------+---------------+--------------+
 | some_numbers       | first_element | last_element |
 +--------------------+---------------+--------------+
 | [0, 1, 1, 2, 3, 5] | 0             | 5            |
 | [2, 4, 8, 16, 32]  | 2             | 32           |
 | [5, 10]            | 5             | 10           |
 +--------------------+---------------+--------------*/

בפונקציות ARRAY_FIRST ו-ARRAY_LAST, אם המערך ריק, הפונקציה מחזירה שגיאה:

WITH
  Sequences AS (
    SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
    UNION ALL
    SELECT [2, 4, 8, 16, 32]
    UNION ALL
    SELECT [] -- Empty array
  )
SELECT
  some_numbers,
  ARRAY_LAST(some_numbers) AS last_element
FROM Sequences;

-- Error: ARRAY_LAST can't get the last element of an empty array.

כדי לטפל במערכים ריקים כשניגשים לאלמנטים הראשונים והאחרונים, אפשר להשתמש בפונקציה ARRAY_LENGTH בתוך SAFE_OFFSET של -1. השאילתה מחזירה ערכי NULL לכל מערך ריק במקום שגיאה:

SELECT
  some_numbers,
  some_numbers[SAFE_OFFSET(ARRAY_LENGTH(some_numbers) - 1)] AS last_element
FROM Sequences;

/*--------------------+--------------+
 | some_numbers       | last_element |
 +--------------------+--------------+
 | [0, 1, 1, 2, 3, 5] | 5            |
 | [2, 4, 8, 16, 32]  | 32           |
 | []                 | NULL         |
 +--------------------+--------------*/

‫ARRAY_LENGTH(array) מחזירה את מספר הרכיבים במערך. מכיוון שההיסטים של המערך מבוססים על 0, הערך של ARRAY_LENGTH(array) - 1 נותן את ההיסט של הרכיב האחרון. אם המערך ריק, הערך של ARRAY_LENGTH הוא 0, וההיסט הופך ל-1-. הפונקציה SAFE_OFFSET(-1) מחזירה את הערך NULL, כך שהגישה הזו מטפלת בבטחה במערכים ריקים.

מציאת אורכים

הפונקציה ARRAY_LENGTH מחזירה את האורך של מערך.

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
       ARRAY_LENGTH(some_numbers) AS len
FROM Sequences;

/*--------------------+--------+
 | some_numbers       | len    |
 +--------------------+--------+
 | [0, 1, 1, 2, 3, 5] | 6      |
 | [2, 4, 8, 16, 32]  | 5      |
 | [5, 10]            | 2      |
 +--------------------+--------*/

המרת רכיבים במערך לשורות בטבלה

כדי להמיר ARRAY לקבוצת שורות, פעולה שנקראת גם "השטחה", משתמשים באופרטור UNNEST. הפונקציה UNNEST מקבלת ARRAY ומחזירה טבלה עם שורה אחת לכל רכיב ב-ARRAY.

מכיוון שהפונקציה UNNEST משנה את הסדר של רכיבי ARRAY, יכול להיות שתרצו לשחזר את הסדר בטבלה. כדי לעשות זאת, משתמשים בסעיף האופציונלי WITH OFFSET כדי להחזיר עמודה נוספת עם ההיסט לכל רכיב במערך, ואז משתמשים בסעיף ORDER BY כדי לסדר את השורות לפי ההיסט שלהן.

דוגמה

SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
  AS element
WITH OFFSET AS offset
ORDER BY offset;

/*----------+--------+
 | element  | offset |
 +----------+--------+
 | foo      | 0      |
 | bar      | 1      |
 | baz      | 2      |
 | qux      | 3      |
 | corge    | 4      |
 | garply   | 5      |
 | waldo    | 6      |
 | fred     | 7      |
 +----------+--------*/

כדי לשטח עמודה שלמה מסוג ARRAY תוך שמירה על הערכים של העמודות האחרות בכל שורה, משתמשים ב-INNER JOIN מתואם כדי לצרף את הטבלה שמכילה את העמודה ARRAY לפלט UNNEST של אותה עמודה ARRAY.

בצירוף correlated, האופרטור UNNEST מפנה לעמודה מסוג מוגדר ARRAY מכל שורה בטבלת המקור, שמופיעה לפני כן בסעיף FROM. בכל שורה N בטבלת המקור, UNNEST משטיח את ARRAY מהשורה N לקבוצת שורות שמכילות את הרכיבים של ARRAY, ואז INNER JOIN או CROSS JOIN משלבים את קבוצת השורות החדשה עם השורה היחידה N מטבלת המקור.

דוגמאות

בדוגמה הבאה משתמשים ב-UNNEST כדי להחזיר שורה לכל רכיב בעמודת המערך. בגלל ה-INNER JOIN, העמודה id מכילה את הערכים של id בשורה ב-Sequences שמכילה כל מספר.

WITH
  Sequences AS (
    SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
    UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
    UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers
  )
SELECT id, flattened_numbers
FROM Sequences
INNER JOIN UNNEST(Sequences.some_numbers) AS flattened_numbers;

/*------+-------------------+
 | id   | flattened_numbers |
 +------+-------------------+
 |    1 |                 0 |
 |    1 |                 1 |
 |    1 |                 1 |
 |    1 |                 2 |
 |    1 |                 3 |
 |    1 |                 5 |
 |    2 |                 2 |
 |    2 |                 4 |
 |    2 |                 8 |
 |    2 |                16 |
 |    2 |                32 |
 |    3 |                 5 |
 |    3 |                10 |
 +------+-------------------*/

שימו לב שבאיחודים מתואמים, האופרטור UNNEST הוא אופציונלי ואפשר לבטא את INNER JOIN בתור CROSS JOIN או בתור שאילתת איחוד (cross join) עם פסיק. אם משתמשים בסימון המקוצר של שאילתת איחוד (cross join) עם פסיק, הדוגמה הקודמת נראית כך:

WITH
  Sequences AS (
    SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
    UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
    UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers
  )
SELECT id, flattened_numbers
FROM Sequences, Sequences.some_numbers AS flattened_numbers;

/*------+-------------------+
 | id   | flattened_numbers |
 +------+-------------------+
 |    1 |                 0 |
 |    1 |                 1 |
 |    1 |                 1 |
 |    1 |                 2 |
 |    1 |                 3 |
 |    1 |                 5 |
 |    2 |                 2 |
 |    2 |                 4 |
 |    2 |                 8 |
 |    2 |                16 |
 |    2 |                32 |
 |    3 |                 5 |
 |    3 |                10 |
 +------+-------------------*/

שליחת שאילתות למערכים בתצוגת עץ

אם טבלה מכילה ARRAY של STRUCT, אפשר לשטח את ה-ARRAY כדי לשלוח שאילתה לשדות של ה-STRUCT. אפשר גם לשטח שדות מסוג ARRAY של ערכי STRUCT.

שליחת שאילתה לגבי `STRUCT` רכיבים במערך

בדוגמה הבאה משתמשים ב-UNNEST עם INNER JOIN כדי לשטח ARRAY של STRUCT.

WITH
  Races AS (
    SELECT
      "800M" AS race,
      [
        STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
        STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
        STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
        STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
        STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
        STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
        STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
        STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)
      ] AS participants
    )
SELECT
  race,
  participant
FROM Races AS r
INNER JOIN UNNEST(r.participants) AS participant;

/*------+---------------------------------------+
 | race | participant                           |
 +------+---------------------------------------+
 | 800M | {Rudisha, [23.4, 26.3, 26.4, 26.1]}   |
 | 800M | {Makhloufi, [24.5, 25.4, 26.6, 26.1]} |
 | 800M | {Murphy, [23.9, 26, 27, 26]}          |
 | 800M | {Bosse, [23.6, 26.2, 26.5, 27.1]}     |
 | 800M | {Rotich, [24.7, 25.6, 26.9, 26.4]}    |
 | 800M | {Lewandowski, [25, 25.7, 26.3, 27.2]} |
 | 800M | {Kipketer, [23.2, 26.1, 27.3, 29.4]}  |
 | 800M | {Berian, [23.7, 26.1, 27, 29.3]}      |
 +------+---------------------------------------*/

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

דוגמה

WITH
  Races AS (
    SELECT
      "800M" AS race,
      [
        STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
        STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
        STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
        STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
        STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
        STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
        STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
        STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)
      ] AS participants
  )
SELECT
  race,
  (
    SELECT name
    FROM UNNEST(participants)
    ORDER BY (SELECT SUM(duration) FROM UNNEST(laps) AS duration) ASC
    LIMIT 1
  ) AS fastest_racer
FROM Races;

/*------+---------------+
 | race | fastest_racer |
 +------+---------------+
 | 800M | Rudisha       |
 +------+---------------*/

שליחת שאילתות לשדות מסוג `ARRAY` ב-struct

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

WITH
  Races AS (
    SELECT
      "800M" AS race,
      [
        STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
        STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
        STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
        STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
        STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
        STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
        STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
        STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)
      ]AS participants
  )
SELECT
  race,
  (
    SELECT name
    FROM UNNEST(participants), UNNEST(laps) AS duration
    ORDER BY duration ASC
    LIMIT 1
  ) AS runner_with_fastest_lap
FROM Races;

/*------+-------------------------+
 | race | runner_with_fastest_lap |
 +------+-------------------------+
 | 800M | Kipketer                |
 +------+-------------------------*/

שימו לב שבשאילתה הקודמת נעשה שימוש באופרטור הפסיק (,) כדי לבצע שאילתת איחוד (cross join) ולשטח את המערך. זה שווה ערך לשימוש ב-CROSS JOIN מפורש, או לדוגמה הבאה שבה משתמשים ב-INNER JOIN מפורש:

WITH
  Races AS (
    SELECT "800M" AS race,
      [
        STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
        STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
        STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
        STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
        STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
        STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
        STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
        STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)
      ] AS participants
  )
SELECT
  race,
  (
    SELECT name
    FROM UNNEST(participants)
    INNER JOIN UNNEST(laps) AS duration
    ORDER BY duration ASC LIMIT 1
  ) AS runner_with_fastest_lap
FROM Races;

/*------+-------------------------+
 | race | runner_with_fastest_lap |
 +------+-------------------------+
 | 800M | Kipketer                |
 +------+-------------------------*/

כשמבצעים שיטוח של מערכים באמצעות INNER JOIN, המערכת לא כוללת שורות עם מערכים ריקים או מערכים מסוג NULL. אם רוצים לכלול את השורות האלה, צריך להשתמש ב-LEFT JOIN.

WITH
  Races AS (
    SELECT
      "800M" AS race,
      [
        STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
        STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
        STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
        STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
        STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
        STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
        STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
        STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps),
        STRUCT("Nathan" AS name, ARRAY<FLOAT64>[] AS laps),
        STRUCT("David" AS name, NULL AS laps)
      ] AS participants
  )
SELECT
  Participant.name,
  SUM(duration) AS finish_time
FROM Races
INNER JOIN Races.participants AS Participant
LEFT JOIN Participant.laps AS duration
GROUP BY name;

/*-------------+--------------------+
 | name        | finish_time        |
 +-------------+--------------------+
 | Murphy      | 102.9              |
 | Rudisha     | 102.19999999999999 |
 | David       | NULL               |
 | Rotich      | 103.6              |
 | Makhloufi   | 102.6              |
 | Berian      | 106.1              |
 | Bosse       | 103.4              |
 | Kipketer    | 106                |
 | Nathan      | NULL               |
 | Lewandowski | 104.2              |
 +-------------+--------------------*/

בניית מערכים

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

יצירת מערכים משאילתות משנה

משימה נפוצה כשעובדים עם מערכים היא להפוך תוצאה של שאילתת משנה למערך. ב-GoogleSQL, אפשר לעשות את זה באמצעות הפונקציה ARRAY().

לדוגמה, נניח שאתם מבצעים את הפעולה הבאה בטבלה Sequences:

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
  UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
  UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x) AS doubled
FROM Sequences;

/*--------------------+---------------------+
 | some_numbers       | doubled             |
 +--------------------+---------------------+
 | [0, 1, 1, 2, 3, 5] | [0, 2, 2, 4, 6, 10] |
 | [2, 4, 8, 16, 32]  | [4, 8, 16, 32, 64]  |
 | [5, 10]            | [10, 20]            |
 +--------------------+---------------------*/

הדוגמה הזו מתחילה בטבלה בשם Sequences. הטבלה הזו מכילה עמודה, some_numbers, מסוג ARRAY<INT64>.

השאילתה עצמה מכילה שאילתת משנה. שאילתת המשנה הזו בוחרת כל שורה בעמודה some_numbers ומשתמשת ב-UNNEST כדי להחזיר את המערך כקבוצת שורות. לאחר מכן, היא מכפילה כל ערך בשתיים, ואז משלבת מחדש את השורות למערך באמצעות האופרטור ARRAY().

סינון מערכים

בדוגמה הבאה נעשה שימוש בסעיף WHERE בשאילתת המשנה של האופרטור ARRAY() כדי לסנן את השורות שמוחזרות.

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x
        WHERE x < 5) AS doubled_less_than_five
FROM Sequences;

/*------------------------+
 | doubled_less_than_five |
 +------------------------+
 | [0, 2, 2, 4, 6]        |
 | [4, 8]                 |
 | []                     |
 +------------------------*/

שימו לב שהשורה השלישית מכילה מערך ריק, כי הרכיבים בשורה המקורית התואמת ([5, 10]) לא עמדו בדרישת הסינון של x < 5.

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

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers)
SELECT ARRAY(SELECT DISTINCT x
             FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM Sequences;

/*-----------------+
 | unique_numbers  |
 +-----------------+
 | [0, 1, 2, 3, 5] |
 +-----------------*/

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

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
   ARRAY(SELECT x
         FROM UNNEST(some_numbers) AS x
         WHERE 2 IN UNNEST(some_numbers)) AS contains_two
FROM Sequences;

/*--------------------+
 | contains_two       |
 +--------------------+
 | [0, 1, 1, 2, 3, 5] |
 | [2, 4, 8, 16, 32]  |
 | []                 |
 +--------------------*/

שימו לב שוב שהשורה השלישית מכילה מערך ריק, כי המערך בשורה המקורית התואמת ([5, 10]) לא הכיל את 2.

מערכי סריקה

כדי לבדוק אם מערך מכיל ערך ספציפי, משתמשים באופרטור IN עם UNNEST. כדי לבדוק אם מערך מכיל ערך שתואם לתנאי, משתמשים באופרטור EXISTS עם UNNEST.

סריקה לאיתור ערכים ספציפיים

כדי לסרוק מערך ולחפש ערך ספציפי, משתמשים באופרטור IN עם UNNEST.

דוגמה

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

SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;

/*----------------+
 | contains_value |
 +----------------+
 | true           |
 +----------------*/

כדי להחזיר את השורות בטבלה שבהן עמודת המערך מכילה ערך ספציפי, מסננים את התוצאות של IN UNNEST באמצעות הסעיף WHERE.

דוגמה

בדוגמה הבאה מוחזר הערך id עבור השורות שבהן עמודת המערך מכילה את הערך 2.

WITH Sequences AS
  (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows
FROM Sequences
WHERE 2 IN UNNEST(Sequences.some_numbers)
ORDER BY matching_rows;

/*---------------+
 | matching_rows |
 +---------------+
 | 1             |
 | 2             |
 +---------------*/

סריקה לאיתור ערכים שעומדים בתנאי מסוים

כדי לסרוק מערך ולחפש ערכים שתואמים לתנאי, משתמשים ב-UNNEST כדי להחזיר טבלה של הרכיבים במערך, משתמשים ב-WHERE כדי לסנן את הטבלה שנוצרה בשאילתת משנה, ומשתמשים ב-EXISTS כדי לבדוק אם הטבלה המסוננת מכילה שורות.

דוגמה

בדוגמה הבאה הפונקציה מחזירה את הערך של id בשורות שבהן עמודת המערך מכילה ערכים שגדולים מ-5.

WITH
  Sequences AS (
    SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
    UNION ALL
    SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
    UNION ALL
    SELECT 3 AS id, [5, 10] AS some_numbers
  )
SELECT id AS matching_rows
FROM Sequences
WHERE EXISTS(SELECT * FROM UNNEST(some_numbers) AS x WHERE x > 5);

/*---------------+
 | matching_rows |
 +---------------+
 | 2             |
 | 3             |
 +---------------*/

סריקה של ערכי שדות מסוג `STRUCT` שעומדים בתנאי מסוים

כדי לחפש במערך של ערכי STRUCT שדה שהערך שלו תואם לתנאי, משתמשים ב-UNNEST כדי להחזיר טבלה עם עמודה לכל שדה STRUCT, ואז מסננים מהטבלה שורות שלא תואמות באמצעות WHERE EXISTS.

דוגמה

בדוגמה הבאה מוחזרות השורות שבהן עמודת המערך מכילה STRUCT שהשדה b שלו מכיל ערך שגדול מ-3.

WITH
  Sequences AS (
    SELECT 1 AS id, [STRUCT(0 AS a, 1 AS b)] AS some_numbers
    UNION ALL
    SELECT 2 AS id, [STRUCT(2 AS a, 4 AS b)] AS some_numbers
    UNION ALL
    SELECT 3 AS id, [STRUCT(5 AS a, 3 AS b), STRUCT(7 AS a, 4 AS b)] AS some_numbers
  )
SELECT id AS matching_rows
FROM Sequences
WHERE EXISTS(SELECT 1 FROM UNNEST(some_numbers) WHERE b > 3);

/*---------------+
 | matching_rows |
 +---------------+
 | 2             |
 | 3             |
 +---------------*/

מערכים וצבירת נתונים

באמצעות GoogleSQL, אתם יכולים לצבור ערכים למערך באמצעות ARRAY_AGG().

WITH Fruits AS
  (SELECT "apple" AS fruit
   UNION ALL SELECT "pear" AS fruit
   UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM Fruits;

/*-----------------------+
 | fruit_basket          |
 +-----------------------+
 | [apple, pear, banana] |
 +-----------------------*/

המערך שמוחזר על ידי ARRAY_AGG() הוא בסדר שרירותי, כי הסדר שבו הפונקציה משרשרת ערכים לא מובטח. כדי לסדר את הרכיבים במערך, משתמשים ב-ORDER BY:

WITH Fruits AS
  (SELECT "apple" AS fruit
   UNION ALL SELECT "pear" AS fruit
   UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit ORDER BY fruit) AS fruit_basket
FROM Fruits;

/*-----------------------+
 | fruit_basket          |
 +-----------------------+
 | [apple, banana, pear] |
 +-----------------------*/

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

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
  (SELECT SUM(x)
   FROM UNNEST(s.some_numbers) AS x) AS sums
FROM Sequences AS s;

/*--------------------+------+
 | some_numbers       | sums |
 +--------------------+------+
 | [0, 1, 1, 2, 3, 5] | 12   |
 | [2, 4, 8, 16, 32]  | 62   |
 | [5, 10]            | 15   |
 +--------------------+------*/

‫GoogleSQL תומך גם בפונקציית צבירה, ARRAY_CONCAT_AGG(), שמשרשרת את הרכיבים של עמודת מערך בשורות.

WITH Aggregates AS
  (SELECT [1,2] AS numbers
   UNION ALL SELECT [3,4] AS numbers
   UNION ALL SELECT [5, 6] AS numbers)
SELECT ARRAY_CONCAT_AGG(numbers) AS count_to_six_agg
FROM Aggregates;

/*--------------------------------------------------+
 | count_to_six_agg                                 |
 +--------------------------------------------------+
 | [1, 2, 3, 4, 5, 6]                               |
 +--------------------------------------------------*/

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

הפונקציה ARRAY_TO_STRING() מאפשרת להמיר ARRAY<STRING> לערך STRING יחיד או ARRAY<BYTES> לערך BYTES יחיד, כשהערך שמתקבל הוא השרשור המסודר של רכיבי המערך.

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

דוגמה:

WITH Words AS
  (SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM Words;

/*-------------+
 | greetings   |
 +-------------+
 | Hello World |
 +-------------*/

הארגומנט השלישי האופציונלי מחליף את הערכים של NULL במערך הקלט.

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

דוגמה:

SELECT
  ARRAY_TO_STRING(arr, ".", "N") AS non_empty_string,
  ARRAY_TO_STRING(arr, ".", "") AS empty_string,
  ARRAY_TO_STRING(arr, ".") AS omitted
FROM (SELECT ["a", NULL, "b", NULL, "c", NULL] AS arr);

/*------------------+--------------+---------+
 | non_empty_string | empty_string | omitted |
 +------------------+--------------+---------+
 | a.N.b.N.c.N      | a..b..c.     | a.b.c   |
 +------------------+--------------+---------*/

שילוב מערכים

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

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) AS count_to_six;

/*--------------------------------------------------+
 | count_to_six                                     |
 +--------------------------------------------------+
 | [1, 2, 3, 4, 5, 6]                               |
 +--------------------------------------------------*/

עדכון מערכים

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

WITH arrays_table AS (
  SELECT
    [1, 2] AS regular_array,
    STRUCT([10, 20] AS first_array, [100, 200] AS second_array) AS nested_arrays
  UNION ALL SELECT
    [3, 4] AS regular_array,
    STRUCT([30, 40] AS first_array, [300, 400] AS second_array) AS nested_arrays
)
SELECT * FROM arrays_table;

/*---------------*---------------------------*----------------------------+
 | regular_array | nested_arrays.first_array | nested_arrays.second_array |
 +---------------+---------------------------+----------------------------+
 | [1, 2]        | [10, 20]                  | [100, 200]                 |
 | [3, 4]        | [30, 40]                  | [130, 400]                 |
 +---------------*---------------------------*----------------------------*/

אפשר לעדכן מערכים בטבלה באמצעות ההצהרה UPDATE. בדוגמה הבאה, המספר 5 מוכנס לעמודה regular_array, והרכיבים מהשדה first_array של העמודה nested_arrays מוכנסים לשדה second_array:

UPDATE
  arrays_table
SET
  regular_array = ARRAY_CONCAT(regular_array, [5]),
  nested_arrays.second_array = ARRAY_CONCAT(nested_arrays.second_array,
                                            nested_arrays.first_array)
WHERE TRUE;
SELECT * FROM arrays_table;

/*---------------*---------------------------*----------------------------+
 | regular_array | nested_arrays.first_array | nested_arrays.second_array |
 +---------------+---------------------------+----------------------------+
 | [1, 2, 5]     | [10, 20]                  | [100, 200, 10, 20]         |
 | [3, 4, 5]     | [30, 40]                  | [130, 400, 30, 40]         |
 +---------------*---------------------------*----------------------------*/

דחיסת מערכים

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

אפשר לכווץ מערכים באמצעות UNNEST ו-WITH OFFSET. בדוגמה הזו, כל זוג ערכים מאוחסן כ-STRUCT במערך.

WITH
  Combinations AS (
    SELECT
      ['a', 'b'] AS letters,
      [1, 2, 3] AS numbers
  )
SELECT
  ARRAY(
    SELECT AS STRUCT
      letters[SAFE_OFFSET(index)] AS letter,
      numbers[SAFE_OFFSET(index)] AS number
    FROM Combinations
    INNER JOIN
      UNNEST(
        GENERATE_ARRAY(
          0,
          LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
    ORDER BY index
  ) AS pairs;

/*------------------------------+
 | pairs                        |
 +------------------------------+
 | [{ letter: "a", number: 1 }, |
 |  { letter: "b", number: 2 }] |
 +------------------------------*/

אפשר להשתמש במערכי קלט באורכים שונים, כל עוד המערך הראשון שווה לאורך של המערך השני או קטן ממנו. המערך המכווץ יהיה באורך של מערך הקלט הקצר ביותר.

כדי לקבל מערך דחוס שכולל את כל הרכיבים גם כשהמערכים של הקלט באורכים שונים, משנים את LEAST ל-GREATEST. רכיבים באחד מהמערכים שאין להם רכיב משויך במערך השני יותאמו ל-NULL.

WITH
  Combinations AS (
    SELECT
      ['a', 'b'] AS letters,
      [1, 2, 3] AS numbers
  )
SELECT
  ARRAY(
    SELECT AS STRUCT
      letters[SAFE_OFFSET(index)] AS letter,
      numbers[SAFE_OFFSET(index)] AS number
    FROM Combinations
    INNER JOIN
      UNNEST(
        GENERATE_ARRAY(
          0,
          GREATEST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
    ORDER BY index
  ) AS pairs;

/*-------------------------------+
 | pairs                         |
 +-------------------------------+
 | [{ letter: "a", number: 1 },  |
 |  { letter: "b", number: 2 },  |
 |  { letter: null, number: 3 }] |
 +-------------------------------*/

בניית מערכים של מערכים

‫GoogleSQL לא תומך ביצירה ישירה של מערכים של מערכים. במקום זאת, צריך ליצור מערך של מבנים, כשכל מבנה מכיל שדה מסוג ARRAY. כדי להמחיש את זה, נבחן את הטבלה הבאה ב-Points:

/*----------+
 | point    |
 +----------+
 | [1, 5]   |
 | [2, 8]   |
 | [3, 7]   |
 | [4, 1]   |
 | [5, 7]   |
 +----------*/

עכשיו נניח שאתם רוצים ליצור מערך שמורכב מכל point בטבלה Points. כדי לעשות את זה, צריך לעטוף את המערך שמוחזר מכל שורה ב-STRUCT, כמו שמוצג בהמשך.

WITH Points AS
  (SELECT [1, 5] AS point
   UNION ALL SELECT [2, 8] AS point
   UNION ALL SELECT [3, 7] AS point
   UNION ALL SELECT [4, 1] AS point
   UNION ALL SELECT [5, 7] AS point)
SELECT ARRAY(
  SELECT STRUCT(point)
  FROM Points)
  AS coordinates;

/*-------------------+
 | coordinates       |
 +-------------------+
 | [{point: [1,5]},  |
 |  {point: [2,8]},  |
 |  {point: [5,7]},  |
 |  {point: [3,7]},  |
 |  {point: [4,1]}]  |
 +-------------------*/

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

גישה לאלמנטים של מערך

מציאת אורכים

המרת רכיבים במערך לשורות בטבלה

שליחת שאילתות למערכים בתצוגת עץ

שליחת שאילתה לגבי STRUCT רכיבים במערך

שליחת שאילתות לשדות מסוג ARRAY ב-struct

בניית מערכים

יצירת מערכים משאילתות משנה

סינון מערכים

מערכי סריקה

סריקה לאיתור ערכים ספציפיים

סריקה לאיתור ערכים שעומדים בתנאי מסוים

סריקה של ערכי שדות מסוג STRUCT שעומדים בתנאי מסוים

מערכים וצבירת נתונים

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

שילוב מערכים

עדכון מערכים

דחיסת מערכים

בניית מערכים של מערכים

עבודה עם מערכים

שליחת שאילתה לגבי `STRUCT` רכיבים במערך

שליחת שאילתות לשדות מסוג `ARRAY` ב-struct

סריקה של ערכי שדות מסוג `STRUCT` שעומדים בתנאי מסוים