[解決済み] JSON型内の配列要素を問い合わせる

質問

をテストしようとしているのですが json という型を PostgreSQL 9.3 で使用することができます。

私の場合は json というカラムがあります。 data というテーブルの中で reports . JSONは次のようなものです。

{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}

objects' 配列の 'src' 値に一致するすべてのレポートをテーブルに照会したいのですが、どうすればよいですか？例えば、次のような場合、DBに問い合わせることは可能ですか？ 'src' = 'foo.png' ? をマッチングさせるクエリを書くことに成功しました。 "background" :

SELECT data AS data FROM reports where data->>'background' = 'background.png'

しかし "objects" は値の配列を持っているので、うまく書けそうにありません。と一致するすべてのレポートをDBに問い合わせることは可能でしょうか？ 'src' = 'foo.png' ? これらのソースに目を通しましたが、まだ手に入りません。

• http://www.postgresql.org/docs/9.3/static/functions-json.html
• 新しいPostgreSQL JSONデータ型内のフィールドを使用してクエリを実行するにはどうすればよいですか?
• http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/

などとも試しましたが、効果がありません。

SELECT json_array_elements(data->'objects') AS data from reports
WHERE  data->>'src' = 'foo.png';

私はSQLの専門家ではないので、何が間違っているのかわかりません。

どのように解決するのですか？

jsonb Postgres 9.4+の場合

あなたは 可能 は、以下のように同じクエリを使用します。 jsonb_array_elements() .

しかし、むしろ jsonb 演算子 @> という式に一致するGINインデックスと組み合わせて使用します。 data->'objects' :

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';

キーであるため objects にはJSONの 配列 の場合、検索語の構造に合わせ、配列の要素も角括弧でくくる必要があります。プレーンなレコードを検索する場合は、配列の括弧を削除してください。

より詳しい説明とオプション

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

json Postgres 9.3+の場合

JSON配列のアンネストを行う関数です。 json_array_elements() の横結合で FROM 節を作成し、その要素をテストする。

SELECT data::text, obj
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';

db<>フィドル これ

<サブ 古い sqlfiddle

または、単なる シングル レベルのネスト。

SELECT *
FROM   reports r, json_array_elements(r.data->'objects') obj
WHERE  obj->>'src' = 'foo.png';

->> , -> と #> 演算子については、マニュアルで解説しています。

どちらのクエリも、暗黙のうちに JOIN LATERAL .

密接に関連している。

• JSON列の配列の要素のクエリ