今天的问题是这样的，PostgreSQL数据库中有张表（t_payment），表中有个jsonb字段（invinfo），表大概长这样（隐去了不必要的字段）

CREATE TABLE "public"."t_payment" (
  "pno" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "invinfo" jsonb,
  CONSTRAINT "t_payment_pkey" PRIMARY KEY ("pno")
);

invinfo中存的是一个json数组，大概长这样：

[
    {
        "invno": "FP0001",
        "invmoney": 539.4
    },
    {
        "invno": "FP0002",
        "invmoney": 539.4
    },
    {
        "invno": "FP0003",
        "invmoney": 539.4
    },
    {
        "invno": "FP0004",
        "invmoney": 539.4
    }
]

造一条数据

INSERT INTO t_payment("pno", "invinfo") VALUES ('FP001', '[{"invno":"FP0001","invmoney":539.4},{"invno":"FP0002","invmoney":539.4},{"invno":"FP0003","invmoney":539.4},{"invno":"FP0004","invmoney":539.4}]');

问题1

现在要将数组中的invno取出并拼接，invtmoney取出并求和，最终结果类似：

FP0001,FP0002,FP0003,FP0004 2157.6

直接说结果：

WITH t1 AS (
    SELECT
        pno,
        jsonb_array_elements ( invinfo ) ->> 'invno' AS invno,
        CAST ( jsonb_array_elements ( invinfo ) ->> 'invmoney' AS FLOAT ) AS invmoney 
    FROM
        t_payment 
    ) 
SELECT
    pno,
    string_agg ( invno, ',' ) AS invno,
    SUM ( invmoney ) AS invmoney 
FROM
    t1 
GROUP BY
    pno

问题2

需要更新数组中的invmoney，并把invno作为条件，比如

    {
        "invno": "FP0001",
        "invmoney": 11111
    }

其他都保持不变
直接说结果：

--数组中的全部元素
with t1 as (
    select jsonb_array_elements(invinfo) as e FROM t_payment where pno = 'FP001'
)
--需要更新的元素
,t2 as(
    select e from t1 where e @> '{"invno":"FP0001"}'::jsonb
)
--需要保留的元素
,t3 as(
    select * from t1 where e != (select e from t2)
)
--更新后的元素
,t4 as(
select e||'{"invmoney":11111}' as e  from t2
)
--更新后的全部元素
,t5 as(
select * from t3 union select * from t4
)
--select jsonb_agg(e) from t5
update t_payment set invinfo = (select jsonb_agg(e) from t5) where pno = 'FP001'

看起来很直观吧，但是每次这么写一堆太多了（别问，问就是懒），组装成一个函数吧

CREATE OR REPLACE FUNCTION "public"."json_array_update_key"(  _elements jsonb, _key jsonb, _value jsonb)
  RETURNS "json"
    LANGUAGE sql
    AS $$
--数组中的全部元素
with t1 as (
    select jsonb_array_elements(_elements) as e
)
--需要更新的元素
,t2 as(
    select e from t1 where e @> _key
)
--需要保留的元素
,t3 as(
    select * from t1 where e != (select e from t2)
)
--需要后的元素
,t4 as(
select e||_value as e  from t2
)
--更新后的全部元素
,t5 as(
select * from t3 union select * from t4
)
select jsonb_agg(e) from t5;
$$

然后更新sql就简单啦：

update t_payment set invinfo = json_array_update_key(invinfo, '{"invno":"FP0001"}', '{"invmoney":2222222}') where pno = 'FP001'