יכול להיות שחלק מהמידע בדף הזה או כולו לא רלוונטי ל-Cloud de Confiance by S3NS. פרטים נוספים מופיעים במאמר מה ההבדל מ-Google Cloud.

Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

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

ב-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.

באיחוד מתואם, האופרטור 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` במבנה

אפשר גם לקבל מידע משדות חוזרים בתוך שדות. לדוגמה, ההצהרה הבאה מחזירה את הרץ שהקיף את המסלול הכי מהר במירוץ ל-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 מפורש, או לדוגמה הבאה שבה נעשה שימוש ב-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` במערך

שאילתות בשדות מסוג `ARRAY` במבנה

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