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

ב-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_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 ערכים קטנים מ-0. אם רוצים לכלול את השורות האלה, משתמשים ב-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` שמקיימים תנאי מסוים