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

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

jsonb psql postgresql sql

crmepham · 技术社区 · 8 年前

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 回复 | 直到 8 年前

klin 8 年前

使用函数 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'

Patrick 8 年前

功能 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;

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

1 年前

Kallydi · jdbc无法连接到docker中的postgresql数据库

1 年前

dietzi96 · Pandas DataFrame.to_sql随机和静默地失败,没有错误消息

1 年前

Peter Schofield · 类型转换Postgresql

1 年前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

1 年前

Kevin Smeeks · Pyspark JDBC分区读取

1 年前

Micah Armantrout · 如何使用sql脚本将数据插入到代码优先的数据库中?

1 年前

Vanortton · 使用Docker开始应用程序开发还是在开发过程中配置Docker?[关闭]

1 年前

Andrus · 如何在sql中查找第二个匹配项

1 年前

MWood · Postgres jsonpath用于引用单例元素或元素数组

1 年前