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

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

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

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演算子と関数は 9.15. JSON関数と演算子に記載されています。

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

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

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. 式に対するインデックスを参照してください。）

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

-- キー "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データ型と同じルールで比較されます。文字列は、デフォルトのデータベース照合を使用して比較されます