JSONBフィールド値の合計に基づくPostgresql更新レコード列

debugcn 投稿 Dev

rovnyart_

Postgresql 9.6.5に、次のようなフィールドを持つテーブルがあります。

CREATE TABLE test (
  id SERIAL,
  data JSONB,
  amount DOUBLE PRECESION,
  PRIMARY KEY(id)
);

でdata列このようなJSONオブジェクトがあります。

{
"Type": 1,
  "CheckClose":
    {"Payments":
      [
        {"Type": 4, "Amount": 2068.07},
        {"Type": 1, "Amount": 1421.07}
      ]
    }
}

私がする必要があるのはamount、このオブジェクトAmountのPaymentsフィールドの値の合計を各行のフィールドに入れるdataことです。たとえば、この特定のオブジェクトの場合、2068.07 + 1421.07 = 3489.14である必要があります。私はPostgresのjson関数とjsonb関数についていくつか読んだので、今ここにいます：

UPDATE test SET amount=sum((jsonb_array_elements(data::jsonb->'CheckClose'->'Payments')->>'Amount')::FLOAT)

それは機能していません-UPDATEで集計関数を使用しないことについてエラーが発生します。

私はこれを次のようにしようとしました：

UPDATE test SET amount=temp.sum
  FROM (
     SELECT sum((jsonb_array_elements(data::jsonb->'CheckClose'->'Payments')->>'Amount')::FLOAT) AS "sum"
    FROM test WHERE id=test.id
  ) as "temp"

エラーが発生しました set-valued function called in context that cannot accept a set

どうすればよいですか？合計を計算して別の行に入れる必要がありますが、それは難しい作業ですか？誰か、私がこれを理解するのを手伝ってください。ありがとう。

ヴァオツン

fn（）集計を返すセットtry：

t=# with c(j) as (values('{"Payments":
      [
        {"Type": 4, "Amount": 2068.07},
        {"Type": 1, "Amount": 1421.07}
      ]
    }'::jsonb))
select sum((jsonb_array_elements(j->'Payments')->>'Amount')::float) from c;

エラー：

ERROR:  aggregate function calls cannot contain set-returning function calls
LINE 7: select sum((jsonb_array_elements(j->'Payments')->>'Amount'):...
                    ^
HINT:  You might be able to move the set-returning function into a LATERAL FROM item.

別のcteによって簡単に克服することができます：

t=# with c(j) as (values('{"Payments":
      [
        {"Type": 4, "Amount": 2068.07},
        {"Type": 1, "Amount": 1421.07}
      ]
    }'::jsonb))
, a as (select (jsonb_array_elements(j->'Payments')->>'Amount')::float am from c)
select sum(am) from a;
   sum
---------
 3489.14
(1 row)

だから今CTEから更新するだけです：

with s as (SELECT ((jsonb_array_elements(data::jsonb->'CheckClose'->'Payments')->>'Amount')::FLOAT) AS "sm", id
    FROM test
)
, a as (select sum(sm), id from s group by id)
UPDATE test SET amount = sum
FROM a
WHERE id=test.id

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]