JSONデータ型はJSON(JavaScript Object Notation)データを格納するためのものです。JSONの仕様はRFC 7159に定義されています。
このようなデータは、text
型として格納することもできますが、JSONデータ型は、それぞれ格納された値がJSONルールに従って有効に施行されるという利点があります。
これらのデータ型に格納されたデータのために利用可能な各種JSON固有の関数と演算子もあります。
9.15を参照してください。
PostgreSQLには、JSONデータを格納するための2つの型、json
とjsonb
があります。
これらのデータ型に対して効率的な問い合わせメカニズムを実装するために、PostgreSQLは8.14.6で説明されているjsonpath
データ型も提供します。
json
型とjsonb
型というデータ型は、ほとんど 同一の入力値セットを受け入れます。
現実的に主要な違いは効率です。
json
データ型は入力テキストの正確なコピーで格納し、処理関数を実行するたびに再解析する必要があります。
jsonb
データ型では、分解されたバイナリ形式で格納されます。
格納するときには変換のオーバーヘッドのため少し遅くなりますが、処理するときには、全く再解析が必要とされないので大幅に高速化されます。
また jsonb
型の重要な利点はインデックスをサポートしていることです。
json
型は入力値のコピーを格納しているので、意味的に重要でないトークン間の空白だけでなく、JSONオブジェクト内のキーの順序も維持します。
また、JSONオブジェクト内に同じキーと値が複数含まれていてもすべてのキー/値のペアが保持されます。(この処理関数は最後の値1つを処理させるようすれば済みます。)
これとは対照的に、jsonb
は空白を保持しません。オブジェクトキーの順序を保持せず、重複したオブジェクトキーを保持しません。重複キーを入力で指定された場合は、最後の値が保持されます。
一般的に、ほとんどのアプリケーションではJSONデータ型としてjsonb
型のほうが望ましいでしょう。ただし、オブジェクトキーを従来のような順序であることを仮定する非常に特殊なニーズが存在するような場合は除きます。
PostgreSQLはデータベースごとに1つの文字セットエンコーディングのみが許可されています。従ってデータベースエンコーディングがUTF8でない限り、厳密にはJSON型がJSON仕様に準拠することはできません。 データベースのエンコーディングで表現できない文字を直接含めようとすると失敗します。逆に、UTF8で許可されずにデータベースのエンコーディングで許可される文字が許されてしまいます。
RFC 7159 では、JSON文字列はUnicodeエスケープシーケンス \u
を許可するように記述されています。
XXXX
json
型の入力関数は、データベースエンコーディング方式に関係なくUnicodeエスケープが許可されています。それは、構文上の正しさ(つまり\u
に続けて16進数が4桁)だけをチェックしています。
しかし、jsonb
の入力関数はより厳しくなります。
データベースのエンコードがUTF8でない限り、Unicodeは非ASCII文字(U+007F
より上位の文字)に対してエスケープを禁止します。
jsonb
型は\u0000
も許可しません。(なぜならPostgreSQLのtext
型で表現できないためです。)
また、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
型は、PostgreSQLのnumeric
型の範囲外の数を拒否します。このような処理系で定義される制限はRFC 7159で許可されています。
しかし、それは IEEE 754 倍精度浮動小数点がJSONのnumber
プリミティブ型を表すのが一般的であるように、実際には他の実装でこのような問題が発生することの方がはるかに可能性が高いです(RFC 7159が明示的に予測して、許可しています)。
このようなシステムとPostgreSQLで交換フォーマットとしてJSONを使用する場合は、数値精度を失う危険性があることを把握しておく必要があります。
逆に、表に示すようにJSONプリミティブ型の入力フォーマットには、対応するPostgreSQL型と適合しない、いくつかのマイナーな制限があります。
表8.23 JSONプリミティブ型とPostgreSQL型の対応表
JSON プリミティブ型 | PostgreSQL型 | 注釈 |
---|---|---|
string | text | \u0000 は許可されません。
またデータベースエンコーディングがUTF8でない場合、非アスキーのユニコードエスケープも許可されません。 |
number | numeric | NaN と infinity 値は許可されません |
boolean | boolean | 小文字のtrue と false という綴りのみ許可されます |
null | (none) | SQLのNULL とは概念が異なります |
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
は小数の末尾のゼロを保持します。それにも関わらず、等しいかチェックする場合等では、意味的に重要ではありません。
JSONの値の作成と処理に使用可能な組み込み関数と演算子のリストについては、9.15を参照してください。
JSONデータは従来のリレーショナルデータモデルよりもかなり柔軟に表現することができます。そのため、変わりやすさを求められる環境では説得力があります。 そして、それは同じアプリケーション内で、両方のアプローチが共存し相互に補完することが可能です。 しかし、最大の柔軟性が要求されるアプリケーションのためでもJSONドキュメントには、まだいくらかの固定構造を持つことを推奨します。 構造は(いくつかのビジネスルールを強制することは宣言的に可能であるが)、一般的に強制されないですが、テーブル内の「ドキュメント」(データ)セットをまとめて予測可能な構造にすることで、簡単に問い合わせを記述することができます。
JSONデータはテーブルに格納するとき、他のデータ型と同一の同時実行制御の対象となります。大きな文章を保存することは実行可能ですが、すべての更新が行レベルロックを取得することに留意してください。 更新トランザクション間のロックの競合を減少させるために、管理可能なサイズにJSONドキュメントを制限することを検討してください。 理想的には、JSONドキュメントはビジネス・ルール上、独立して変更することができない単位までデータを分割すべきです。
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に記載されています。
jsonb
インデックス
GINインデックスは、多数のjsonb
ドキュメント(データ)のキーやキー/値ペアを効率的に検索するときに用いることができます。
異なるパフォーマンスと柔軟性のトレードオフを持つ、2つのGIN 「演算子クラス」 が提供されています。
jsonb
型の問い合わせでサポートしているデフォルトのGIN演算子クラスは、トップレベルのキーが存在するかの演算子として?
、?&
、?|
があり、パス/値が存在するかの演算子として@>
があります。
(これらの演算子の意味の詳細は、表 9.45を参照してください。)
この演算子クラスのインデックスを作成する例。
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" ] }
テーブル名 api
にjsonb
型で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を参照してください。)
また、GINインデックスはjsonpath
のマッチングを実行する@@
演算子と@?
演算子をサポートします。
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] ? (@ == "qui")';
GINインデックスは、jsonpath
: accessors_chain
= const
の形式の文を抽出します。
アクセサチェーン(Accessors chain)は[*]
と[
アクセサで構成されます。
index
]jsonb_ops
は、さらに.*
と.**
アクセサもサポートします。
別のアプローチとして包含を利用する問い合わせがあります。例を示します。
-- キー "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_ops
とjsonb_path_ops
のGINインデックスの技術的差異は、前者はデータのキーと値のための独立したインデックスを作成しますが、後者は、データの値に対してのみインデックスを作成します。
[6]
基本的に、jsonb_path_ops
インデックス項目は、値とキーのハッシュです。例えば、{"foo": {"bar": "baz"}}
のインデックスはハッシュ値にfoo
、bar
、 baz
すべてを組み込んで作成されます。したがって、包含問い合わせのためのインデックス検索は、非常に特定の構造を返すようになっています。 しかしfooがキーとして表示されるかどうかを調べるには全く方法はありません。
一方、jsonb_opsインデックスは個別にはfoo、bar、bazを表す3つのインデックス項目を作成します。その後、包含問い合わせをおこなうには、これらの項目の3つすべてを含む行を探します。
GINインデックスは、かなり効率的に検索することができますが、特に3つの索引項目のいずれかで、非常に多数の行が単一の場合に、同等のjsonb_path_ops検索よりも遅くなります。
jsonb_path_ops
アプローチの欠点は、{"a": {}}
のような、任意の値を含まないJSON構造のためのインデックスエントリを生成しません。このような構造を含むドキュメントの検索が要求された場合、それは、フルインデックススキャンを必要とします。それは非常に遅くなります。そのため、jsonb_path_ops
は、多くの場合、そのような検索を実行するのには不適当です。
jsonb
型は、btree
と hash
インデックスもサポートします。これらは通常、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データ型と同じルールで比較されます。文字列は、デフォルトのデータベース照合を使用して比較されます
異なるプロシージャ言語でjsonb
型の変換を実装した追加の拡張が入手可能です。
PL/Perl向けの拡張は、jsonb_plperl
とjsonb_plperlu
と呼ばれます。
この拡張を使うとjsonb
の値は適したPerlの配列、ハッシュ、スカラにマップされます。
PL/Python向けの拡張は、jsonb_plpythonu
、jsonb_plpython2u
、jsonb_plpython3u
と呼ばれます(PL/Pythonの命名規約については45.1を参照してください)。
この拡張を使うとjsonb
の値は適したPythonの辞書型、リストやスカラにマップされます。
jsonpath
型は、PostgreSQLでJSONデータの効率的な問い合わせをするために、SQL/JSONパス言語のサポートを実装しています。
構文解析されたSQL/JSONパス式のバイナリ表現を提供し、SQL/JSON問い合わせ関数でさらに処理するために、パスエンジンがJSONデータから取得する項目を指定します。
SQL/JSONパス述部および演算子のセマンティクスは、SQLに準拠しています。 同時に、JSONデータを処理する最も自然な方法を提供するために、SQL/JSONのパス構文ではいくつかのJavaScript規則を使用します。
ドット(.
)は、メンバアクセスに使用されます。
大括弧([]
)は配列アクセスに使用されます。
1から始まる通常のSQL配列とは異なり、SQL/JSON配列は0スタートです。
SQL/JSONパス式は通常、SQL問い合わせでSQL文字列リテラルとして記述されるため、一重引用符で囲む必要があり、値内で必要な一重引用符は二重にする必要があります
(4.1.2.1を参照)。
一部の形式のパス式では、文字列リテラルを含める必要があります。
これらの埋め込み文字列リテラルは二重引用符で囲む必要があり、バックスラッシュエスケープを使用してハード・タイプ文字を表すことができます。
特に、埋め込み文字列リテラル内で二重引用符を記述する方法は\"
であり、バックスラッシュを記述する必要がある場合は\\
と書く必要があります。
その他の特別なバックスラッシュ構文には、以下のJSON文字列で認識されるものが含まれます。
さまざまなASCII制御用文字の\b
、\f
、\n
、\r
、\t
、\v
、および4つの16進数のコードポイントで識別されるUnicode文字用の\u
です。
バックスラッシュ構文には、JSONでは許されない2つのケースも含まれています。
NNNN
\x
は2桁の16進数だけで記述された文字コードの場合で、NN
\u{
は、1~6桁の16進数で記述された文字コードの場合です。
N...
}
パスの式は、次のようなパス要素のシーケンスで構成されます。
jsonpath
式を使用したSQL/JSON問い合わせ関数の詳細は、9.15.2を参照してください。
表8.24 jsonpath
変数
変数 | 説明 |
---|---|
$ | 問い合わせ対象(context item)のJSONテキストを表す変数。 |
$varname |
名前付き変数。
その値はいくつかのJSON処理関数のパラメーターvars で設定できます。
詳細は表 9.47とそのノートを参照してください。
|
@ | フィルター式のパス評価の結果を表す変数。 |
表8.25 jsonpath
Accessors
アクセサ演算子 | 説明 |
---|---|
|
指定されたキーを持つオブジェクトメンバを返すメンバアクセサ。
キー名が |
|
現在のオブジェクトの最上位レベルになるすべてのメンバの値を返すワイルドカードメンバアクセサ。 |
|
現在のオブジェクトのJSON階層のすべてのレベルを処理し、ネストされたレベルに関わらず全てのメンバ値を返す再帰的なワイルドカードメンバアクセサ。 これはSQL/JSON標準のPostgreSQLの拡張です。 |
|
|
|
配列要素アクセサ。
指定された |
|
全ての配列の要素を返すワイルドカード配列要素アクセサ。 |