PostgreSQLのJSONBクエリ

先ほど、 postgres / psycopg2でjsonbサポートを有効にする方法を書きました 。 今日、JSONのような列のデータを要求する方法を試しました。
このテーマに関するドキュメントはありますが、さまざまな操作がどのように機能するかは完全にはわかりませんでした。

CREATE TABLE json_test ( id serial primary key, data jsonb ); INSERT INTO json_test (data) VALUES ('{}'), ('{"a": 1}'), ('{"a": 2, "b": ["c", "d"]}'), ('{"a": 1, "b": {"c": "d", "e": true}}'), ('{"b": 2}'); 

リクエストは機能しました。チェックするすべてのデータを取得しましょう。

 SELECT * FROM json_test; id | data ----+-------------------------------------- 1 | {} 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} (5 rows) 

次に、結果をフィルタリングします。 使用できる演算子はいくつかありますが、タイプとしてjsonbを選択した理由は後で説明します。

平等
jsonbでは、2つのJSONオブジェクトが同一であることを確認できます。

 SELECT * FROM json_test WHERE data = '{"a":1}'; id | data ----+------ 1 | {"a": 1} (1 row) 

制限事項
また、別のオブジェクトを含むjsonオブジェクトを取得することもできます。 「サブセットであること」：

 SELECT * FROM json_test WHERE data @> '{"a":1}'; 

言う：-キーaと値1で始まるすべてのオブジェクトを教えてください：

  id | data ----+-------------------------------------- 2 | {"a": 1} 4 | {"a": 1, "b": {"c": "d", "e": true}} (2 rows) 

両方向の制限：
この場合、クエリは空のオブジェクトと2番目のオブジェクトの完全一致を出力します。

 SELECT * FROM json_test WHERE data <@ '{"a":1}'; id | data ----+---------- 1 | {} 2 | {"a": 1} (2 rows) 

キー/アイテムの存在
演算子の最後のバッチでは、キー（または配列内の文字列型の要素）の存在を確認します。

  id | data ----+-------------------------------------- 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} (3 rows) 

リストから任意のキーを持つオブジェクトを取得します。

 SELECT * FROM json_test WHERE data ?| array['a', 'b']; id | data ----+-------------------------------------- 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} (4 rows) 

そして、リストから完全に一致するキーを持つオブジェクトのすべての値：

 SELECT * FROM json_test WHERE data ?& array['a', 'b']; id | data ----+-------------------------------------- 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} (2 rows) 

バイパスするキー
key-> pathに一致するレコードをフィルタリングすることもできます。 単純な場合、制限演算子を使用する方が簡単ですが、複雑な演算子ではなく、それらを省くことができません。 これらの操作はSELECTで使用できますが、WHERE句で適用する方がより興味深いです。

 SELECT * FROM json_test WHERE data ->> 'a' > '1'; 

キーaが1である連想要素の値のすべてのレコードを取得します。
数値ではなくテキスト値を使用する必要があることに注意してください。

  id | data ----+--------------------------- 3 | {"a": 2, "b": ["c", "d"]} (1 row) 

オブジェクトと配列のプリミティブを比較できます：

 SELECT * FROM json_test WHERE data -> 'b' > '1'; id | data ----+-------------------------------------- 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} (3 rows) 

配列とオブジェクトは数字以上のものであることがわかります。
より深い道も見ることができます。

 SELECT * FROM json_test WHERE data #> '{b,c}' = '"d"'; 

要素bに子オブジェクトcがあり、cが文字列「d」に等しいオブジェクトを取得します。

 id | data ----+-------------------------------------- 4 | {"a": 1, "b": {"c": "d", "e": true}} 

JSONオブジェクトではなく、テキストを返すこれらのステートメントのバージョンもあります。 最後のリクエストの場合、これは（実際に文字列を取得したいバージョンの）JSONオブジェクトと比較する必要がないことを意味します。

 SELECT * FROM json_test WHERE data #>> '{b,c}' = 'd'; id | data ----+-------------------------------------- 4 | {"a": 1, "b": {"c": "d", "e": true}} (1 row) 

したがって、ここまではすべて問題ありません。 異なるデータを扱うことができ、jsonbインデックスでも同じデータを使用できます。 ただし、より注意深い読者は、ルートからのオブジェクトパスを持つJSONデータを処理していることに気付いているかもしれません。 これは次のようにする必要はありません。配列も有効なJSONであり、実際に有効な例は次のとおりです。

 SELECT 'null'::json, 'true'::json, 'false'::json, '2'::json, '1.0001'::json, '"abc"'::json, '1E7'::jsonb; 

最後のエントリは、jsonb型であり、標準形式に変換されることに注意してください。

  json | json | json | json | json | json | jsonb ------+------+-------+------+---------+-------+---------- null | true | false | 2 | 1.00001 | "abc" | 10000000 (1 row) 

JSON nullもSQL NULLとは異なります。
では、混合型のオブジェクトをJSON列に格納するとどうなりますか？

 INSERT INTO json_test (data) VALUES ('[]'), ('[1,2,"a"]'), ('null'), ('1E7'), ('"abc"'); SELECT * FROM json_test; id | data ----+-------------------------------------- 1 | {} 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} 6 | [] 7 | [1, 2, "a"] 8 | null 9 | 10000000 10 | "abc" (10 rows) 

構造全体が問題なく推定されました。 これらのオブジェクトとクエリを使用できるかどうかを確認しましょう。
同等性チェックは正常に機能します。

 SELECT * FROM json_test WHERE data = '{"a":1}'; SELECT * FROM json_test WHERE data = 'null'; 

制限も期待どおりに機能します。

 SELECT * FROM json_test WHERE data @> '{"a":1}'; SELECT * FROM json_test WHERE data <@ '{"a":1}'; 

キーと既存のアイテムも機能します。 当然のことながら、単一のリクエストは、配列の要素とオブジェクトのキーに一致します。

 SELECT * FROM json_test WHERE data ? 'a'; id | data ----+-------------------------------------- 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 7 | [1, 2, "a"] (4 rows) 

 SELECT * FROM json_test WHERE data ?| array['a', 'b']; id | data ----+-------------------------------------- 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} 7 | [1, 2, "a"] (5 rows) 

 SELECT * FROM json_test WHERE data ?& array['a', 'b']; id | data ----+-------------------------------------- 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} (2 rows) 

しかし、「get」のキーまたは要素を作成し始めるとすぐに、問題が発生します。

（どうやら、この記事の著者は執筆時点でPotgreSQL 9.4 bettaバージョンをインストールしていたため、クエリの一部にエラーがあり、9.4.1ですべてのクエリが処理されました）：

 SELECT * FROM json_test WHERE data ->> 'a' > '1'; ERROR: cannot call jsonb_object_field_text (jsonb ->> text operator) on an array 

非スカラー値がある場合でも、キーパスバイパスを使用できます。

 SELECT * FROM json_test WHERE data #> '{b,c}' = '"d"'; ERROR: cannot call extract path from a scalar SELECT * FROM json_test WHERE data #> '{b,c}' = '"d"' AND id < 8; id | data ----+-------------------------------------- 4 | {"a": 1, "b": {"c": "d", "e": true}} (1 row) 

文字列（jsonキーが必要）または整数（配列インデックス）のキーパスの構文に注意してください。
これは非常に厳しい制限を課します。 MondgoDBでこのようなことがどのように機能するかはわかりません。

しかし、将来的に、配列とjsonオブジェクトのデータを1つの列に格納する場合、将来、いくつかの問題が発生する可能性があります。 しかし、すべてが失われるわけではありません。 ベースオブジェクトに基づいて文字列を取得できます。

 SELECT * FROM json_test WHERE data @> '{}'; id | data ----+-------------------------------------- 1 | {} 2 | {"a": 1} 3 | {"a": 2, "b": ["c", "d"]} 4 | {"a": 1, "b": {"c": "d", "e": true}} 5 | {"b": 2} (5 rows) 

次に、このリクエストを上記のリクエストと組み合わせることができます。

 SELECT * FROM json_test WHERE data @> '{}' AND data ->> 'a' > '1'; id | data ----+--------------------------- 3 | {"a": 2, "b": ["c", "d"]} (1 row) 

実際、Postgresでは、データ@> '{}が最初に来ることを確認する必要すらありません。
しかし、配列データ型のみが必要な場合はどうでしょうか？ 同じトリックを使用できることがわかりました。

 SELECT * FROM json_test WHERE data @> '[]'; id | data ----+------------- 6 | [] 7 | [1, 2, "a"] (2 rows) 

また、他の演算子と組み合わせることができます。

 SELECT * FROM json_test WHERE data @> '[]' AND data ->> 1 = '2'; id | data ----+------------- 7 | [1, 2, "a"] (1 row) 

@>演算子のエントリはjsonb列でのみ使用できるため、通常のjson列の混合データをリクエストすることはできません。

次は？

Postgresのjsonbはサードパーティのプロジェクトであると考え、現在ORM djangoでjson（b）クエリに取り組んでいます。 Django 1.7では、次のような検索関数を記述できます。

 # Exact MyModel.objects.filter(data={'a': 1}) MyModel.objects.exclude(data={}) # Key/element existence MyModel.objects.filter(data__has='a') MyModel.objects.filter(data__has_any=['a', 'b']) MyModel.objects.filter(data__has_all=['a', 'b']) # Sub/superset of key/value pair testing MyModel.objects.filter(data__contains={'a': 1}) MyModel.objects.filter(data__in={'a': 1, 'b': 2}) # Get element/field (compare with json) MyModel.objects.filter(data__get=(2, {'a': 1})) # Get element/field (compare with scalar, including gt/lt comparisons) MyModel.objects.filter(data__get=(2, 'a')) MyModel.objects.filter(data__get__gt=('a', 1)) # key path traversal, compare with json or scalar. MyModel.objects.filter(data__get=('{a,2}', {'foo': 'bar'})) MyModel.objects.filter(data__get=('{a,2}', 2)) MyModel.objects.filter(data__get__lte=('{a,2}', 2)) 

しかし、最後のセットの名前が機能するかどうかはわかりません。 「get」という名前は少し普遍的であり、入力タイプの代わりに別の名前を使用できますが、整数と文字列のみが有効です。