★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

8.14. JSONデータ型

JSONデータ型はJSON(JavaScript Object Notation)データを格納するためのものです。JSONの仕様はRFC 7159に定義されています。 このようなデータは、text型として格納することもできますが、JSONデータ型は、それぞれ格納された値がJSONルールに従って有効に施行されるという利点があります。 これらのデータ型に格納されたデータのために利用可能な各種JSON固有の関数と演算子もあります。 9.15を参照してください。

JSONデータ型にはjson型とjsonb型という2種類のデータ型があります。 それらは ほとんど 同一の入力値セットを受け入れます。現実的に主要な違いは効率です。 jsonデータ型は入力テキストの正確なコピーで格納し、処理関数を実行するたびに再解析する必要があります。 jsonbデータ型では、分解されたバイナリ形式で格納されます。 格納するときには変換のオーバーヘッドのため少し遅くなりますが、処理するときには、全く再解析が必要とされないので大幅に高速化されます。 また jsonb型の重要な利点はインデックスをサポートしていることです。

json型は入力値のコピーを格納しているので、意味的に重要でないトークン間の空白だけでなく、JSONオブジェクト内のキーの順序も維持します。 また、JSONオブジェクト内に同じキーと値が複数含まれていてもすべてのキー/値のペアが保持されます。(この処理関数は最後の値1つを処理させるようすれば済みます。) これとは対照的に、 jsonbは空白を保持しません。オブジェクトキーの順序を保持せず、重複したオブジェクトキーを保持しません。重複キーを入力で指定された場合は、最後の値が保持されます。

一般的に、ほとんどのアプリケーションではJSONデータ型としてjsonb型のほうが望ましいでしょう。ただし、オブジェクトキーを従来のような順序であることを仮定する非常に特殊なニーズが存在するような場合は除きます。

PostgreSQLはデータベースごとに1つの文字セットエンコーディングのみが許可されています。従ってデータベースエンコーディングがUTF8でない限り、厳密にはJSON型がJSON仕様に準拠することはできません。 データベースのエンコーディングで表現できない文字を直接含めようとすると失敗します。逆に、UTF8で許可されずにデータベースのエンコーディングで許可される文字が許されてしまいます。

RFC 7159 では、JSON文字列はUnicodeエスケープシーケンス \uXXXX を許可するように記述されています。 json型の入力関数は、データベースエンコーディング方式に関係なくUnicodeエスケープが許可されています。それは、構文上の正しさ(つまり\uに続けて16進数が4桁)だけをチェックしています。 しかし、jsonbの入力関数はより厳しくなります。 データベースのエンコードがUTF8でない限り、Unicodeは非ASCII文字(U+007Fより上位の文字)に対してエスケープを禁止します。 jsonb型は\u0000も許可しません。(なぜならPostgreSQLtext型で表現できないためです)。 また、Unicode基本多言語面以外の文字はUnicodeのサロゲートペアに直すことが要求されています。 有効なUnicodeエスケープは、同等のASCIIまたはUTF8文字に変換されて格納されます。これはサロゲートペアを単一の文字に変換する処理も含まれています。

注記

9.15で説明されているJSONの処理関数の多くは、Unicodeエスケープを通常の文字に変換します。 そして、それらの入力はjsonbでないjsonの場合でも記載された同じ種類のエラーになります。 json入力関数は歴史的経緯によりこれらのチェックをしないため、非UTF8のデータベースエンコーディングで、JSON Unicodeエスケープされた文字を単に格納(処理を必要としない場合)できてしまいます。 一般的には、可能であれば非UTF8のデータベースエンコーディングではUnicodeのJSONエスケープを混在させないようにすることをお勧めします。

原文のJSONがjsonb型に変換されるときには、RFC 7159に記載されているプリミティブ型を表 8.23に記されているようにPostgreSQLのネイティブな型に変換されます。 そのため、jsonbデータ型には、json型になく、また理論上JSONにはないマイナーな制約があります。それは基礎となるデータ型に付随する制限によって表されます。 特にjsonb型は、PostgreSQLnumeric型の範囲外の数を拒否します。このような処理系で定義される制限はRFC 7159で許可されています。 しかし、それは IEEE 754 倍精度浮動小数点がJSONのnumberプリミティブ型を表すのが一般的であるように、実際には他の実装でこのような問題が発生することの方がはるかに可能性が高いです(RFC 7159が明示的に予測して、許可しています)。 このようなシステムとPostgreSQLで交換フォーマットとしてJSONを使用する場合は、数値精度を失う危険性があることを把握しておく必要があります。

逆に、表に示すようにJSONプリミティブ型の入力フォーマットには、対応するPostgreSQL型と適合しない、いくつかのマイナーな制限があります。

表8.23 JSONプリミティブ型とPostgreSQL型の対応表

JSON プリミティブ型PostgreSQL注釈
stringtext\u0000は許可されません。 またデータベースエンコーディングがUTF8でない場合、非アスキーのユニコードエスケープも許可されません。
numbernumericNaNinfinity 値は許可されません
booleanboolean小文字のtruefalse という綴りのみ許可されます
null(none)SQLのNULLとは概念が異なります

8.14.1. JSONの入出力構文

JSON型の入出力構文の仕様はRFC 7159 に規定されています。

以下は、すべて有効なjson型(または jsonb型)の式です。

--- シンプルなスカラ/プリミティブ値
--- プリミティブ値は、数値、引用符で括られた文字列、true、 false、またはnullです。
SELECT '5'::json;

--- 0個以上の要素の配列(要素は同じ型である必要はありません)。
SELECT '[1, 2, "foo", null]'::json;

--- キーと値のペアを含むオブジェクト
--- オブジェクトキーは常に引用符で括られた文字列でなければならないことに注意してください。
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

--- 配列とオブジェクトは任意に入れ子にすることができます。
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

先に述べたようにJSONの値が入力されたときに、その後、追加の処理を行わずに表示する場合、jsonは入力と同じテキストが出力されます、jsonbでは、空白のような意味を持たない情報を保持しません。 例を示します。ここでは相違点に注意してください。

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json                       
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb                       
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

もう一つ注目に値するのは、jsonbでは、数値はnumeric型の動作に応じて表示され、意味を持たない情報を保持しません。実際には数字はE表記なしで表示されることを意味します。 例を示します。

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb          
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

しかし、この例に見られるようにjsonbは小数の末尾のゼロを保持します。それにも関わらず、等しいかチェックする場合等では、意味的に重要ではありません。

8.14.2. 効果的なJSONドキュメントの設計

JSONデータは従来のリレーショナルデータモデルよりもかなり柔軟に表現することができます。そのため、変わりやすさを求められる環境では説得力があります。 そして、それは同じアプリケーション内で、両方のアプローチが共存し相互に補完することが可能です。 しかし、最大の柔軟性が要求されるアプリケーションのためでもJSONドキュメントには、まだいくらかの固定構造を持つことを推奨します。 構造は(いくつかのビジネスルールを強制することは宣言的に可能であるが)、一般的に強制されないですが、テーブル内のドキュメント(データ)セットをまとめて予測可能な構造にすることで、簡単に問い合わせを記述することができます。

JSONデータはテーブルに格納するとき、他のデータ型と同一の同時実行制御の対象となります。大きな文章を保存することは実行可能ですが、すべての更新が行レベルロックを取得することに留意してください。 更新トランザクション間のロックの競合を減少させるために、管理可能なサイズにJSONドキュメントを制限することを検討してください。 理想的には、JSONドキュメントはビジネス・ルール上、独立して変更することができない単位までデータを分割すべきです。

8.14.3. jsonb型用包含演算子と存在演算子

包含演算子のテストはjsonb型の重要な機能です。これらのセットはjson型には全くありません。 jsonbドキュメントが、その中に指定する値を含むかどうかをテストします。 これらの例は、特に記載がないかぎりtrueを返します。

--- 単純なスカラ/プリミティブ値は、同一の値が含まれています。
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

--- 左辺の配列に右辺の配列が含まれています。
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

--- 配列要素の順序は重要ではありませんので、これもまた真になります。
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

--- 配列要素に重複が含まれているかは問題ではありません。
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

--- 右辺の単一ペアを持つオブジェクトが左辺のオブジェクト内に含まれています。
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

-- 右辺の配列は左辺の配列に含まれません、
-- 類似の配列が、その中のネストに含まれているにも関わらず。
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- falseになる

--- しかし、ネストで層を合わせれば含まれるようになります。
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

--- 同様に、これも含まれません。
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- falseになる

--- トップレベルのキーと空のオブジェクトが含まれる。
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

一般原則では、オブジェクトにオブジェクトが含まれているかを判断するには、いくつかの条件に一致しない配列要素とキー/値のペアを含むオブジェクトを捨てた後に構造とデータを一致させる必要があります。 しかし、条件に一致するには配列要素の順序は重要ではなく、重複要素は一回のみ有効に評価されることを覚えておく必要があります。

構造が一致しなければならないという一般原則の特別な例外として、配列はプリミティブな値を含めることができます。

--- この配列はプリミティブな文字列を含みます。
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

--- この例外は相互的ではありません。 -- これは含まれません。
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- falseになる

jsonb型は、また存在演算子を持ちます。包含の変種です。それは文字列(与えられたtext値)が、jsonb値のオブジェクトキーまたは配列のトップレベルに存在するかどうかをテストします。 これらの例は、特に記載がないかぎりtrueを返します。

--- 文字列が配列要素に存在する。
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

--- 文字列がオブジェクトキーに存在する。
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

--- オブジェクト値は考慮されません。
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- falseになる

--- オブジェクトはトップレベルから一致するように存在する必要があります。
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- falseになる

--- 文字列はJSONプリミティブ文字列と一致させることができます。
SELECT '"foo"'::jsonb ? 'foo';

JSONオブジェクトは、関係するキーや要素が多く存在する場合、含むかどうかまたは存在するかどうかのテストに適しています。なぜなら配列とは異なり、リニア検索をする必要がなく、内部的に検索に最適化されています。

ヒント

JSONでは包含がネストされるので、適切な問い合わせではサブオブジェクトの明示的な選択を省略することが出来ます。 例を挙げます。 doc列にトップレベルのオブジェクトがあります。 このオブジェクトには、tagsフィールドが含まれ、このフィールドにサブオブジェクトの配列が多く含まれているとします。 以下の問い合わせは、サブオブジェクトが"term":"paris""term":"food"の両方を含むエントリを探します。 そのときtags配列の外側にある、それらのキーは無視されます。

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

同じことを達成することは出来ます。例えば、

SELECT doc->'site_name' FROM websites
  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

しかし、そのアプローチは柔軟性に欠け、効率も落ちます。

一方、JSONの存在演算子は、ネストしていません。 JSONの値の最上位に指定されたキーまたは配列要素のみを探します。

JSONの様々な包含演算子や存在演算子、他のすべてのJSON演算子と関数は 9.15に記載されています。

8.14.4. jsonb インデックス

GINインデックスは、多数のjsonbドキュメント(データ)のキーやキー/値ペアを効率的に検索するときに用いることができます。 異なるパフォーマンスと柔軟性のトレードオフを持つ、2つのGIN 演算子クラス が提供されています。

jsonb型の問い合わせでサポートしているデフォルトのGIN演算子クラスは、トップレベルのキーが存在するかの演算子として??&?|があり、パス/値が存在するかの演算子として@>があります。 (これらの演算子の意味の詳細は、表 9.44を参照してください。) この演算子クラスのインデックスを作成する例。

CREATE INDEX idxgin ON api USING GIN (jdoc);

デフォルトでないGIN演算子クラスは、@>演算子のみサポートするjsonb_path_opsがあります。 この演算子クラスのインデックスを作成する例。

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);

サードパーティのWebサービスから、ドキュメント化されたスキーマ定義を持つJSONドキュメントを取得し、格納するテーブルの例を考えてみましょう。 典型的なドキュメントは、次のとおりです。

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

テーブル名 apijsonb型でjdocをカラム名として格納します。 このカラムにGINインデックスを作成した場合、以下のような問い合わせがインデックスを利用することができます。

--- "company"キー が "Magnafone"値であるものを見つける
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

しかし 次のような問い合わせはインデックスを使用しません。なぜなら、?演算子はインデックス可能ですが、jdocカラムのインデックスが直接適用されていないためです。

--- キー "tags" の配列要素に "qui"が含まれているか見つける
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

それでも、上記の問い合わせは、式インデックスを適切に使用することでインデックスを使用することができます。一般的な "tags"キーから特定の項目を照会する場合、このようなインデックスを定義すると良いかもしれません。

CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

さて、 WHERE句の jdoc -> 'tags' ? 'qui'は、インデックス式では 、?演算子はインデックス可能として認識されます。 (式インデックスに関する詳細情報は11.7を参照してください。)

別のアプローチとして包含を利用する問い合わせがあります。例を示します。

--- キー "tags"に 要素"qui"が含まれるかどうか見つける
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

jdocカラムのシンプルなGINインデックスは、この問い合わせをサポートすることができます。 しかし、前の例では、tagsキーの下にあるデータのみをインデックスに格納していたのに対して、そのようなインデックスは、jdocのすべてのキーと値のコピーを保存しますので、注意が必要です。 シンプルなインデックスアプローチは(それが全てのキーについての問い合わせをサポートしているため)はるかに柔軟ですが、ターゲット式インデックスは単純なインデックスより小さく、検索のときに高速である可能性が高くなります。

jsonb_path_ops演算子クラスは、@>演算子をサポートしているだけですが、デフォルト演算子クラスのjsonb_opsよりも顕著なパフォーマンス上の利点があります。 jsonb_path_opsインデックスは、通常同じデータのjsonb_opsインデックスよりもはるかに小さく、データの中で頻繁に表示されるキーを含む場合のような特別な検索には、より良くなります。 そのため、デフォルトの演算子クラスよりも検索操作よくなります。

jsonb_opsjsonb_path_opsのGINインデックスの技術的差異は、前者はデータのキーと値のための独立したインデックスを作成しますが、後者は、データの値に対してのみインデックスを作成します。 [6] 基本的に、jsonb_path_opsインデックス項目は、値とキーのハッシュです。例えば、{"foo": {"bar": "baz"}}のインデックスはハッシュ値にfoobarbazすべてを組み込んで作成されます。したがって、包含問い合わせのためのインデックス検索は、非常に特定の構造を返すようになっています。 しかしfooがキーとして表示されるかどうかを調べるには全く方法はありません。 一方、jsonb_opsインデックスは個別にはfoo、bar、bazを表す3つのインデックス項目を作成します。その後、包含問い合わせをおこなうには、これらの項目の3つすべてを含む行を探します。 GINインデックスは、かなり効率的に検索することができますが、特に3つの索引項目のいずれかで、非常に多数の行が単一の場合に、同等のjsonb_path_ops検索よりも遅くなります。

jsonb_path_opsアプローチの欠点は、{"a": {}}のような、任意の値を含まないJSON構造のためのインデックスエントリを生成しません。このような構造を含むドキュメントの検索が要求された場合、それは、フルインデックススキャンを必要とします。それは非常に遅くなります。そのため、jsonb_path_opsは、多くの場合、そのような検索を実行するのには不適当です。

jsonb型は、btreehash インデックスもサポートします。これらは通常、JSONドキュメントの完全性をチェックすることが重要な場合のみ有用です。 jsonbのためのbtree順序には、興味深いことはほとんどありませんが、しかし、完全さのために次に示します。

Object > Array > Boolean > Number > String > Null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

ペアの同じ番号を持つオブジェクトは、順に比較されます。

key-1, value-1, key-2 ...

そのオブジェクトのキーは、その格納順に比較されることに注意してください。 短いキーは長いキーの前に格納されているため、特にこれは、次のような直感的でない結果に結果につながるかもしれません。

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

同様に、配列と同じ番号を持つ要素を比較する順番。

element-1, element-2 ...

JSONプリミティブ値は基本的にPostgreSQLデータ型と同じルールで比較されます。文字列は、デフォルトのデータベース照合を使用して比較されます



[6] この目的のために、 という用語は配列の要素を含みますが、JSONの専門用語では、オブジェクト内の値と配列の要素が時々違うことがあります。