〜JOINとCROSS APPLYで広がるデータの世界〜
今回使用するテーブル
CREATE TABLE Orders (
OrderId INT PRIMARY KEY,
CustomerInfo JSON NOT NULL,
OrderDetails JSON NOT NULL,
CreatedAt DATETIME2 NOT NULL,
UpdatedAt DATETIME2 NOT NULL,
IsActive BIT NOT NULL,
OrderStatus NVARCHAR(50) NOT NULL,
TotalAmount DECIMAL(18,2) NOT NULL
);
CustomerInfo
Ex.
{
"customerId": 1,
"name": "佐藤 千代",
"email": "mikako17@gmail.com",
"address": {
"city": "立川市",
"prefecture": "山形県",
"postalCode": "588-1891",
"street": "280 松本 Street"
},
"phone": "080-6685-8712",
"registeredDate": "2023-03-08"
}
OrderDetails
{
"orderId": 1,
"orderDate": "2025-04-26",
"items": [
{
"productId": 6895,
"productName": "溝",
"category": "書籍",
"quantity": 6,
"unitPrice": 4242.76
},
{
"productId": 8825,
"productName": "シュガー",
"category": "衣類",
"quantity": 3,
"unitPrice": 14094.12
},
{
"productId": 3919,
"productName": "チーズ",
"category": "家具",
"quantity": 8,
"unitPrice": 2827.21
},
{
"productId": 6267,
"productName": "狐",
"category": "食品",
"quantity": 2,
"unitPrice": 6961.97
},
{
"productId": 6463,
"productName": "ヘア",
"category": "書籍",
"quantity": 10,
"unitPrice": 18969.8
},
{
"productId": 1818,
"productName": "デッド",
"category": "電化製品",
"quantity": 10,
"unitPrice": 8390.16
},
{
"productId": 7416,
"productName": "バナー",
"category": "食品",
"quantity": 10,
"unitPrice": 3360.59
}
],
"payment": {
"method": "代引き",
"status": "失敗",
"amount": 411486.04
},
"delivery": {
"carrier": "佐川",
"trackingNo": "e6f92c0d-c0fc-4da1-8333-c1122bed6306",
"expectedDate": "2025-03-28"
}
}
テーブル生成のSQL
クエリの例（category = 書籍）
SELECT
o.OrderId,
JSON_VALUE(o.CustomerInfo, '$.name') AS CustomerName,
i.productId,
i.productName,
i.category,
i.quantity,
i.unitPrice
FROM Orders o
CROSS APPLY OPENJSON(o.OrderDetails, '$.items')
WITH (
productId INT '$.productId',
productName NVARCHAR(100) '$.productName',
category NVARCHAR(50) '$.category',
quantity INT '$.quantity',
unitPrice DECIMAL(18,2) '$.unitPrice'
) AS i
WHERE i.category = N'書籍';
解説
Orders テーブルには category という物理列は存在しない
→ JSON の中にあるだけ。
OPENJSON(... WITH (...)) で JSON のキーを SQL の列に展開する必要がある。
ここで category NVARCHAR(50) '$.category' と指定すると、
JSON 内の "category": "書籍" を i.category という列で参照できるようになる。
その上で WHERE i.category = N'書籍' という条件が書ける。
OPENJSON 関数
1. そもそも JSON とは？
- JSON（JavaScript Object Notation）は、データを キーと値 のペアで表す書き方。
- 例えば注文データを JSON で書くとこんな感じ：
{
"orderId": 1,
"items": [
{"productId": 101, "name": "本", "price": 1200},
{"productId": 102, "name": "パン", "price": 300}
]
}
ここで問題になるのは、SQL Server の1つのセルに JSON をそのまま入れたら、普通の列みたいに検索できないこと。
2. OPENJSON の役割
OPENJSON は JSON をテーブルみたいに変換する関数。
JSON の配列やオブジェクトを 行と列に展開できる。
3. シンプルな使い方
DECLARE @json NVARCHAR(MAX) = N'[
{"productId":101,"name":"本","price":1200},
{"productId":102,"name":"パン","price":300}
]';
SELECT * FROM OPENJSON(@json);
結果はこんな感じ：
key value type
---- ----------------------------- ----
0 {"productId":101,"name":"本"...} 5
1 {"productId":102,"name":"パン"...} 5
つまり「配列の中身が行になって出てくる」んだね。
4. WITH 句で列にマッピング
さっきの結果はちょっと使いづらい。
そこで WITH 句 を使うと、欲しいカラムに変換できる。
SELECT *
FROM OPENJSON(@json)
WITH (
productId INT '$.productId',
name NVARCHAR(50) '$.name',
price DECIMAL(10,2) '$.price'
);
結果：
productId name price
--------- ---- -----
101 本 1200
102 パン 300
5. テーブルと組み合わせる（よく使う形）
Orders テーブルに JSON カラム OrderDetails がある場合：
SELECT o.OrderId, i.productId, i.name, i.price
FROM Orders o
CROSS APPLY OPENJSON(o.OrderDetails, '$.items')
WITH (
productId INT '$.productId',
name NVARCHAR(50) '$.name',
price DECIMAL(10,2) '$.price'
) AS i;
これで「注文1件 → 商品n件」を展開して、明細を取れる。
6. 覚えておきたいポイント
-
OPENJSONは JSON を行に変換してくれる -
WITH句を使うと きれいな列形式 にできる - よく一緒に使うのは
CROSS APPLY（注文ごとに JSON を展開） - JSON を まるで別テーブルのように扱える のが強み
CROSS APPLY
JOIN と CROSS APPLY の違い
🟦 普通の JOIN
たとえば「注文テーブル」と「顧客テーブル」があったとする。
Order.CustomerId = Customer.Id でつなぐ場合：
Orders テーブル Customers テーブル
+---------+----------+ +----+--------+
| OrderId | CustId | | Id | Name |
+---------+----------+ +----+--------+
| 1 | 101 | ---> |101 | 佐藤 |
| 2 | 102 | ---> |102 | 鈴木 |
| 3 | 101 | ---> |101 | 佐藤 |
+---------+----------+ +----+--------+
JOIN は 「2つのテーブルをキーでくっつける」 もの。
結果は「横に情報が増える」イメージ。
CROSS APPLY + OPENJSON
次に「注文テーブル」の中に JSON で商品配列 が入っているケース。
Orders テーブル
+---------+---------------------------------+
| OrderId | OrderDetails (JSON) |
+---------+---------------------------------+
| 1 | { items: [A, B, C] } |
| 2 | { items: [D, E] } |
+---------+---------------------------------+
このままだと「注文1は商品3つ」「注文2は商品2つ」という情報が ひとつのセル にまとまっていて使いにくい。
CROSS APPLY を使うと
SELECT o.OrderId, i.product
FROM Orders o
CROSS APPLY OPENJSON(o.OrderDetails, '$.items') WITH (product NVARCHAR(50) '$')
結果はこうなる：
+---------+---------+
| OrderId | product |
+---------+---------+
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | D |
| 2 | E |
+---------+---------+
1行の中に入っていた配列を、縦に展開してくれる のが CROSS APPLY。
つまり「JSON を子テーブルみたいに JOIN してる」感覚だね。
まとめ
- JOIN → テーブルとテーブルを「キー」でくっつける
- CROSS APPLY → 行ごとに「関数（OPENJSONなど）」を実行して、結果を展開してくっつける
JOIN は横に増やす、CROSS APPLY は縦に展開する。