pgsql中主子表数据使用json_agg和json_build_object汇聚

原创

已于 2022-12-08 19:54:34 修改 · 1.8k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#json #json_agg #主子表数据汇聚 #pgsql

于 2022-12-08 19:46:02 首次发布

本文介绍了一种使用json_build_object和json_agg函数将多条记录聚合为单一JSON对象的方法，解决了前端展示时需要将多个相关联的数据库记录整合为一条数据的需求。

在实际的业务场景中，我们会经常遇到主从关系的数据，所以相应的数据库层面，我们会根据需要把表设置成主子表的形式，方便存储数据，这就导致了另一个问题，比如查询数据，主子表关联的时候，数据会变成多条，但是使用方（比如手机端、前端）希望是返回一条数据,而且他们需要的还不止一个字段，如果是一个字段，我反手就整个array_to_string(ARRAY_AGG(column_name::VARCHAR),',') as return_name 给处理了，只能另想办法，我第一时间想到了我以前写的一篇博客（感兴趣的小伙伴可以点此处去看看），内心狂喜，能copy的代码，我是一点都不想写，当我把那段代码copy到我现在项目中的时候，发现有个标签在当前项目使用的mybatis版本中不支持，内心瞬间拔凉拔凉，好在像我这样的摸鱼者，肯定还有plan B,这就是我今天要介绍的json_build_object。

国际惯例，直接上代码：

SELECT
	dm.ds_id,--关联主表的字段
	json_agg(
		json_build_object(
			'rule_name','名称',-- 部分需要写死的返回数据
			'column_id',id::VARCHAR,--前面是json的key（key自己定义）,后面是vlue（具体要取的表字段值）
			'type',type,
			'operator', operator
		)
	) AS children_data  -- json_agg 最后组装成数组
	FROM kenvueotc_display_metric dm
	WHERE dm.status = '1' --可以放过滤条件
	GROUP BY