当前位置：首页 > news >正文

DBAPI如何用SQL将多表关联查询出树状结构数据（嵌套JSON格式）

news 来源：原创 2024/9/20 15:40:22

场景描述

假设数据库中有3张表如下：

客户信息表

订单表

订单详情表

一个客户有多个订单，一个订单包含多个产品信息，客户-订单-产品就构成了3级的树状结构，如何查询出如下树状结构数据呢？

[{"customer_age": 50,"orders": [{"order_phone": "15300000000","order_address": "广东省广州市","order_id": 60001,"products": [{"product": "手机","num": 1}]}, {"order_phone": "15300000000","order_address": "广东省深圳市","order_id": 60002,"products": [{"product": "电脑","num": 2}, {"product": "足球","num": 1}]}],"customer_name": "刘德华","customer_id": 5001}, {"customer_age": 20,"orders": [{"order_phone": "13200000000","order_address": "上海市","order_id": 60003,"products": [{"product": "口红","num": 1}, {"product": "衣服","num": 2}]}],"customer_name": "刘亦菲","customer_id": 5002}
]

案例实操

数据准备

用SQL关联查询出所有信息

可以看到只要对这个结果进行两次分组，就可以得到树状结构数据了。第一次按customer_id分组，得到一个客户下的所有订单信息，然后对订单信息按order_id分组，得到一个订单下的所有产品信息。这个就是实现思路，DBAPI官方已经提供了插件来进行数据格式转换。

插件安装

从官网进入插件市场

下载高级树状结构数据插件

将下载的插件放入DBAPI安装目录下的lib目录并重启DBAPI即可

创建API

填写关联查询的SQL语句，选择插件并填写插件参数如下：

[{"groupBy": "customer_id","childrenName": "orders","fields": ["customer_name", "customer_age"]}, {"groupBy": "order_id","childrenName": "products","fields": ["order_address", "order_phone"]}
]

插件参数的意思就是第一次按customer_id分组，得到一个客户下的所有订单信息并命名为 orders ，同时取出 customer_name customer_age 两个属性;

然后对订单信息按order_id分组，得到一个订单下的所有产品信息并命名为 products，同时取出 order_address order_phone 两个属性；

最后一级（第三级）就是订单中的所有产品信息，有哪些字段就是取决于SQL查询出的所有字段去除掉前面父级用掉的字段后，剩下的字段。

测试数据

发送请求可以看到数据已经按照树状结构返回了

数据格式化后如下

{"data": [{"customer_age": 50,"orders": [{"order_phone": "15300000000","order_address": "广东省广州市","order_id": 60001,"products": [{"product": "手机","num": 1}]}, {"order_phone": "15300000000","order_address": "广东省深圳市","order_id": 60002,"products": [{"product": "电脑","num": 2}, {"product": "足球","num": 1}]}],"customer_name": "刘德华","customer_id": 5001}, {"customer_age": 20,"orders": [{"order_phone": "13200000000","order_address": "上海市","order_id": 60003,"products": [{"product": "口红","num": 1}, {"product": "衣服","num": 2}]}],"customer_name": "刘亦菲","customer_id": 5002}],"msg": null,"success": true
}