MENU

SQLで配列データを処理・変換する方法｜PostgreSQLのJSONB・UNNEST

SQL

SQLで配列を扱うときは、DBMSのネイティブ配列型とJSON配列を区別することが重要です。本記事はPostgreSQLのJSONB配列を中心に、行への展開、型変換、結合、フィルタリング、順位付け、移動平均、インデックスの使い方を解説します。MySQLやSQL Serverでは関数と構文が異なります。

PostgreSQLの例は上から順に、同名テーブルがない検証用データベースで試す構成です。CREATE TABLE・INSERT・CREATE INDEX・VACUUMはデータや環境に変更を加えるため、本番へそのまま貼り付けないでください。以下のJSONは架空のデータで、id・score・amount・monthは記載した型へ変換できる値を前提にしています。

目次

配列データの基本概念とSQLでの扱い方

配列は要素に順序があるデータ構造です。PostgreSQLのinteger[]やtext[]は同一要素型のSQL配列ですが、JSON配列は数値・文字列・オブジェクト・nullなどを混在できます。SQL配列、JSON、XMLは異なる仕組みであり、XMLをJSON配列関数へそのまま渡すことはできません。

ネイティブ配列型と他DBMSとの違い

PostgreSQLのネイティブ配列を行へ展開する関数はunnestです。JSON配列用のjsonb_array_elementsとは使い分けます。通常のARRAY構築式は添字1始まりですが、PostgreSQLの配列は別の下限も持てるため、添字とWITH ORDINALITYの番号は同一とは限りません。

-- PostgreSQL: integer[]を行にする独立した例
SELECT e.value, e.pos
FROM unnest(ARRAY[10, 20, 30]::integer[])
  WITH ORDINALITY AS e(value, pos)
ORDER BY e.pos;

MySQL 8系ではJSON配列の表形式展開にJSON_TABLEを、SQL ServerではOPENJSONを使います。OPENJSONはデータベース互換性レベル130以上が必要です。本記事のJSONB、::integer、LATERAL、GINの例を、そのまま別DBMSで実行しないでください。

配列データの例

例えば、以下のようなJSON形式の配列データがあります。

[
  {"id": 1, "name": "Alice"},
  {"id": 2, "name": "Bob"},
  {"id": 3, "name": "Charlie"}
]

SQLでの基本的な配列データの取り扱い

JSON配列を格納する最初の例です。PostgreSQLのJSONB型を使い、最上位が配列であることをCHECK制約で確認します。この制約だけでは、各要素のidやnameの型・必須性までは検証しません。

CREATE TABLE array_documents (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL CHECK (jsonb_typeof(data) = 'array')
);
INSERT INTO array_documents (data) VALUES
('[
  {"id": 1, "name": "Alice"},
  {"id": 2, "name": "Bob"},
  {"id": 3, "name": "Charlie"}
]'::jsonb);

配列をテーブル形式に変換する方法

配列データをテーブル形式に変換することで、通常のSQLクエリを使ってデータを操作しやすくなります。ここでは、PostgreSQLを例に、JSON配列データをテーブル形式に変換する方法を紹介します。

配列を個々の行に展開する

PostgreSQLのjsonb_array_elementsは、JSON配列の要素を行として返します。元の行との対応をFROM句のLATERALで表し、WITH ORDINALITYで1始まりの位置番号を付けます。配列順の表示を保証するため、最終的なORDER BYも指定します。

SELECT e.value AS element
FROM array_documents AS d
CROSS JOIN LATERAL jsonb_array_elements(d.data)
  WITH ORDINALITY AS e(value, pos)
ORDER BY d.id, e.pos;

このクエリは、以下のような結果を返します。

 element
-----------------------------
 {"id": 1, "name": "Alice"}
 {"id": 2, "name": "Bob"}
 {"id": 3, "name": "Charlie"}

展開した配列データをテーブルに変換する

配列データを個々の行に展開した後、各要素をテーブルの列に変換します。->はJSONBのまま、->>はテキストとして値を返すため、数値として扱うidはintegerへ明示的に変換します。

SELECT
  (e.value->>'id')::integer AS id,
  e.value->>'name' AS name
FROM array_documents AS d
CROSS JOIN LATERAL jsonb_array_elements(d.data)
  WITH ORDINALITY AS e(value, pos)
ORDER BY d.id, e.pos;

このクエリは、以下のような結果を返します。

 id |  name
----+---------
 1  | Alice
 2  | Bob
 3  | Charlie

テーブルに挿入する

展開したデータを、JSON保存用とは別名のnormalized_usersへ挿入できます。これはデータを書き込む例です。idの重複・NULLやnameのNULLは制約違反となり、数値に変換できないidもエラーになるので、取り込み前に検証してください。同じINSERTを繰り返す例ではありません。

CREATE TABLE normalized_users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
INSERT INTO normalized_users (id, name)
SELECT
  (e.value->>'id')::integer,
  e.value->>'name'
FROM array_documents AS d
CROSS JOIN LATERAL jsonb_array_elements(d.data) AS e(value);

これにより、配列データが通常のリレーショナル形式のテーブルに変換され、標準的なSQL操作が可能になります。

JSONデータを利用した配列処理

JSONデータを利用することで、SQLでの配列処理がより柔軟かつ効率的になります。ここでは、PostgreSQLを例に、JSONデータを用いた配列処理の方法を紹介します。

JSONデータの挿入とクエリ

まず、JSONデータをテーブルに挿入し、そのデータをクエリする方法を見ていきます。

テーブルの作成とJSONデータの挿入

前節の「配列そのもの」とは別に、usersキーの中に配列を持つオブジェクトをuser_documentsへ保存します。以降の通常例では、usersが配列であり、要素がidとnameを持つオブジェクトであることを前提にします。

CREATE TABLE user_documents (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL
);
INSERT INTO user_documents (data) VALUES
('{"users": [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]}'::jsonb);

JSONデータのクエリ

挿入したJSONデータをクエリして、必要な情報を取得する方法を示します。例えば、ユーザー名を取得する場合、以下のクエリを使用します。

SELECT
  id AS document_id,
  data->'users'->0->>'name' AS first_user_name
FROM user_documents
ORDER BY id;

このクエリは、配列内の最初のユーザー名Aliceを取得します。JSON配列の添字は0始まりです。WITH ORDINALITYの1始まりの位置番号とは異なります。キーや要素が存在しない場合、これらの抽出演算子はSQL NULLを返します。

配列の各要素にアクセスする

配列の各要素にアクセスして操作するために、 jsonb_array_elements 関数を使用します。

SELECT d.id AS document_id, e.pos, e.value AS user_element
FROM user_documents AS d
CROSS JOIN LATERAL jsonb_array_elements(d.data->'users')
  WITH ORDINALITY AS e(value, pos)
ORDER BY d.id, e.pos;

このクエリは、各ユーザーを個別の行として返します。

各要素のプロパティにアクセス

展開した各要素のプロパティにアクセスする方法を示します。

SELECT
  d.id AS document_id,
  (e.value->>'id')::integer AS user_id,
  e.value->>'name' AS name
FROM user_documents AS d
CROSS JOIN LATERAL jsonb_array_elements(d.data->'users')
  WITH ORDINALITY AS e(value, pos)
ORDER BY d.id, e.pos;

このクエリは、各ユーザーのIDと名前を取得します。

JSON関数の活用

PostgreSQLには、JSONデータを操作するための様々な関数が用意されています。例えば、配列の長さを取得するには jsonb_array_length 関数を使用します。

SELECT id AS document_id,
       jsonb_array_length(data->'users') AS number_of_users
FROM user_documents
ORDER BY id;

このクエリは、ユーザー配列の長さ3を返します。空配列なら0ですが、JSONのnullやオブジェクトなど、配列でない値を渡すとエラーになります。SQL NULLとJSONのnullも区別してください。

JSONデータを利用することで、配列の柔軟な操作が可能となり、データの取り扱いがより効率的になります。

空配列・欠損キー・JSON nullを扱う

CROSS JOIN LATERALでは空配列が0行になるため、その親ドキュメントも結果から消えます。親を残すならLEFT JOIN LATERALを使います。配列以外の入力はエラーになるので、次は配列以外を空配列として扱う方針を明示した例です。不正な入力を拒否すべき業務では、黙って置き換えず取り込み時にエラーとして記録してください。

SELECT d.id AS document_id, e.pos, e.value
FROM user_documents AS d
LEFT JOIN LATERAL jsonb_array_elements(
  CASE WHEN jsonb_typeof(d.data->'users') = 'array'
       THEN d.data->'users'
       ELSE '[]'::jsonb
  END
) WITH ORDINALITY AS e(value, pos) ON true
ORDER BY d.id, e.pos;

usersが空配列、欠損、JSON null、オブジェクトなどの場合、この方針では親を1行残し、e.posとe.valueがSQL NULLになります。配列内の要素としてのJSON nullは別で、位置番号を持つ1要素です。単にCOALESCEで配列を補うだけでは、SQL NULLではないJSON nullやオブジェクトは防げません。

順序を保ってJSON配列へ戻す

展開後の行をJSON配列へ戻す場合は、jsonb_aggの内部でORDER BYを指定します。LEFT JOINが補った空行は位置番号で除外し、要素が0個なら空配列にします。以下は通常例のようにusersが配列である場合のクエリです。

SELECT d.id AS document_id,
       COALESCE(
         jsonb_agg(e.value ORDER BY e.pos)
           FILTER (WHERE e.pos IS NOT NULL),
         '[]'::jsonb
       ) AS users
FROM user_documents AS d
LEFT JOIN LATERAL jsonb_array_elements(d.data->'users')
  WITH ORDINALITY AS e(value, pos) ON true
GROUP BY d.id
ORDER BY d.id;

配列データの結合とフィルタリング

配列データを結合およびフィルタリングすることで、必要な情報を抽出しやすくなります。ここでは、PostgreSQLを例に、配列データの結合とフィルタリングの方法を解説します。

配列データの結合

複数のJSON配列データを結合することで、異なる配列間の関連情報をまとめることができます。以下のクエリは、複数のJSON配列を結合する方法を示します。

例：ユーザーと注文データの結合

ユーザーデータは前節のuser_documentsを使い、注文用のorder_documentsだけを追加します。ここではユーザーIDが全ドキュメントを通じて一意であると仮定します。テナントや取り込みバッチごとに同じIDを使う場合は、その識別子も結合条件に必要です。

-- user_documentsは前の節で作成済み
CREATE TABLE order_documents (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL
);
INSERT INTO order_documents (data) VALUES
('{"orders": [{"user_id": 1, "product": "Laptop"}, {"user_id": 2, "product": "Tablet"}, {"user_id": 3, "product": "Smartphone"}]}'::jsonb);

次に、ユーザーデータと注文データを結合するクエリを実行します。

WITH user_rows AS (
  SELECT u.id, u.name
  FROM user_documents AS d
  CROSS JOIN LATERAL jsonb_to_recordset(d.data->'users')
    AS u(id integer, name text)
), order_rows AS (
  SELECT o.user_id, o.product
  FROM order_documents AS d
  CROSS JOIN LATERAL jsonb_to_recordset(d.data->'orders')
    AS o(user_id integer, product text)
)
SELECT u.name AS user_name, o.product
FROM user_rows AS u
INNER JOIN order_rows AS o ON u.id = o.user_id
ORDER BY u.id, o.product;

このクエリはAliceとLaptop、BobとTablet、CharlieとSmartphoneを対応付けます。jsonb_to_recordsetはオブジェクトの配列を指定した列型へ展開します。欠けた列はNULLになり、変換できない値はエラーです。INNER JOINなので注文のないユーザーは返らず、複数注文があれば複数行になります。全ユーザーを残す場合はLEFT JOINに変更します。

配列データのフィルタリング

配列データをフィルタリングして、特定の条件に一致する要素のみを抽出する方法を説明します。

例：特定のユーザーをフィルタリング

以下のクエリは、名前が “Alice” のユーザーをフィルタリングして取得します。

SELECT (e.value->>'id')::integer AS id,
       e.value->>'name' AS name
FROM user_documents AS d
CROSS JOIN LATERAL jsonb_array_elements(d.data->'users')
  WITH ORDINALITY AS e(value, pos)
WHERE e.value->>'name' = 'Alice'
ORDER BY d.id, e.pos;

このクエリは、名前が “Alice” のユーザーのみを返します。

例：特定の製品を注文したユーザーをフィルタリング

以下のクエリは、”Laptop” を注文したユーザーをフィルタリングして取得します。

WITH user_rows AS (
  SELECT u.id, u.name
  FROM user_documents AS d
  CROSS JOIN LATERAL jsonb_to_recordset(d.data->'users')
    AS u(id integer, name text)
), order_rows AS (
  SELECT o.user_id, o.product
  FROM order_documents AS d
  CROSS JOIN LATERAL jsonb_to_recordset(d.data->'orders')
    AS o(user_id integer, product text)
)
SELECT u.name AS user_name, o.product
FROM user_rows AS u
INNER JOIN order_rows AS o ON u.id = o.user_id
WHERE o.product = 'Laptop'
ORDER BY u.id, o.product;

このクエリは、”Laptop” を注文したユーザーとその注文情報を返します。

配列データの結合とフィルタリングを活用することで、複雑なデータ操作が可能となり、必要な情報を効率的に抽出できます。

ウィンドウ関数を用いた高度な配列処理

ウィンドウ関数を使用すると、配列データに対して高度な分析や集計を行うことができます。ここでは、PostgreSQLを例に、ウィンドウ関数を用いた配列データの処理方法を紹介します。

ウィンドウ関数の基本

ウィンドウ関数は、特定の範囲内の行に対して計算を行う関数です。通常の集計関数と異なり、行をグループ化せずに計算結果を取得できるため、データの詳細な分析に適しています。

例：配列データの順位付け

ユーザーのスコアを含む配列データをテーブルに挿入し、そのスコアに基づいて順位付けを行います。

CREATE TABLE user_scores (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO user_scores (data) VALUES
('{"users": [{"id": 1, "name": "Alice", "score": 85}, {"id": 2, "name": "Bob", "score": 90}, {"id": 3, "name": "Charlie", "score": 75}]}');

次に、ユーザーのスコアに基づいて順位を計算するクエリを実行します。

SELECT
  s.id AS document_id,
  e.value->>'name' AS name,
  (e.value->>'score')::integer AS score,
  RANK() OVER (
    PARTITION BY s.id
    ORDER BY (e.value->>'score')::integer DESC NULLS LAST
  ) AS rank
FROM user_scores AS s
CROSS JOIN LATERAL jsonb_array_elements(s.data->'users')
  WITH ORDINALITY AS e(value, pos)
ORDER BY s.id, score DESC NULLS LAST, e.pos;

各ドキュメントを1つの順位表として、スコア降順でRANKを計算します。同点は同順位となり、その次の順位には飛びが生じます。PARTITION BYを省くと全ドキュメントを通した順位になります。ウィンドウ内のORDER BYだけでは表示順を保証しないため、外側にもORDER BYを指定しています。

 document_id | name    | score | rank
-------------+---------+-------+-----
 1           | Bob     | 90    | 1
 1           | Alice   | 85    | 2
 1           | Charlie | 75    | 3

例：移動平均の計算

ウィンドウ関数を用いて、配列データの移動平均を計算することもできます。以下は各ドキュメント内に月1行ずつ売上がある例です。January、Februaryなどの月名文字列順は時系列ではないため、年を含むISO日付を保存し、date型へ変換して並べます。

CREATE TABLE monthly_sales (
  id SERIAL PRIMARY KEY,
  data JSONB
);
INSERT INTO monthly_sales (data) VALUES
('{"sales": [{"month": "2026-01-01", "amount": 100}, {"month": "2026-02-01", "amount": 200}, {"month": "2026-03-01", "amount": 150}, {"month": "2026-04-01", "amount": 300}]}'::jsonb);

次に、移動平均を計算するクエリを実行します。

SELECT
  s.id AS document_id,
  (e.value->>'month')::date AS month,
  (e.value->>'amount')::numeric AS amount,
  ROUND(AVG((e.value->>'amount')::numeric) OVER (
    PARTITION BY s.id
    ORDER BY (e.value->>'month')::date, e.pos
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ), 2) AS moving_avg
FROM monthly_sales AS s
CROSS JOIN LATERAL jsonb_array_elements(s.data->'sales')
  WITH ORDINALITY AS e(value, pos)
ORDER BY s.id, month, e.pos;

現在行と前後1行の平均を返し、小数点以下2桁に丸めます。端の月は存在する2行だけの平均です。これは前後を含む中心移動平均であり、将来の月を含めない過去3行平均ならROWS BETWEEN 2 PRECEDING AND CURRENT ROWにします。

 document_id | month      | amount | moving_avg
-------------+------------+--------+-----------
 1           | 2026-01-01 | 100    | 150.00
 1           | 2026-02-01 | 200    | 150.00
 1           | 2026-03-01 | 150    | 216.67
 1           | 2026-04-01 | 300    | 225.00

ROWSは「行数」で範囲を決めるので、欠けた月や同じ月の複数行があると「連続する3か月」とは一致しません。月単位の集計とカレンダー補完を先に行い、欠損を0にするか不明値にするかを決めてください。AVGはNULLの金額を平均の分母に含めません。

配列データのパフォーマンス最適化

配列データを効率的に処理するためには、パフォーマンス最適化が重要です。ここでは、SQLで配列データを最適に処理するためのベストプラクティスと技術を紹介します。

インデックスの活用

JSONBの包含条件@>などにはGINインデックスを使える場合があります。ただし、jsonb_array_elementsで展開した後の全処理が自動的に高速化するわけではありません。以下ではドキュメントに対する包含条件と、展開した要素に対する条件を両方指定します。

-- インデックスの作成は検証環境で効果と書き込みコストを確認する
CREATE INDEX idx_user_documents_data
  ON user_documents USING GIN (data);

SELECT d.id AS document_id, e.value->>'name' AS name
FROM user_documents AS d
CROSS JOIN LATERAL jsonb_array_elements(d.data->'users')
  WITH ORDINALITY AS e(value, pos)
WHERE d.data @> '{"users": [{"name": "Alice"}]}'::jsonb
  AND e.value->>'name' = 'Alice'
ORDER BY d.id, e.pos;

包含条件だけでは、Aliceを含むドキュメント内のBobやCharlieも展開されます。そのため要素側にも名前の条件が必要です。GINを実際に使うかはデータ量や選択率などによって決まります。実行計画と読み書き両方のコストを確認してください。

不要なデータの除去

取得列を絞ると、返却データ量やアプリケーション側の処理量を減らせます。ただし、SELECTする列を減らすだけでJSON配列の展開自体が不要になるわけではありません。次はidがSQL NULLでない要素の必要列だけを取得する例です。

SELECT (e.value->>'id')::integer AS id,
       e.value->>'name' AS name
FROM user_documents AS d
CROSS JOIN LATERAL jsonb_array_elements(d.data->'users')
  WITH ORDINALITY AS e(value, pos)
WHERE e.value->>'id' IS NOT NULL
ORDER BY d.id, e.pos;

このSELECTは保存済みのJSONを削除・変更しません。また、idがNULLでないこととintegerに変換できることは別です。空文字や不正な数値文字列は取り込み時に検証してください。

バルクインサートの利用

複数行のINSERTをまとめると、1行ずつ送信する場合の往復回数を減らせます。次は複数ドキュメントを追加する例です。巨大なSQL文を無制限に作るのではなく、入力サイズ、トランザクション、失敗時の再実行単位も決めます。

INSERT INTO user_documents (data) VALUES
('{"users": [{"id": 4, "name": "David"}, {"id": 5, "name": "Eva"}]}'::jsonb),
('{"users": [{"id": 6, "name": "Frank"}, {"id": 7, "name": "Grace"}]}'::jsonb);

このように、一度に複数のレコードを挿入することで、挿入処理を効率化します。

定期的なVACUUMとANALYZEの実行

PostgreSQLでは通常、既定で有効なautovacuumがVACUUMとANALYZEを自動実行します。まず動作状況を確認し、大量更新や取り込み後の統計など、必要性を判断して保守します。通常のVACUUMは不要領域を再利用可能にしますが、テーブルファイル全体を常に縮小してOSへ返す処理ではありません。

-- 管理者が必要性・負荷を判断して行う保守の例
-- トランザクションブロック内では実行しない
VACUUM (ANALYZE) user_documents;

ANALYZEによる統計はプランナーの判断材料になりますが、最適な実行計画を常に保証するものではありません。VACUUMは権限・I/O負荷・実行場所を確認する管理作業であり、検索するたびに実行する必要はありません。

適切なデータ型の選択

JSONBは検索・加工やインデックスに向く一方、入力時には変換コストがあります。JSON型は入力テキストの空白やオブジェクトのキー順などを保持しますが、JSONBはそれらを保持せず、重複キーも最後の値にまとめます。「常にJSONBが速い」ではなく用途で選びます。頻繁な結合・制約・要素更新が中心なら、通常の子テーブルへ正規化する設計も検討してください。

並列処理の活用

PostgreSQLは条件が整えば並列クエリを選択しますが、max_parallel_workers_per_gatherを4にするだけで4ワーカー実行や高速化が保証されるわけではありません。利用可能なワーカー、プランのコスト、並列安全性などにも依存します。まず設定を変えず、EXPLAINで計画を確認します。

-- 実行せずに推定実行計画を確認する例
EXPLAIN
SELECT COUNT(*)
FROM user_documents
WHERE data @> '{"users": [{"name": "Alice"}]}'::jsonb;

計画にGatherやGather Mergeがあるかを確認します。小さいサンプルでは並列計画にならなくても不自然ではありません。EXPLAIN ANALYZEは実際にクエリを実行するため、負荷や対象を確認せず本番で使わないでください。

最適化は、正しい結果と必要なデータ粒度を確保したうえで、対象データに近い検証環境の実行計画と計測結果に基づいて行います。

まとめ

PostgreSQLでは、SQL配列をunnest、JSONB配列をjsonb_array_elementsやjsonb_to_recordsetで行へ展開できます。配列の順序、空配列、JSON null、数値・日付の型、親ドキュメントとの対応を明確にしてから、結合やウィンドウ関数を適用してください。GINは対応する演算子と組み合わせ、保守や並列化は実行計画と負荷を確認して判断します。DBMSごとの構文を混同せず、正しい結果を確認してから効率化することが大切です。

参考資料

PostgreSQL: JSON関数と演算子 / 配列型 / 配列関数 / LATERAL・WITH ORDINALITY / 集約関数 / ウィンドウ式

PostgreSQL: JSON型とインデックス / VACUUM・ANALYZE / 並列クエリ / EXPLAIN / MySQL: JSON_TABLE / SQL Server: OPENJSON

SQL
データ処理 SQL 変換 配列

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次