sql - 如何根据键模式从 Postgress 中的 json 中提取值?
问题描述
在 Postgres v9.6.15 中,我需要使用某些键的文本模式来获取所有值的总和。
例如,有 4 行的表“TABLE1”:
|group |col1
======================================
Row #1:|group1 |{ "json_key1.id1" : 1 }
Row #2:|group1 |{ "json_key1.id2" : 1 }
Row #3:|group1 |{ "json_key2.idX" : 1 }
Row #4:|group1 |{ "not_me" : 2 }
我想使用键的第一部分( "json_key1" 和 "json_key2" )的模式获取 int 值,并使用 CASE 块对它们进行求和,如下所示:
SELECT table1.group as group,
COALESCE(sum(
CASE
WHEN table1.col1 = 'col1_val1' THEN (table1.json_col->>'json_key1.%')::bigint
WHEN table1.col1 = 'col1_val2' THEN (table1.json_col->>'json_key2.%')::bigint
ELSE 0::bigint
END), 0)::bigint AS my_result
FROM table1 as table1
GROUP BY table1.group;
我需要“my_result”看起来像:
|group |my_result
======================================
Row #1:|group1 |3
有没有办法使用正则表达式或类似的东西来收集值。不确定我是否检查了正确的文档(https://www.postgresql.org/docs/9.6/functions-json.html),但我没有找到任何可以帮助我实现上述目标的东西,或者如果那实际上是可能的..
解决方案
在横向连接中使用以从 json 对象jsonb_each_text()
中获取对:(key, value)
select
group_col as "group",
coalesce(sum(case when key like 'json_k%' then value::numeric end), 0) as my_result
from table1
cross join jsonb_each_text(col1)
group by group_col
推荐阅读
- php - woocommerce 客户城市将通过“新订单”电子邮件发送
- c++ - 无法在预处理器指令中使用 printf
- html - 如何在不更改其他元素大小的情况下将隐藏元素添加到 flex 父元素?
- bash - sed 仅替换第一次出现,然后仅替换第二次出现,然后仅替换第三次
- haskell - Haskell 工具栈和可执行文件大小
- php - 如果复选框选中已更改,则更改 td 上的类
- amazon-web-services - AWS Workspaces CLI:“开始 - 工作区”的语法
- python - 如何从 Excel 工作表创建嵌套字典并将它们合并在一起
- html - 如何更改自动建议反应组件中 ul li 元素的背景颜色?
- nestjs - Nestjs 服务级缓存