📝 ドリル 03 — JSON 内の値で検索する (PostgreSQL `JSONB`)

問題

products テーブルから spec.brand が 'acme' の商品を id 順で抽出し、name: brand の形式で出力する。

ノートPC: acme
マウス: acme

driver で JSON 抽出のシンタックスが異なる:

driver	カラム型	brand 抽出
PostgreSQL	JSONB	spec->>'brand'
MySQL	JSON	JSON_UNQUOTE(JSON_EXTRACT(spec, '$.brand'))
SQLite	TEXT (JSON 文字列)	json_extract(spec, '$.brand')

期待される出力

ノートPC: acme
マウス: acme

採点

php scripts/grade.php topics/11-db/ch12-postgres-specific/drill/03-jsonb-or-json/

ヒント

• getenv('DOJO_DB_DRIVER') ?: 'sqlite' で分岐
• PostgreSQL の -> は JSON のまま 返し、->> は テキスト で返す。比較したいときは ->>
• SQLite の json_extract(col, '$.key') は組み込み関数 (3.38 以降は col -> '$.key' でも OK)
• MySQL の JSON_EXTRACT() は二重引用符付き値を返すので JSON_UNQUOTE() で剥がす
• 出力フォーマット: name: brand (コロンの後ろにスペース 1 つ)
• ORDER BY id

JSONB と JSON の違い (PostgreSQL)

観点	JSON	JSONB
内部表現	テキスト (入力そのまま)	バイナリ (正規化)
入力時の空白	保持	削除
キーの重複	保持	最後の値で上書き
順序	保持	失われる
インデックス	不可	GIN インデックス可
検索性能	遅い (パース必須)	速い
推奨	ログのような「原文保存」用途	検索・更新する JSON は JSONB

→ 実務では基本的に JSONB を選ぶ。

なぜ JSON 型を使うか

「商品ごとに任意のスペックを持つ」ような スキーマレス な要件で、

• 中間テーブルを作るほどでもない
• カラムを増やすほど明確でもない

ようなときに JSONB が便利。ただし「検索する属性が決まったら」普通のカラムに切り出した方が高速。

ノートPC: acme
マウス: acme

<?php

// TODO:
// 1) $driver = getenv('DOJO_DB_DRIVER') ?: 'sqlite';
// 2) dojo_db_connect() で PDO を取得
// 3) spec.brand が 'acme' の商品を id 順で抽出する
//    driver 別の JSON 抽出:
//      pgsql  : spec->>'brand'
//      mysql  : JSON_UNQUOTE(JSON_EXTRACT(spec, '$.brand'))
//      sqlite : json_extract(spec, '$.brand')
// 4) 出力フォーマット:
//      ノートPC: acme
//      マウス: acme

<?php

require __DIR__ . '/../../../../../scripts/shared/db-connect.php';

$driver = getenv('DOJO_DB_DRIVER') ?: 'sqlite';
$pdo = dojo_db_connect();

// driver 別に JSON から brand を抽出する式を組み立てる
$brandExpr = match ($driver) {
    'pgsql'  => "spec->>'brand'",
    'mysql'  => "JSON_UNQUOTE(JSON_EXTRACT(spec, '\$.brand'))",
    default  => "json_extract(spec, '\$.brand')",  // sqlite
};

$sql = "SELECT name, {$brandExpr} AS brand FROM products WHERE {$brandExpr} = 'acme' ORDER BY id";
$rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

foreach ($rows as $row) {
    echo $row['name'] . ': ' . $row['brand'] . "\n";
}