达梦数据库学习之四:实现查询结果导出Json

bestyellow

已于 2022-11-18 09:43:43 修改

阅读量3.8k

点赞数 1

分类专栏：达梦兼容文章标签：数据库学习 sql

于 2022-11-13 15:18:59 首次发布

本文链接：https://blog.csdn.net/bestyellow/article/details/127832203

版权

达梦同时被 2 个专栏收录

7 篇文章 2 订阅

订阅专栏

兼容

3 篇文章 0 订阅

订阅专栏

该博客介绍了如何在达梦数据库中利用JSONB_AGG和JSONB_BUILD_OBJECT函数来构建和聚合JSON数据。通过示例展示了如何从KT_U_User表中选取特定条件的数据，并转换成符合要求的JSON格式，同时也给出了处理无num_launches情况下的数据聚合方法。

摘要由CSDN通过智能技术生成

在实际应用中，我们经常要用到把查询的结果导出来Json中，在Sql中有很简单的实现方式，请阅读我之前的文章：

sql server 导出Json(实际应用)_bestyellow的博客-CSDN博客_sql 生成json实际用到的表结构，在sql server2016以上版中使用FOR JSON PATH来保持对 JSON 输出格式的完全控制。你可以创建包装对象并嵌套复杂属性。goDROP TABLE IF EXISTS #tabStudent;DROP TABLE IF EXISTS #tabClass;goCREATE TABLE #tabClass( ClassGuid uniqueIdentifier not null default newid(), Cl..https://blog.csdn.net/bestyellow/article/details/124537149

但是在达梦中，没有直接生成的，便是也提供了许多这样的函数，可以参考JSON | 达梦技术文档JSON（JavaScript Object Notation）是完全独立于语言的文本格式，是一种轻量级的数据交换格式。 JSONB（JavaScript Object Notation Binary）与 JSON 基本类似，区别在于 JSON 将数据保存为文本格式，而 JSONB 将数据保存为二进制格式。 DM 数据库支持对 JSON 数据进行存储和查询。在 DM 数据库中 JSON 数据以字https://eco.dameng.com/document/dm/zh-cn/pm/json.html#18-2-9-jsonb-object-agg

通过里面的 JSONB_AGG与 JSONB_BUILD_OBJECT 函数就可以实现上面的查询Json结果

select JSONB_AGG(val) from (
   SELECT JSONB_BUILD_OBJECT('UserNo', "UserNo", 'UserId', "UserId", 'UserName',"UserName") as val FROM "KT_U_User" where "UserName" Like '江%');

jsonb_build_object
jsonb_build_object 根据指定的“名称”和“值”创建 JSONB 对象。
语法格式
<jsonb_build_object函数> ::= jsonb_build_object(<exp1>, <exp2> {, <exp3>, <exp4>})
参数
<exp1>：指定“名称”，数据类型为 VARCHAR。
<exp2>：指定“值”，数据类型可以为任意类型。
返回值
JSONB 对象。
使用说明
支持指定多个“名称”和“值”，jsonb_build_object 参数的个数必须为偶数。

举例说明
例根据表 KT_U_User中姓名中包含有 “二” 的各列数据创建 JSONB 对象。

SELECT JSONB_BUILD_OBJECT('UserNo', UserNo, 'UserId', UserId, 'UserName',UserName) as val FROM KT_U_User where UserName Like '%二%'

jsonb_agg
jsonb_agg 为集函数，将指定数据聚合成一个 JSONB 数组。
语法格式
<jsonb_agg函数> ::= jsonb_agg(<exp>)
参数
<exp>：指定数据，数据类型可以为任意类型。
返回值
JSONB 数组。
使用说明
支持在<exp> 参数前指定 DISTINCT 关键字，即 jsonb_agg(DISTINCT <exp>)，表示对<exp> 进行去重操作。
举例说明
例 1 将表 KT_U_User 中姓名中包含有 “二” 的各列数据的 UserName 列数据聚合成一个 JSONB 数组。

   select JSONB_AGG(UserName) FROM KT_U_User where UserName Like '%二%';

最后就是综合得出我们要想的数据了

select JSONB_AGG(val) from (
   SELECT JSONB_BUILD_OBJECT('UserNo', "UserNo", 'UserId', "UserId", 'UserName',"UserName") as val FROM "KT_U_User" where "UserName" Like '江%');

下面的是转的

-----------------------------------------------------------------------------------------

sql - 如何使用 `jsonb_build_object` 创建具有聚合函数和动态键值的嵌套 JSON 返回 - IT工具网

----------------------------------------------------------------------------------------

如何使用 "jsonb_build_object" 创建具有聚合函数和动态键值的嵌套 JSON 返回
表格的示例？

+---------------------+------------------+------------------+
| country_code | region | num_launches |
+---------------------+------------------+------------------+
| 'CA' | 'Ontario' | 5 |
+---------------------+------------------+------------------+
| 'CA' | 'Quebec' | 9 |
+---------------------+------------------+------------------+
| 'DE' | 'Bavaria' | 15 |
+---------------------+------------------+------------------+
| 'DE' | 'Saarland' | 12 |
+---------------------+------------------+------------------+
| 'DE' | 'Berlin' | 23 |
+---------------------+------------------+------------------+
| 'JP' | 'Tokyo' | 19 |
+---------------------+------------------+------------------+

我能够编写一个返回每个 country_code 的查询与所有 regions嵌套在其中，但我无法得到我正在寻找的东西。我的预期返回看起来像。
[
{ 'CA': [
{ 'Ontario': 5 },
{ 'Quebec': 9 }
]
},
{ 'DE': [
{ 'Bavaria': 15 },
{ 'Saarland': 12 },
{ 'Berlin': 23 }
]
},
{ 'JP': [
{ 'Tokyo': 19 }
]
}
]

如果 num_launches不可用？

+---------------------+------------------+
| country_code | region |
+---------------------+------------------+
| 'CA' | 'Ontario' |
+---------------------+------------------+
| 'CA' | 'Ontario' |
+---------------------+------------------+
| 'CA' | 'Ontario' |
+---------------------+------------------+
| 'CA' | 'Quebec' |
+---------------------+------------------+
| 'CA' | 'Quebec' |
+---------------------+------------------+
| 'DE' | 'Bavaria' |
+---------------------+------------------+
| 'DE' | 'Bavaria' |
+---------------------+------------------+
| 'DE' | 'Bavaria' |
+---------------------+------------------+
| 'DE' | 'Bavaria' |
+---------------------+------------------+
| 'DE' | 'Saarland' |
+---------------------+------------------+
| 'DE' | 'Berlin' |
+---------------------+------------------+
| 'DE' | 'Berlin' |
+---------------------+------------------+
| 'JP' | 'Tokyo' |
+---------------------+------------------+

预期 yield
[
{ 'CA': [
{ 'Ontario': 3 },
{ 'Quebec': 2 }
]
},
{ 'DE': [
{ 'Bavaria': 4 },
{ 'Saarland': 1 },
{ 'Berlin': 2 }
]
},
{ 'JP': [
{ 'Tokyo': 1 }
]
}
]

您可以尝试使用json_agg与 json_build_object函数在子查询中获取数组，然后在主查询中再次执行。

CREATE TABLE T(
country_code varchar(50),
region varchar(50),
num_launches int
);

insert into t values ('CA','Ontario',5);
insert into t values ('CA','Quebec',9);
insert into t values ('DE','Bavaria',15);
insert into t values ('DE','Saarland',12);
insert into t values ('DE','Berlin',23);
insert into t values ('JP','Tokyo',19);

查询 #1
select json_agg(json_build_object(country_code,arr)) results
from (
SELECT country_code,
json_agg(json_build_object(region,num_launches)) arr
FROM T
group by country_code
) t1;

结果
[{"CA":[{"Ontario":5},{"Quebec":9}]},{"DE":[{"Bavaria":15},{"Saarland":12},{"Berlin":23}]},{"JP":[{"Tokyo":19}]}]