关联表查询并给表里字段在其他表取别名

 

SELECT (
           SELECT json_object_agg(
                          c.name_en, -- 使用别名或原字段名
                          ec.value
                      )
           FROM jsonb_each(to_jsonb(b.*) - 'incident_id') ec -- 展开extend_content的字段为键值对
                    RIGHT JOIN t_incident_field_config c ON ec.key = concat('extend', c.order_ext) -- 关联别名配置
       ) AS extended_content
FROM incident a
         LEFT JOIN extend_content b ON a.incident_id = b.incident_id
limit 10; -- 关联两个表