☰

Postgres でオブジェクトの JSON 配列をクエリする 質問する

tag-icon • tag-icon json postgresql
Postgres でオブジェクトの JSON 配列をクエリする 質問する

私は JSON データ フィールドを持つ Postgres データベースを持っています。私が持っている JSON はオブジェクトの配列です:

[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]

JSON 配列内の特定のキーの値を返そうとしているので、上記の例では、名前の値を返したいと思います。

次のクエリを使用すると、NULL 値が返されます。

SELECT data->'name' AS name FROM json_test

これはオブジェクトの配列だからだと思います。名前キーを直接アドレス指定することは可能ですか?

最終的に必要なのは、すべての一意の名前の数を返すことですが、これは可能ですか?

ありがとう！

ベストアンサー1

あなたがしなければならない巣を壊すまず関数を使用してJSONオブジェクトの配列（json_array_elements またはjsonb_array_elementsjsonbデータ型の場合）の場合は、キーを指定して値にアクセスできます。

WITH json_test (col) AS (
  values (json '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT
  y.x->'name' "name"
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y

-- outputs:
name
--------------
"Mickey Mouse"
"Donald Duck"

一意の名前の数を取得するには、count distinctive集計関数が適用されていることを除いて、上記と同様のクエリを使用します。y.x->>name

WITH json_test (col) AS (
  values (json '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT
  COUNT( DISTINCT y.x->>'name') distinct_names
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y

前者 ( )->>は抽出された値をテキストとしてキャストし、等価比較 (個別のカウントに必要) をサポートしますが、後者 ( ) は値を json として抽出し、等価比較をサポートしないため、の代わりに を使用する必要があります。->->>->

あるいは、jsonを に変換してjsonbを使用しますjsonb_array_elements。JSONBは等価比較をサポートしているため、 による抽出とともに COUNT DISTINCT を使用することが可能です->。

COUNT(DISTINCT (y.x::jsonb)->'name')

postgresql バージョン 12 以降向けの更新された回答

クエリされたフィールドが json ではなく jsonb である限り、jsonb パス クエリを使用してオブジェクトのリストから特定のキーを抽出/ネスト解除できるようになりました。

例：

WITH json_test (col) AS (
  values (jsonb '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT jsonb_path_query(col, '$[*].name') "name"
FROM json_test
-- replaces this original snippet:
-- SELECT
--   y.x->'name' "name"
-- FROM json_test jt, 
-- LATERAL (SELECT json_array_elements(jt.col) x) y

おすすめ記事