[解決済み】新しいPostgreSQL JSONデータ型内のフィールドを使用してクエリを実行するにはどうすればよいですか？

2022-04-17 05:50:53

質問

PostgreSQL 9.2 の新しい JSON 関数について、ドキュメントやサンプルを探しています。

具体的には、一連のJSONレコードが与えられた場合。

[
  {name: "Toby", occupation: "Software Engineer"},
  {name: "Zaphod", occupation: "Galactic President"}
]

名前でレコードを検索するSQLはどのように書けばいいのでしょうか？

バニラSQLで。

SELECT * from json_data WHERE "name" = "Toby"

公式の開発マニュアルはかなりまばらです。

http://www.postgresql.org/docs/devel/static/datatype-json.html
http://www.postgresql.org/docs/devel/static/functions-json.html

アップデートI

をまとめました。 PostgreSQL 9.2 で現在可能なことを詳細に説明した gist . いくつかのカスタム関数を使用すると、次のようなことが可能になります。

SELECT id, json_string(data,'name') FROM things
WHERE json_string(data,'name') LIKE 'G%';

アップデート II

JSON関数を独自のプロジェクトに移動しました。

PostSQL - PostgreSQLとPL/v8を全く素晴らしいJSONドキュメントストアに変換するための関数群です。

解決するには？

ポストグレス9.2

引用 pgsql-hackers メーリングリストの Andrew Dunstan さん。 :

<ブロッククオート

ある段階では、json処理も行われるかもしれません。を生成する) 関数がありますが、9.2 ではありません。

しかし、PLV8であなたの問題を解決するような実装例を提供することを妨げるものではありません。(リンクは現在無効になっています。 PLV8 その代わりに)

ポストグレス9.3

json-processingを追加するための新しい関数と演算子を提供します。

JSONの新機能に関するマニュアルです。
pg 9.3 の新機能に関する Postgres Wiki .

の答えは オリジナルクエスチョン をPostgres 9.3に追加しました。

SELECT *
FROM   json_array_elements(
  '[{"name": "Toby", "occupation": "Software Engineer"},
    {"name": "Zaphod", "occupation": "Galactic President"} ]'
  ) AS elem
WHERE elem->>'name' = 'Toby';

高度な例です。

JSONデータ型のレコードのネストされた配列を持つクエリの組み合わせ

大きなテーブルでは、パフォーマンスを向上させるためにエクスプレッションインデックスを追加することができます。

JSON配列の要素を検索するためのインデックス

ポストグレス9.4

追加 jsonb (b は "binary"、値は Postgres ネイティブの型として保存されます)、さらに多くの機能が どちらも という型があります。上記の式インデックスに加え jsonb もサポートしています。 GIN、btree、ハッシュの各インデックス GINはこれらの中で最も強力なものです。

のマニュアルがあります。 json と jsonb データ型と機能 .
Postgres Wiki の pg 9.4 の JSONB について

マニュアルには、こう書かれています。

一般的には ほとんどのアプリケーションは、JSON データを jsonb ただし、レガシーなものなど、かなり特殊なニーズがある場合はこの限りではありません。オブジェクトのキーの順序に関する仮定。

太字強調は私です。

GINインデックスの一般的な改善によるパフォーマンス上のメリット。

ポストグレス9.5

完了 jsonb 関数と演算子で構成されています。を操作するための関数を追加する。 jsonb をその場で、表示することができます。

Postgres 9.5のリリースノートには、大きな朗報が書かれています。