文章/答案/技术大牛

发布

社区首页 >问答首页 >查询jsonb列，通过rails中最外层的键获取最内部值的和

问查询jsonb列，通过rails中最外层的键获取最内部值的和
EN

Stack Overflow用户

提问于 2022-08-03 13:18:27

回答 1查看 56关注 0票数 0

我有一个名为组织请求的模型，它有许多列，如服务、国家、请求类型、证书类型。我希望将组织请求计数保存在一个名为Statistics的单独表中。这两个表都在Postgresql中。每天晚上，通过一个rake任务，我将把一整天的计数保存在json格式的统计表中。列名为daily_stats，类型为jsonb。统计表的目的是为了长时间统计。因为在一段时间之后，组织请求将被删除。因此，在这种情况下，统计将是有用的。daily_stats记录的示例：

{"tink": {"sweden": {"api": {"consumer": {"organization_1": 53, "organization_2": 77}, "corporate": {"organization_1": 80, "organization_2": 38}}, "iframe": {"consumer": {"organization_1": 21, "organization_2": 51}, "corporate": {"organization_1": 41, "organization_2": 11}}, "generic_link": {"consumer": {"organization_1": 83, "organization_2": 25}, "corporate": {"organization_1": 84, "organization_2": 45}}, "manual_request": {"consumer": {"organization_1": 64, "organization_2": 97}, "corporate": {"organization_1": 39, "organization_2": 44}}}, "finland": {"api": {"consumer": {"organization_1": 20, "organization_2": 71}, "corporate": {"organization_1": 76, "organization_2": 59}}, "iframe": {"consumer": {"organization_1": 48, "organization_2": 79}, "corporate": {"organization_1": 74, "organization_2": 17}}, "generic_link": {"consumer": {"organization_1": 25, "organization_2": 30}, "corporate": {"organization_1": 52, "organization_2": 3}}, "manual_request": {"consumer": {"organization_1": 2, "organization_2": 2}, "corporate": {"organization_1": 4, "organization_2": 76}}}}, "enable_banking": {"sweden": {"api": {"consumer": {"organization_1": 17, "organization_2": 30}, "corporate": {"organization_1": 89, "organization_2": 23}}, "iframe": {"consumer": {"organization_1": 57, "organization_2": 21}, "corporate": {"organization_1": 9, "organization_2": 55}}, "generic_link": {"consumer": {"organization_1": 18, "organization_2": 86}, "corporate": {"organization_1": 67, "organization_2": 5}}, "manual_request": {"consumer": {"organization_1": 2, "organization_2": 46}, "corporate": {"organization_1": 43, "organization_2": 88}}}, "finland": {"api": {"consumer": {"organization_1": 55, "organization_2": 22}, "corporate": {"organization_1": 38, "organization_2": 54}}, "iframe": {"consumer": {"organization_1": 84, "organization_2": 62}, "corporate": {"organization_1": 51, "organization_2": 60}}, "generic_link": {"consumer": {"organization_1": 74, "organization_2": 84}, "corporate": {"organization_1": 63, "organization_2": 95}}, "manual_request": {"consumer": {"organization_1": 37, "organization_2": 80}, "corporate": {"organization_1": 34, "organization_2": 31}}}}}

我想使用像active record这样的查询从它中获取记录。我试过使用map，但这太复杂了，而且在大量记录上使用map也会影响性能。我搜索了很多，但没有找到一个链接，可以根据不同的条件从jsonb类型中提取数据。

昨天，我从下面的postgresql查询中获得了sum：

select sum(stats.val::numeric) as total
from(
    select key, sum(value::numeric) val
    from statistics t,
    jsonb_each_text(daily_stats->'tink'->'sweden'->'api'->'consumer')
    group by key) as stats;

在这里，我可以得到daily_stats列所有记录在给定的jsonpath上的所有值的总和，但仍然不能使用单键(即"tink“)提取值。我想要一个查询，给出位于最里面的键'organization_1‘和'organization_2’键的所有值的总和。有人能帮忙吗？

ruby-on-rails

database

postgresql

ruby-on-rails-6

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-08-08 06:24:15

这个查询解决了我的问题：

WITH new_table AS (
    select 
       s.service service,
       m.market market, 
       rt.request_type request_type,
       ct.certificate_type certificate_type,
       stats.organization organization,
       (stats.req_count::text)::int req_count
    from statistics b
      cross join json_each(daily_stats::json) as s(service, service_hash)
      cross join json_each(service_hash::json) as m(market, market_hash)
      cross join json_each(market_hash::json) as rt(request_type, request_type_hash)
      cross join json_each(request_type_hash::json) as ct(certificate_type, certificate_type_hash)
      cross join json_each(certificate_type_hash::json) as stats(organization, req_count)
)     

select SUM(req_count) from new_table where service = 'tink' and market = 'sweden' and request_type = 'api' and certificate_type = 'corporate';

在最后一个SELECT语句中，通过更改where子句中的参数，我们可以从statistics表中获得所需的结果。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/73222468

复制

相似问题

问查询jsonb列，通过rails中最外层的键获取最内部值的和
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查询jsonb列，通过rails中最外层的键获取最内部值的和EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查询jsonb列，通过rails中最外层的键获取最内部值的和
EN