配列の操作

Cloud Spanner SQL でいう配列とは、ゼロ個以上の同じデータ型の値で構成する順序付きリストです。INT64 のような簡単なデータ型で構成した配列や、STRUCT などの複雑なデータ型で構成した配列などを作成できます。現時点では ARRAY データ型はこの例外です。配列の配列はサポートされていません。

Cloud Spanner SQL では、配列のリテラルを作成し、ARRAY 関数を使用してサブクエリから配列を作成したうえで、ARRAY_AGG 関数を使用して配列に値を集約できます。

ARRAY_CONCAT() のような関数を使用して複数の配列を結合し、ARRAY_TO_STRING() を使用して配列を文字列に変換できます。

配列の作成

配列リテラルの使用

Cloud Spanner SQL では括弧([])を使用して配列リテラルを作成できます。配列の各要素はカンマで区切って記述します。

SELECT [1, 2, 3] as numbers;

SELECT ["apple", "pear", "orange"] as fruit;

SELECT [true, false, true] as booleans;

互換性のある型を使用した式から配列を作成することもできます。例:

SELECT [a, b, c]
FROM
  (SELECT 5 AS a,
          37 AS b,
          406 AS c);

SELECT [a, b, c]
FROM
  (SELECT CAST(5 AS INT64) AS a,
          CAST(37 AS FLOAT64) AS b,
          406 AS c);

2 番目の例では、INT64 を返す式、FLOAT64 を返す式、およびリテラルを宣言する式という 3 つの式を使用しています。これら 3 つの式はすべて FLOAT64 をスーパータイプとして共有するため、上記の式は正常に機能します。

配列に特定のデータ型を宣言するには、山かっこ(<>)を使用します。例:

SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;

INT64STRING など大半のデータ型の配列では、これらのデータ型を先に宣言しておく必要はありません。

SELECT [1, 2, 3] as numbers;

ARRAY<type>[] を使用すると、特定の型の空の配列を書き込むことができます。また、[] を使用すると、型のない空の配列を書き込むことができます。この場合、Cloud Spanner SQL は周囲のコンテキストから配列の型を推測します。Cloud Spanner SQL が型を推測できない場合、デフォルトの型 ARRAY<INT64> が使用されます。

生成された値の使用

生成された値を使用して ARRAY を作成することもできます。

整数の配列の生成

GENERATE_ARRAY は、開始値と終了値、ステップ値で構成される配列を生成します。たとえば、次のクエリは、11〜33 のすべての奇数の整数で構成された配列を生成します。

SELECT GENERATE_ARRAY(11, 33, 2) AS odds;

+--------------------------------------------------+
| odds                                             |
+--------------------------------------------------+
| [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] |
+--------------------------------------------------+

負のステップ値を指定して、降順の値で構成された配列を生成することもできます。

SELECT GENERATE_ARRAY(21, 14, -1) AS countdown;

+----------------------------------+
| countdown                        |
+----------------------------------+
| [21, 20, 19, 18, 17, 16, 15, 14] |
+----------------------------------+

日付の配列の生成

GENERATE_DATE_ARRAY は、開始および終了の DATE とステップ INTERVAL で構成された DATE の配列を生成します。

GENERATE_DATE_ARRAY を使用して DATE 値のセットを生成できます。たとえば、次のクエリは、現在の DATE と以降の 1 WEEK ごとの DATE を、後に指定されている DATE まで返します。

SELECT
  GENERATE_DATE_ARRAY('2017-11-21', '2017-12-31', INTERVAL 1 WEEK)
    AS date_array;
+--------------------------------------------------------------------------+
| date_array                                                               |
+--------------------------------------------------------------------------+
| [2017-11-21, 2017-11-28, 2017-12-05, 2017-12-12, 2017-12-19, 2017-12-26] |
+--------------------------------------------------------------------------+

配列の要素へのアクセス

以下のテーブル sequences を例に取り上げます。

CREATE TABLE sequences (
  id INT64 NOT NULL,
  some_numbers ARRAY<INT64> NOT NULL
) PRIMARY KEY(id);

テーブルに次の行があるとします。

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

このテーブルには、ARRAY データ型の列 some_numbers があります。この列にある配列の要素にアクセスするには、使用するインデックスのタイプを指定する必要があります。ゼロから始まるインデックスであれば OFFSET、1 から始まるインデックスであれば ORDINAL を指定します。

このクエリは OFFSET()ORDINAL() の使用方法を示しています。

SELECT some_numbers,
       some_numbers[OFFSET(1)] AS offset_1,
       some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;

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

長さの確認

ARRAY_LENGTH() 関数は配列の長さを返します。

このクエリの例では、上記の sequences テーブルと同じ定義であり、次のサンプル行があるとします。

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 演算子を使用します。UNNESTARRAY を取得し、ARRAY に含まれる各要素を 1 行にしたテーブルを返します。

UNNESTARRAY 要素の順序を無視するため、テーブルで順序の復元が必要になる場合があります。その場合、オプションの 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 の列全体をフラット化するには、CROSS JOIN を使用して、ARRAY 列を含むテーブルとその ARRAY 列の UNNEST 出力を結合します。

これは相関クロス結合です。UNNEST 演算子は、以前に FROM 句に指定したソーステーブルの各行から ARRAY の列を参照します。ソーステーブルの N 行ごとに、UNNEST は、ARRAY の行 NARRAY 要素を含む一連の行にフラット化し、CROSS JOIN がこの新しい一連の行をソーステーブルの単一行 N と結合します。

次の例では、UNNEST を使用して配列の列内の各要素の行を返しています。CROSS JOIN のため、id 列の id の値は、各数値を含む行の sequences 内での値を表します。

SELECT id, flattened_numbers FROM sequences
CROSS 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 |
+------+-------------------+

ネストされた配列のクエリ

テーブルに STRUCTARRAY が含まれている場合に、その STRUCT のフィールドをクエリするには、ARRAY をフラット化します。また、STRUCT 値の ARRAY 型フィールドをフラット化することもできます。

ARRAY 内の STRUCT 要素のクエリ

次の例では、UNNESTCROSS JOIN を使用して、STRUCTARRAY をフラット化します。

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

+------+-------------+-----------------------+
| race | name        | splits                |
+------+-------------+-----------------------+
| 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]   |
+------+-------------+-----------------------+

繰り返しフィールドから特定の情報を検索できます。たとえば、次のクエリを実行すると、800m 競走で最速の参加者が返されます。

次の例では、配列のフラット化を行っていませんが、よく使われる方法で繰り返しフィールドから情報を取得しています。

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

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

STRUCT 内の ARRAY 型フィールドのクエリ

ネストされた繰り返しフィールドの情報も取得できます。たとえば、次の文では 800m 競走で最速ラップの参加者が返されます。

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

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

前のクエリでは、カンマ演算子(,)で CROSS JOIN を暗黙的に実行していますが、次の例では CROSS JOIN を明示的に実行しています。

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

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

CROSS JOIN を使用して配列をフラット化すると、空の配列または NULL 配列を含む行は除外されます。このような行を入れる場合には LEFT JOIN を使用します。

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

+-------------+--------------------+
| name        | duration           |
+-------------+--------------------+
| Murphy      | 102.9              |
| Rudisha     | 102.19999999999999 |
| David       | NULL               |
| Rotich      | 103.6              |
| Makhloufi   | 102.6              |
| Bosse       | 103.4              |
| Kipketer    | 106                |
| Nathan      | NULL               |
| Lewandowski | 104.2              |
+-------------+--------------------+

サブクエリからの配列の作成

配列の操作で多用する作業として、サブクエリを配列に変換する操作があります。Cloud Spanner SQL では、

ARRAY() 関数を使用して、この操作を行うことができます。

たとえば、sequences テーブルで以下のオペレーションを実行するとします。

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

この例では sequence というテーブルから開始します。このテーブルには、ARRAY<INT64> 型で構成される列 some_numbers があります。

このクエリ自体にサブクエリがあります。このサブクエリは、some_numbers 列の各行を選択し、UNNEST を使用して配列を一連の行として返します。次に各値に 2 を乗算し、ARRAY() 演算子を使用してこれらの行を配列に再結合します。

配列のフィルタ処理

次の例では、ARRAY() 演算子のサブクエリで WHERE 句を使用して、返される行にフィルタを適用します。

注: 次の例では、結果の行が順序どおりになっていません。

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

3 番目の行は空の配列となっています。これは、この配列に対応する元の行([5, 10])がこのフィルタの要件である x < 5 を満たしていないからです。

SELECT DISTINCT を使用して、配列内の一意の要素のみが返されるようにすることもできます。

SELECT ARRAY(SELECT DISTINCT x
             FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM sequences
WHERE id = 1;

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

また、IN キーワードを使用して配列の行にフィルタを適用することもできます。このキーワードを使用すると、配列の要素が特定の値に一致するかどうかが判断されて、一致する場合はその配列を含む行にフィルタが適用されます。

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

ここでも、3 番目の行には空の配列があります。この配列に対応する元の行([5, 10])に 2 がないからです。

配列のスキャン

配列に特定の値が含まれているかどうかを確認するには、IN 演算子を UNNEST と併用します。条件に一致する値が配列に含まれているかどうかを確認するには、EXISTS 関数を UNNEST と併用します。

特定の値のスキャン

配列の特定の値をスキャンするには、IN 演算子と UNNEST を使用します。

次の例では、配列に数値 2 が含まれている場合に true を返します。

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

+----------------+
| contains_value |
+----------------+
| true           |
+----------------+

配列の列に特定の値が含まれているテーブルの行を返すには、WHERE 句を使用して IN UNNEST の結果をフィルタリングします。

次の例では、配列の列に値 2 が含まれている行の id 値を返します。

SELECT id AS matching_rows
FROM (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)
WHERE 2 IN UNNEST(some_numbers)
ORDER BY matching_rows;

+---------------+
| matching_rows |
+---------------+
| 1             |
| 2             |
+---------------+

条件を満たす値のスキャン

条件に一致する値が配列に含まれているかどうかスキャンするには、UNNEST を使用して配列内の要素テーブルを取得します。次に、サブクエリで WHERE を使用して、取得したテーブルをフィルタリングします。最後に EXISTS を使用して、フィルタリングされたテーブルに任意の行が含まれているかどうかを確認します。

次の例では、配列の列に 5 より大きい値を含む行の id 値を返します。

SELECT id AS matching_rows
FROM (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)
WHERE EXISTS (SELECT *
              FROM UNNEST(some_numbers) AS x
              WHERE x > 5);

+---------------+
| matching_rows |
+---------------+
| 2             |
| 3             |
+---------------+

条件を満たす STRUCT フィールド値のスキャン

条件に一致するフィールドの STRUCT 配列を検索するには、UNNEST を使用して、各 STRUCT フィールドの列を含むテーブルを取得し、WHERE EXISTS を使用して条件に一致しない行をフィルタリングします。

次の例では、フィールド b の値が 3 より大きい STRUCT を含む配列の行を返します。

SELECT id AS matching_rows
FROM (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)
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(some_numbers)
  WHERE b > 3
);

+---------------+
| matching_rows |
+---------------+
| 2             |
| 3             |
+---------------+

配列と集約

Cloud Spanner SQL では、ARRAY_AGG() を使用して配列に値を集約できます。

以下のテーブル fruits を例に取り上げます。

CREATE TABLE fruits (
  fruit STRING(MAX),
  id INT64 NOT NULL
) PRIMARY KEY(id);

このテーブルには次のデータがあるとします。

+----+--------------+
| id | fruit        |
+----+--------------+
| 1  | "apple"      |
| 2  | "pear"       |
| 3  | "banana"     |
+----+--------------+

このクエリは ARRAY_AGG() の使用方法を示しています。

SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM fruits;

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

ARRAY_AGG() によって返される配列は、この関数では値が連結される順序が保証されていないため不定の順序になります。

配列の要素に SUM() などの集約関数を適用することもできます。たとえば、次のクエリは、sequences テーブルの行ごとに配列要素の合計を返します。

SELECT some_numbers,
  (SELECT SUM(x)
   FROM UNNEST(s.some_numbers) x) AS sums
FROM sequences s;

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

配列から文字列への変換

ARRAY_TO_STRING() 関数を使用すると、ARRAY<STRING> を単一の STRING 値に変換したり、ARRAY<BYTES> を単一の BYTES 値に変換したりできます。この場合、変換後の値は、配列の要素が順序付けられて連結された形になります。

2 番目の引数は、関数が出力を生成するために入力項目間に挿入する区切り文字です。この 2 番目の引数は、最初の引数の要素と同じ型でなければなりません。

例:

SELECT ARRAY_TO_STRING(["Hello", "World"], " ") AS greetings;

+-------------+
| greetings   |
+-------------+
| Hello World |
+-------------+

オプションの 3 番目の引数は、入力配列に含まれる 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]                               |
+--------------------------------------------------+

配列の配列の作成

Cloud Spanner SQL では、配列の配列を直接作成することはできません。代わりの方法として、それぞれが ARRAY 型のフィールドを持つ構造体の配列を作成します。以下の points テーブルはこの方法を示しています。

CREATE TABLE points (
  point ARRAY<INT64>,
  id INT64 NOT NULL
) PRIMARY KEY(id);

テーブルに次の行があるとします。

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

ここで、points テーブルの各 point で構成する配列を作成するとします。この処理を行うため、各行から返される配列を STRUCT にラップします。

SELECT ARRAY(
  SELECT STRUCT(point)
  FROM points)
  AS coordinates;

+--------------+
| coordinates  |
+--------------+
| point: [1,5] |
| point: [2,8] |
| point: [3,7] |
| point: [4,1] |
| point: [5,7] |
+--------------+
このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

Cloud Spanner のドキュメント