代码之家  ›  专栏  ›  技术社区  ›  crmepham

如何在Postgresql中对JSONB数组中的值求和?

  •  1
  • crmepham  · 技术社区  · 6 年前

    p06 在桌子上 ryzom_characters :

            -[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        p06 | {
      "id": 675010,
      "cname": "Bob",
      "rpjobs": [
        {
          "progress": 25
        },
        {
          "progress": 13
        },
        {
          "progress": 30
        }
      ]
    }
    

    progress . 我尝试了以下操作:

        SELECT 
    c.cname AS cname,
    jsonb_array_elements(c.p06->'rpjobs')::jsonb->'progress' AS value 
    FROM ryzom_characters c
    Where cid = 675010
    ORDER BY value DESC 
    LIMIT 50;
    

    正确列出了这些值:

     cname  | value
    --------+-------
     Savisi | 30
     Savisi | 25
     Savisi | 13
    (3 rows)
    

    但现在我想对这些值求和,它们可以是空的。

    下面是表格结构:

                         Table "public.ryzom_characters"
        Column     |          Type          | Collation | Nullable | Default
    ---------------+------------------------+-----------+----------+---------
     cid           | bigint                 |           |          |
     cname         | character varying(255) |           | not null |
     p06           | jsonb                  |           |          |
     x01           | jsonb                  |           |          |
    
    2 回复  |  直到 6 年前
        1
  •  2
  •   klin    6 年前

    使用函数 jsonb_array_elements() 在from子句中的横向连接中:

    select cname, sum(coalesce(value, '0')::int) as value
    from (
        select 
            p06->>'cname' as cname, 
            value->>'progress' as value
        from ryzom_characters
        cross join jsonb_array_elements(p06->'rpjobs')
        where cid = 675010
        ) s
    group by cname
    order by value desc 
    limit 50;
    

    可以使用左联接而不是交叉联接来保护查询不受不一致数据的影响:

        left join jsonb_array_elements(p06->'rpjobs')
        on jsonb_typeof(p06->'rpjobs') = 'array'
        where p06->'rpjobs' <> 'null'
    
        2
  •  0
  •   Patrick    6 年前

    功能 jsonb_array_elements() FROM 条款)。调用之后,您将有一个表,其中每一行都包含一个数组元素。从那以后就相对容易了。

    SELECT cname, 
           sum(coalesce(r.prog->>'progress'::int, 0)) AS value  
    FROM ryzom_characters c,
         jsonb_array_elements(c.p06->'rpjobs') r (prog)
    WHERE c.cid = 675010
    GROUP BY cname 
    ORDER BY value DESC 
    LIMIT 50;