JSON データをクエリ

Oracle

-- Extract a scalar value with JSON_VALUE:
SELECT JSON_VALUE(data, '$.customer.city') AS city FROM orders;
-- Expand into rows with JSON_TABLE:
SELECT t.product, t.quantity
FROM orders s,
     JSON_TABLE(s.data, '$.items[*]'
       COLUMNS (product VARCHAR2(50) PATH '$.product',
                quantity NUMBER PATH '$.quantity')) t;

Oracle(12c+)は SQL/JSON パス式で JSON データをクエリする。JSON_VALUE は単一のスカラー値を、JSON_QUERY はオブジェクト/配列の断片を返し、JSON_TABLE は JSON をリレーショナルな行列構造に変換する。21c+ はネイティブの JSON データ型をサポートする。

PostgreSQL

-- jsonb operators:
SELECT data->'customer'->>'city' AS city FROM orders;
-- Containment (@>) and a GIN index:
CREATE INDEX ix ON orders USING GIN (data);
SELECT * FROM orders
WHERE data @> '{"status": "approved"}';

PostgreSQL の jsonb 型は豊富な演算子を提供する: ->(json オブジェクト/要素)、->>(テキスト値)、#>(パス)、@>(包含)、?(キーの存在)。GIN インデックスは @> と ? のクエリを高速化する。jsonb_path_query による SQL/JSON パスクエリも可能。

MySQL

-- JSON_EXTRACT or shorthand operators:
SELECT JSON_EXTRACT(data, '$.customer.city') AS city FROM orders;
-- ->> returns unquoted text:
SELECT data->>'$.customer.city' AS city FROM orders
WHERE data->>'$.status' = 'approved';

MySQL(5.7+)はネイティブの JSON 型を JSON_EXTRACT() でクエリする。-> 演算子は JSON_EXTRACT の、->> は JSON_UNQUOTE(JSON_EXTRACT()) の省略形。JSON フィールドに対する検索は生成列 + インデックスで高速化できる。

SQL Server

-- Extract a scalar value with JSON_VALUE:
SELECT JSON_VALUE(data, '$.customer.city') AS city FROM orders;
-- Expand into rows with OPENJSON:
SELECT k.product, k.quantity
FROM orders s
CROSS APPLY OPENJSON(s.data, '$.items')
  WITH (product NVARCHAR(50) '$.product', quantity INT '$.quantity') k;

SQL Server(2016+)は JSON を NVARCHAR 列に格納する。JSON_VALUE はスカラー値を、JSON_QUERY はオブジェクト/配列を返し、OPENJSON は JSON を表形式に変換する。ISJSON() で検証する。JSON パスに対する検索は計算列 + インデックスで高速化できる。

MongoDB

// MongoDB is natively a document (BSON/JSON) database.
// Access nested fields with dot notation:
db.orders.find({ "customer.city": "Ankara" })
// Array element and projection:
db.orders.find(
  { "items.product": "pen" },
  { "items.$": 1 }
)

MongoDB はすべてのデータをドキュメント(JSON に似た BSON)として格納するため、クエリはネイティブ。ドット表記でネストされたフィールドや配列要素にアクセスする。複雑な変換は Aggregation Pipeline で行い、インデックスによりネストされたフィールドを高速に検索できる。

Redis

# Requires the RedisJSON module (Redis Stack).
# Write a JSON document:
JSON.SET order:1 $ '{"customer":{"city":"Ankara"},"status":"approved"}'
# Read a value with JSONPath:
JSON.GET order:1 $.customer.city

コアの Redis には JSON 型がない。RedisJSON モジュール(Redis Stack)が JSON.SET/JSON.GET による JSONPath ベースの読み書きを提供する。RediSearch と組み合わせると JSON ドキュメントをインデックス化してクエリできる。モジュールがない場合、JSON は文字列として格納され、アプリケーションで解析する。

Elasticsearch

# Documents are already JSON; query fields directly:
GET orders/_search
{ "query": { "term": { "customer.city": "Ankara" } } }
# For nested arrays, use the nested type and a nested query:
GET orders/_search
{ "query": { "nested": { "path": "items",
  "query": { "match": { "items.product": "pen" } } } } }

Elasticsearch はドキュメントをネイティブに JSON として格納し、インデックス化する。ネストされたオブジェクトの配列で要素の境界を保つには、'nested' フィールド型と nested クエリを使用する。そうしないと object 型の配列はフラット化される(交差マッチングのリスク)。object と nested の区別は正確性にとって重要。

DBAtlas で開く →