PostgreSQL 10数据库JSON文本字段关联与条件过滤查询咨询

实现方案 #

前置说明 #

你可以根据实际业务的表结构、字段名调整下文中的对应命名：

• 空置建筑表名为 buildings，建筑主键字段为 building_id，空置筛选条件可替换示例中的status = 'vacant'
• 成本表名为 costs，存储用电信息的JSON字段名为 elec_rules，如果你的字段是JSONB类型，把下文中所有json_开头的函数替换为jsonb_即可
• 可自定义参数：@query_date为你要指定的过滤日期，例如'2023-10-01'

具体SQL实现 #

WITH vacant_buildings AS (
    -- 筛选所有空置建筑
    SELECT building_id
    FROM buildings
    WHERE status = 'vacant' -- 替换为实际的空置状态筛选条件
),
all_elec_rules AS (
    -- 展开成本表的嵌套JSON字段，提取所有用电规则
    SELECT
        (rule_item ->> 'id')::int AS building_id, -- 提取建筑ID，字符串类型ID可删::int强转
        (rule_item ->> 'part') AS part_status, -- 提取用电状态
        (rule_item ->> 'active_from')::date AS active_from -- 提取规则生效时间
    FROM costs,
         -- 拆分最外层splitkey_hash对象为键值对
         json_each(costs.elec_rules -> 'splitkey_hash') AS split_keys(key, value),
         -- 把每个建筑对应的规则数组拆分为单行记录
         json_array_elements(split_keys.value) AS rule_item
),
latest_elec_status AS (
    -- 取每个建筑在查询日期前最新生效的用电规则
    SELECT DISTINCT ON (building_id)
        building_id,
        part_status
    FROM all_elec_rules
    WHERE active_from <= @query_date -- 替换为你要查询的指定日期
    ORDER BY building_id, active_from DESC
)
-- 关联空置建筑，筛选出用电激活的结果
SELECT vb.building_id
FROM vacant_buildings vb
INNER JOIN latest_elec_status les
    ON vb.building_id = les.building_id
WHERE les.part_status = '1.0';

补充说明 #

• 上述SQL用到的DISTINCT ON是PostgreSQL原生支持的语法，在10版本上可以正常运行，相比窗口函数写法性能更高、更简洁
• 如果需要返回建筑名称、地址等更多属性，在最终SELECT语句中补充buildings表的对应字段即可
• 如果成本表存在多条记录存储不同周期的用电规则，可在all_elec_rules的CTE中先补充成本表的过滤条件

内容的提问来源于stack exchange，提问作者user2210516