JSON型 - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、JSON型の定義と構文について説明します。

JSONデータ型は、RFC 7159で指定されているJSON (JavaScript Object Notation) データを格納するためのものです。このようなデータはテキストとして格納することもできますが、JSONデータ型には、格納された各値がJSONルールに従って有効であることを強制するという利点があります。これらのデータ型に格納されたデータに使用できるJSON固有の関数と演算子もあります。

PostgreSQLoffers JSONデータを格納するための2つのタイプ: jsonとjsonb。これらのデータ型に対して効率的なクエリメカニズムを実装するために、PostgreSQLはjsonpathデータ型も提供しています。

jsonおよびjsonbデータ型は、ほぼ同じ値のセットを入力として受け入れます。主な実用的な違いは効率の1つです。 jsonデータ型には入力テキストの正確なコピーが格納され、処理関数は実行ごとに再解析する必要があります。一方、jsonbデータは分解されたバイナリ形式で格納されます。jsonbはインデックス作成もサポートしています。これは大きな利点です。

json型には入力テキストの正確なコピーが格納されるため、トークン間の意味的に重要でない空白と、JSONオブジェクト内のキーの順序が保持されます。また、値内のJSONオブジェクトに同じキーが複数回含まれている場合は、すべてのキーと値のペアが保持されます。 (処理関数は、最後の値を有効な値とみなす。) 対照的に、jsonbは空白を保持せず、オブジェクトキーの順序を保持せず、重複するオブジェクトキーを保持しません。入力に重複キーが指定されている場合、最後の値のみが保持されます。

一般的に、ほとんどのアプリケーションは、オブジェクトキーの順序付けに関する従来の仮定など、非常に特殊なニーズがない限り、JSONデータをjsonbとして保存することを好みます。

RFC7159は、JSON文字列をUTF8でエンコードすることを指定します。したがって、データベースエンコーディングがUTF8でない限り、JSON型がJSON仕様に厳密に準拠することはできません。データベースエンコーディングでは表現できない文字を直接含めることはできません。逆に、データベースエンコーディングでは表現できるがUTF8では表現できない文字は許可されます。

RFC7159により、JSON文字列に \u XXXXで示されるUnicodeエスケープシーケンスを含めることができます。 json型の入力関数では、データベースのエンコーディングに関係なくUnicodeエスケープが許可され、構文の正確性 (つまり、4つの16進数が \uに続くこと) についてのみチェックされます。ただし、jsonbの入力関数はより厳格です。つまり、データベースエンコーディングで表現できない文字のUnicodeエスケープを許可しません。また、jsonb型は \u0000を拒否し (PostgreSQLのtext型では表すことができないため) 、Unicodeのサロゲートペアを使用してUnicode Basic Multilingual Plane以外の文字を指定することは正しいと主張します。有効なUnicodeエスケープは、ストレージ用に同等の単一文字に変換されます。これには、サロゲートペアを単一文字に折りたたむことが含まれます。

重要

JSON処理関数の多くは、Unicodeエスケープを通常の文字に変換するため、入力がjsonbではなくjson型であっても、説明したのと同じタイプのエラーをスローします。 json入力関数がこれらのチェックを行わないという事実は、履歴アーティファクトと見なすことができますが、表現された文字をサポートしないデータベースエンコーディングでのJSON Unicodeエスケープの単純なストレージ (処理なし) を可能にします。

テキストJSON入力をjsonbに変換する場合、RFC7159で記述されたプリミティブ型は、ネイティブPostgreSQL型に効果的にマップされます。したがって、有効なjsonbデータを構成するものには、基になるデータ型で表すことができるものの制限に対応する、json型にもJSONにも適用されないいくつかのマイナーな追加の制約があります。特に、jsonbはPostgreSQLの数値データ型の範囲外の数値を拒否しますが、jsonは拒否しません。このような実装定義の制限は、RFC7159によって許可されます。しかし、実際には、JSONの数プリミティブタイプをIEEE 754倍精度浮動小数点として表すことが一般的であるため、このような問題は他の実装で発生する可能性がはるかに高くなります (RFC7159は明示的に予測して許可します) 。このようなシステムでJSONを交換形式として使用する場合、PostgreSQLによって最初に格納されたデータと比較して数値精度が失われる危険性を考慮する必要があります。

逆に、表に示すように、JSONプリミティブ型の入力形式には、対応するPostgreSQLtypesには適用されない小さな制限があります。

JSONプリミティブ型と対応するPostgreSQL型

JSONプリミティブ型	PostgreSQLtype	ノート
`文字列`	`テキスト`	`\u0000`が許可されていません。データベースエンコーディングで使用できない文字を表すUnicodeエスケープも同様です。
`番号`	`数値`	`NaN`と`infinity`の値は許可されません
`ブール値`	`ブール値`	小文字の`true`と`false`のスペルのみが受け入れられます
`null`	(なし)	SQL `NULL`は別の概念です

JSON入力および出力構文

JSONデータ型の入出力構文は、RFC7159で指定されたとおりです。

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

-- 単純なスカラー /プリミティブ値
    -- プリミティブ値には、数値、引用符で囲まれた文字列、true、false、またはnullを指定できます。
    SELECT '5'::json;

    -ゼロ以上の要素の配列 (要素は同じ型である必要はありません)
    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行）

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

意味的に重要ではない詳細の1つは、jsonbでは、基になるnumeric型の動作に従って数値が印刷されることです。実際には、これはE表記で入力された数字がそれなしで印刷されることを意味します。

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

ただし、jsonbは、等式チェックなどの目的で意味的に重要ではない場合でも、この例で見られるように、後続の分数ゼロを保持します。

JSON値の作成と処理に使用できる組み込み関数と演算子のリスト。

JSONドキュメントのデザイン

データをJSONとして表現することは、従来のリレーショナルデータモデルよりもかなり柔軟にできます。これは、要件が流動的な環境では魅力的です。両方のアプローチが共存し、同じアプリケーション内で互いに補完することはかなり可能である。ただし、最大限の柔軟性が望まれるアプリケーションであっても、JSONドキュメントはある程度固定された構造を持つことが推奨されます。この構造は通常、強制されていませんが (いくつかのビジネスルールを宣言的に強制することは可能ですが) 、予測可能な構造を持つことで、テーブル内の「ドキュメント」 (データム) のセットを有効に要約するクエリを簡単に作成できます。

JSONデータは、テーブルに格納される場合、他のデータ型と同じ同時実行制御の考慮事項の対象となります。大きなドキュメントを保存することは実用的ですが、更新は行全体の行レベルのロックを取得することに注意してください。更新トランザクション間のロック競合を減らすために、JSONドキュメントを管理可能なサイズに制限することを検討してください。理想的には、JSONドキュメントは、ビジネスルールが指示するアトミックデータをそれぞれ表す必要があります。

jsonbの封じ込めと存在

封じ込めのテストは、jsonbの重要な機能です。 jsonタイプには、機能の並列セットはありません。 1つの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;

    -右側に1つのペアのオブジェクトが含まれています
-左側のオブジェクト内:
    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; --

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

一般的な原理は、含まれているオブジェクトは、おそらく含まれているオブジェクトからいくつかの不一致配列要素またはオブジェクトキー /値ペアを破棄した後に、構造およびデータコンテンツに関して含まれているオブジェクトと一致しなければならないということです。ただし、包含一致を行う場合、配列要素の順序は重要ではなく、重複する配列要素は効果的に1回だけ考慮されることに注意してください。

構造が一致する必要があるという一般的な原則の特別な例外として、配列にはプリミティブ値が含まれる場合があります。

-- この配列には、プリミティブ文字列値が含まれます。
    SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

    -この例外は相互ではありません-非封じ込めはここで報告されます:
    SELECT '"bar"'::jsonb @> '["bar"]'::jsonb; -- はfalse

を生成します

jsonbには存在演算子もあります。これは、包含のテーマのバリエーションです。つまり、文字列 (テキスト値として指定) がオブジェクトキーまたは配列要素としてjsonb値の最上位に表示されるかどうかをテストします。これらの例は、注意を除いてtrueを返します。

-- Stringは配列要素として存在します。
    SELECT '["foo", "bar", "baz"]'::jsonb? 'バー';

    -- Stringはオブジェクトキーとして存在します:
    SELECT '{"foo": "bar"}'::jsonb? 'foo';

    -- オブジェクト値は考慮されません:
    SELECT '{"foo": "bar"}'::jsonb? 'バー'; -falseを生成します

    -封じ込めと同様に、存在はトップレベルで一致する必要があります。
    SELECT '{"foo": {"bar": "baz"}}'::jsonb? 'バー'; -falseを生成します

    -- プリミティブJSON文字列と一致する場合、文字列は存在すると見なされます。
    SELECT '"foo"'::jsonb? 'foo';

JSONオブジェクトは、配列とは異なり、内部的に検索用に最適化されており、線形に検索する必要がないため、包含または存在をテストするために配列よりも適しています。

JSONコンテインメントはネストされているため、適切なクエリではサブオブジェクトの明示的な選択をスキップできます。例として、トップレベルのオブジェクトを含むdoc列があり、ほとんどのオブジェクトにはサブオブジェクトの配列を含むtagsfieldsが含まれているとします。このクエリは、"term":"paris" と "term":"food" の両方を含むサブオブジェクトが表示されるエントリを検索しますが、tags配列以外のキーは無視します。

SELECT doc->'site_name' Webサイトから
      WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

同じことを達成することができます。

SELECT doc->'site_name' Webサイトから
      WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

しかし、そのアプローチは柔軟性が低く、しばしば効率も低くなります。

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

jsonbインデックス作成

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

jsonbのデフォルトのGIN演算子クラスは、key-exists演算子 @> 、? 、?& 、および ?| でクエリをサポートします。この演算子クラスでインデックスを作成する例は次のとおりです。

CREATE INDEX idxgin ON api USING gin (jdoc);

デフォルト以外のGIN演算子クラスjsonb_path_opsはkey-exists演算子をサポートしていませんが、@> をサポートしています。この演算子クラスでインデックスを作成する例は次のとおりです。

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" 、
        "住所": "178ハワードプレイス、湾岸、ワシントン、702" 、
        "registered": "2009-11-07T08:53:22 + 08:00" 、
        "緯度": 19.793713、
        "経度": 86.513373、
        "tags": [
            "enim" 、
            "aliquip" 、
            "qui"
        ]
    }

これらのドキュメントは、apiという名前のテーブル、jdocという名前のjsonb列に格納されます。この列にGINインデックスが作成されている場合、次のようなクエリでインデックスを使用できます。

-キー「company」の値が「Magnafone」であるドキュメントを検索します
    SELECT jdoc->'guid', jdoc->'name 'FROM api WHERE jdoc @> '{"company": "Magnafone"}';

ただし、次のようなクエリにはインデックスを使用できませんでした。? がインデックス可能であるため、インデックス付き列jdocには直接適用されません。

-- キー「タグ」がキーまたは配列要素「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' に

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の形式の句を抽出し、これらの句に記載されているキーと値に基づいてインデックス検索を行います。アクセサーチェーンは、を含むことができる。キー、[*] 、および [index] アクセサー。 jsonb_ops演算子クラスは .* および .** アクセサーもサポートしますが、jsonb_path_ops演算子クラスはサポートしません。

クエリのもう1つのアプローチは、封じ込めを活用することです。

-- キー "tags" が配列要素 "qui" を含むドキュメントを検索します。
    SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

jdoc列の単純なGINインデックスでこのクエリをサポートできます。ただし、このようなインデックスはjdoc列のすべてのキーと値のコピーを格納しますが、前の例の式インデックスはtagsキーの下にあるデータのみを格納します。単純インデックスアプローチははるかに柔軟性がありますが (任意のキーに関するクエリをサポートするため) 、ターゲット式インデックスは単純インデックスよりも小さく、検索が高速になる可能性があります。

jsonb_path_ops演算子クラスは、@> 、@? 、および @ @ 演算子を使用したクエリのみをサポートしますが、デフォルトの演算子クラスjsonb_opsよりもパフォーマンス上の顕著な利点があります。 jsonb_path_opsインデックスは通常、同じデータ上のjsonb_opsインデックスよりもはるかに小さく、特にクエリにデータに頻繁に表示されるキーが含まれている場合は、検索の特異性が向上します。したがって、検索操作は通常、デフォルトの演算子クラスよりも優れています。

jsonb_opsとjsonb_path_ops GINインデックスの技術的な違いは、前者はデータ内の各キーと値に対して独立したインデックスアイテムを作成し、後者はデータ内の各値に対してのみインデックスアイテムを作成することです。基本的に、各jsonb_path_opsインデックス項目は、値とそれにつながるキーのハッシュです。たとえば、{"foo": {"bar": "baz"}} をインデックスするには、foo、bar、bazの3つすべてをハッシュ値に組み込んだ単一のインデックス項目が作成されます。したがって、この構造を探している包含クエリは、極端に特定のインデックス検索をもたらすが、fooがキーとして現れるかどうかを調べる方法は全くない。一方、jsonb_opsインデックスは、foo、bar、およびbazを個別に表す3つのインデックス項目を作成します。次に、包含クエリを実行するには、これら3つの項目すべてを含む行を探します。 GINインデックスはこのようなAND検索をかなり効率的に実行できますが、特に3つのインデックス項目のいずれか1つを含む行が非常に多数ある場合は、同等のjsonb_path_ops検索よりも具体的ではなく遅くなります。

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

jsonbは、btreeおよびhashインデックスもサポートしています。これらは通常、完全なJSONドキュメントの同等性をチェックすることが重要な場合にのみ役立ちます。 jsonbデータムのbtree順序付けに大きな関心はほとんどありませんが、完全を期すためには次のとおりです。

Object> 配列> Boolean > Number > String > Null

    nペアのオブジェクト> n - 1ペアのオブジェクト

    n個の要素を持つ配列> n - 1個の要素

を持つ配列

ペアの数が等しいオブジェクトは、次の順序で比較されます。

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

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

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

同様に、要素数が等しい配列は次の順序で比較されます。

element-1, element-2...

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

変換

さまざまな手続き型言語のjsonb型の変換を実装する拡張機能を使用できます。

これらの拡張機能のうち、jsonb_plperlは「信頼済み」と見なされます。つまり、現在のデータベースに対してCREATE権限を持つスーパーユーザー以外がインストールできます。残りはインストールにスーパーユーザー権限が必要です。

jsonpath型

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

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

Dot (.) はメンバーアクセスに使用されます。
角括弧 ([]) は、配列アクセスに使用されます。
SQL/JSON配列は、1から始まる通常のSQL配列とは異なり、0相対です。

SQL/JSONパス式は通常、SQLクエリでSQL文字列リテラルとして記述されるため、単一引用符で囲む必要があり、値内で必要な単一引用符は2倍にする必要があります。一部の形式のパス式では、文字列リテラルが必要です。これらの埋め込み文字列リテラルは、JavaScript/ECMAScriptの規則に従います。二重引用符で囲む必要があり、バックスラッシュエスケープを使用して、他のタイプが難しい文字を表すことができます。特に、埋め込み文字列リテラル内に二重引用符を書く方法は \" であり、バックスラッシュ自体を書くには \\ を書く必要があります。その他の特殊なバックスラッシュシーケンスには、JSON文字列で認識されるものがあります。\b、\f、\n、\r、\t、\v、および4 16進コードポイントで識別されるUnicode文字の場合は \u NNNNです。バックスラッシュ構文には、JSONで許可されていない2つのケースも含まれます。2つの16進数字のみで記述された文字コードの場合は \x NN、および \u{ N。 . . 1 ~ 6桁の16進数字で記述された文字コードの} 。

パス式は、次のいずれかのパス要素のシーケンスで構成されます。

JSONプリミティブ型のパスリテラル: Unicodeテキスト、数値、true、false、またはnull。
パス変数。
アクセサー演算子。
jsonpath演算子とメソッド。
これは、フィルター式を提供したり、パス評価の順序を定義したりするために使用できます。

jsonpath変数

変数	説明
`$`	照会されるJSON値を表す変数 (コンテキスト項目) 。
`$varname`	名前付き変数。その値は、いくつかのJSON処理関数のparametervarsによって設定できます。
`@`	フィルター式でのパス評価の結果を表す変数。

jsonpath Accessors

Accessorオペレーター	説明
`.キー` `."$` `varname"`	指定されたキーを持つオブジェクトメンバーを返すメンバーアクセサー。キー名が `$`で始まる名前付き変数と一致する場合、または識別子のJavaScriptルールを満たさない場合は、文字列リテラルにするために二重引用符で囲む必要があります。
`.*`	現在のオブジェクトの最上位レベルにあるすべてのメンバーの値を返すワイルドカードメンバーアクセサー。
`.**`	現在のオブジェクトのJSON階層のすべてのレベルを処理し、ネストレベルに関係なくすべてのメンバー値を返します。これは、SQL/JSON標準のPostgreSQLextensionです。
`.{` `level}` `{` `start_levelからend_level}`	`**`に似ていますが、JSON階層の指定されたレベルのみを選択します。入れ子レベルは整数として指定されます。レベル0は現在のオブジェクトに対応します。最も低いネストレベルにアクセスするには、`last`キーワードを使用します。これは、SQL/JSON標準のPostgreSQLextensionです。
`[添え字, ...]`	配列要素アクセサー`. 下付き文字`2つの形式で与えることができます。`インデックス`または`start_index`へ`end_index`. 最初のフォームは、インデックスによって単一の配列要素を返します。 2番目の形式は、指定された`start_index`および`end_index`に対応する要素を含む、インデックスの範囲ごとの配列スライスを返します。指定された`インデックス`は、整数だけでなく、自動的に整数にキャストされる単一の数値を返す式にすることもできます。インデックス0は、第1の配列要素に対応する。 `last`キーワードを使用して、最後の配列要素を示すこともできます。これは、長さが不明な配列を処理する場合に便利です。
`[*]`	すべての配列要素を返すワイルドカード配列要素アクセサー。