Postgres -如何从JSON列中搜索和聚合
Postgres -如何从JSON列中搜索和聚合
提问于 2019-06-05 19:33:22
我有一个asset_quantities表，如下所示
id | asset_type | quantity | site_id | asset_ids_json
1 'Container' 3 1 [{"id":1,"make":"am1","model":"amo1"},{"id":2,"make":"am1","model":"amo2"},{"id":3,"make":"am3","model":"amo3"}]
2 'Cage' 3 1 [{"id":4,"make":"bm1","model":"bmo1"},{"id":5,"make":"bm2","model":"bmo2"},{"id":6,"make":"bm2","model":"cmo3"}]
3 'Crate' 3 1 [{"id":7,"make":"cm1","model":"cmo1"},{"id":8,"make":"cm1","model":"cmo1"},{"id":9,"make":"cm1","model":"cmo2"}]我想用Postgres编写一个SQL查询，它将为我提供给定品牌或型号的每种资产类型的数量计数。
例如，如果我想获取每个资产类型的数量，其中make='am1'，
site_id | Container_qty | Cage_qty | Crate_qty
1 2 0 0例如，如果我想获取每个资产类型的数量，其中make='cm1'，结果集将如下所示
site_id | Container_qty | Cage_qty | Crate_qty
1 0 0 3我已经编写了下面的查询来将'asset_type‘行中的值旋转到列中，但是我不知道如何根据字段'asset_ids_json’中的属性来过滤和聚合计数。可以安全地假设asset_ids_json中的json数组的长度将始终与'quantity‘列中的值相同。
select
aq.site_id,
sum(case when aq.asset_type = 'Container' then aq.quantity end) container_qty,
sum(case when aq.asset_type = 'Cage' then aq.quantity end) cage_qty ,
sum(case when aq.asset_type = 'Crate' then aq.quantity end) crate_qty,
from asset_quantities aq
group by aq.site_id;我的问题的关键是如何根据json列'asset_ids_json‘中的属性过滤和聚合结果。我使用的是Postgres 9.4。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-06-05 21:22:13
SELECT
site_id,
SUM(case when asset_type = 'Container' then quantity end) container_qty,
SUM(case when asset_type = 'Cage' then quantity end) cage_qty ,
SUM(case when asset_type = 'Crate' then quantity end) crate_qty
FROM (
SELECT DISTINCT ON (id)
site_id,
asset_type,
quantity
FROM asset_quantities aq,
json_array_elements(asset_ids_json)
WHERE value ->> 'make' = 'cm1'
) s
GROUP BY site_id要获得JSON数组内容上的WHERE子句，必须展开该数组。json_array_elements()为每个元素创建一行。这样就有可能要求一个特定值。
由于这种扩展，当前行被乘以(这里是三倍，因为数组中有三个元素)。因为您只对简单地复制到新记录中的原始site_id、asset_type和quantity数据感兴趣，所以可以使用DISTINCT消除它们。DISTINCT ON检查每个id的不同值。因此，如果两个JSON数组包含相同键/值，则将保存这两个数组。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56459674
复制相关文章
相似问题
腾讯云开发者
