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.16を参照してください。

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.16に記載されています。

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

jsonb型の問い合わせでサポートしているデフォルトのGIN演算子クラスは、キーが存在するかの演算子として?、?|、?&があり、包含演算子として@>があり、jsonpathマッチング演算子として@?、@@があります。 （これらの演算子の意味の詳細は、表 9.46を参照してください。） この演算子クラスのインデックスを作成する例。

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'は、インデックス式jdoc->'tags'では、?演算子はインデックス可能として認識されます。 （式インデックスに関する詳細情報は11.7を参照してください。）

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

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

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

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 = constantの形式の句を抽出し、句内で使われているキーと値に基づいてインデックスサーチをします。 アクセサチェーン(accessors chain)は.key、[*]、[index]アクセサを含みます。 jsonb_ops演算子クラスは.*と.**アクセサもサポートしますが、jsonb_path_ops演算子クラスはサポートしません。

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

jsonb_opsとjsonb_path_opsのGINインデックスの技術的差異は、前者はデータのキーと値のための独立したインデックスを作成しますが、後者は、データの値に対してのみインデックスを作成します。 ^[7] 基本的に、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データ型は要素を取り出したり修正したりするために配列形式の添字表現をサポートします。 入れ子になった値は、jsonb_set関数でのpath引数と同じ規則に従って、添字表現をつなげることで指定できます。 jsonb値が配列であれば、数字の添字はゼロから始まり、負の整数は配列の最後の要素から逆に数えます。 スライス表現はサポートされていません。 添字表現の結果は、必ずjsonbデータ型です。

UPDATE文では、jsonb値を修正するSET句内で添字が使えます。 添字のパスは、存在する範囲では影響する値すべてが到達可能でなければなりません。 例えば、パスval['a']['b']['c']は、val、val['a']、val['a']['b']それぞれがオブジェクトであれば、cまでたどることができます。 val['a']またはval['a']['b']が定義されていなければ、空のオブジェクトとして作られ必要に応じて埋められます。 しかしながら、val自身または途中の値の1つでも、文字列、数値、jsonb nullのような非オブジェクトとして定義されていれば、到達できないためエラーが発生し、トランザクションはアボートされます。

添字構文の例です。

-- キーでオブジェクトの値を取り出す
SELECT ('{"a": 1}'::jsonb)['a'];


-- キーのパスで入れ子のオブジェクトの値を取り出す
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];


-- インデックスで配列要素を取り出す
SELECT ('[1, "2", null]'::jsonb)[1];


-- キーでオブジェクトの値を更新する。'1'の周りの一重引用符に注意。
-- 代入する値もjsonb型でなければならない
UPDATE table_name SET jsonb_field['key'] = '1';


-- これはjsonb_field['a']['b']のいずれかのデータがオブジェクト以外のものであればエラーになる。
-- 例えば、値{"a": 1}はキー'a'の数値を持つ。
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';


-- WHERE句で添字を使ってデータにフィルタを掛ける。
-- 添字による結果はjsonbなので、それと比較する値もjsonbでなければならない。
-- 二重引用符により"value"も有効なjsonb文字列になる。
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';

添字によるjsonbの代入は、まれにjsonb_setとは異なる場合があります。 元のjsonb値がNULLの場合、添字による代入は、添字のキーで暗示されるその型の空のJSON値(オブジェクトまたは配列)であるかのように処理されます。

-- jsonb_fieldがNULLの場合、{"a": 1}になる
UPDATE table_name SET jsonb_field['a'] = '1';


-- jsonb_fieldがNULLの場合、[1]になる
UPDATE table_name SET jsonb_field[0] = '1';

要素が足りない配列に対してインデックスを指定した場合、インデックスが到達可能になって値が設定できるようになるまでNULL要素が追加されます。

-- jsonb_fieldが[]なら、[null, null, 2]になり、
-- jsonb_fieldが[0]なら、[0, null, 2]になる
UPDATE table_name SET jsonb_field[2] = '2';

対応する添字が暗示するように、到達できる最後の存在する要素がオブジェクトか配列である限り、jsonb値は存在しない添字のパスへの代入を受け付けます。 (パスの最後の添字で指定される要素には到達しませんし、何でも構いません。) 入れ子の配列やオブジェクト構造が作られ、前者の場合には、添字のパスにより指定されたように値が代入できるようになるまでnullで埋められます。

-- jsonb_fieldが{}であれば、{"a": [{"b": 1}]}になる
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';


-- jsonb_fieldが[]であれば、[null, {"a": 1}]になる
UPDATE table_name SET jsonb_field[1]['a'] = '1';

異なるプロシージャ言語でjsonb型の変換を実装した追加の拡張が入手可能です。

PL/Perl向けの拡張は、jsonb_plperlとjsonb_plperluと呼ばれます。 この拡張を使うとjsonbの値はPerlの配列、ハッシュ、スカラの適切なものにマップされます。

PL/Python向けの拡張は、jsonb_plpython3uと呼ばれます。 この拡張を使うと、jsonbの値はPythonの辞書型、リスト、スカラの適切なものにマップされます。

上記の拡張のうち、jsonb_plperlは「trusted」と見なされます。つまり、現在のデータベースに対してCREATE権限を持つ非スーパーユーザがインストールできます。 残りはインストールするのにスーパーユーザ権限が必要です。

jsonpath型は、PostgreSQLでJSONデータの効率的な問い合わせをするために、SQL/JSONパス言語のサポートを実装しています。 構文解析されたSQL/JSONパス式のバイナリ表現を提供し、SQL/JSON問い合わせ関数でさらに処理するために、パスエンジンがJSONデータから取得する項目を指定します。

SQL/JSONパス述部および演算子のセマンティクスは、SQLに準拠しています。 同時に、JSONデータを処理する自然な方法を提供するために、SQL/JSONのパス構文ではいくつかのJavaScript規則を使用します。

SQL/JSONパス式の数値リテラルは、JavaScriptルールに従います。JavaScriptルールは、いくつかの細かい点でSQLやJSONのいずれとも異なります。 例えば、SQL/JSONパスでは.1や1.が有効ですが、JSONでは無効です。 例えば、1_000_000、0x1EEE_FFFF、0o273、0b100101など、10進数でない整数リテラルやアンダースコアの区切り文字がサポートされています。 SQL/JSONパス式では（およびJavaScriptでは、しかし本来のSQLではそうではありません）、基数の接頭辞の直後にアンダースコアの区切り文字を使用できません。

SQL/JSONパス式は通常、SQL問い合わせでSQL文字列リテラルとして記述されるため、一重引用符で囲む必要があり、値内で必要な一重引用符は二重にする必要があります(4.1.2.1を参照)。 一部の形式のパス式では、文字列リテラルを含める必要があります。 これらの埋め込み文字列リテラルは JavaScript/ECMAScript規則に従います。二重引用符で囲む必要があり、その中でバックスラッシュエスケープを使用してタイプしにくい文字を表すことができます。 特に、埋め込み文字列リテラル内で二重引用符を記述する方法は\"であり、バックスラッシュを記述する必要がある場合は\\と書く必要があります。 その他の特別なバックスラッシュ構文には、以下のJavaScript文字列で認識されるものが含まれます。 さまざまなASCII制御用文字の\b、\f、\n、\r、\t、\v、2桁の16進数だけで記述された文字コード用の\xNN、4つの16進数のコードポイントで識別されるUnicode文字用の\uNNNNおよび1～6桁の16進数で記述されたUnicode文字コードポイント用の\u{N...}です。

パスの式は、次のようなパス要素のシーケンスで構成されます。

jsonpath式を使用したSQL/JSON問い合わせ関数の詳細は、9.16.2を参照してください。