在WEB应用或图展示的程序中通常使用Json格式传输，JavaScript Object Notation (JSON) 是一种轻量级的数据传输格式,在关系型数据库中通常存储的是表、列格式，如果数据库可以直接返回JSON, 那基于JSON传输的应用将会收益，JSON 是一种完全独立于语言的文本格式，使 JSON 成为理想的数据交换语言。 在PostGreSQL和ORACLE 12.2中都提供了JSON的SQL函数， Oracle Database 21c 中添加了原生 JSON 数据类型，这针对查询和 DML 处理进行了优化。加快处理 JSON 文档的速度。从 21c 开始，这是存储大型 JSON 文档的最佳类型。Oracle Database 19c 引入了一个新选项：使用 JSON_mergepatch 进行 JSON 修补。这仅替换了文档的相关部分，Oracle Database 21c提供了一种更好的方法：JSON_transform。Oracle Database 21c提供了一种更好的方法：JSON_transform，JSON_transform 具有强大的 JSON 操作功能，允许您添加、删除和更改文档中的值，与JSON_mergepatch 不同，您可以针对要更改的特定属性。 下面记录一下PostgreSQL和Oracle返回Json的方法。
PostGreSQL
sdbo=# select version();
version
------------------------------------------------------------
PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit
sdbo=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+-----------------+--------+--------
public | sdbo_department | 数据表 | weejar
(1 行记录)
sdbo=# select * from sdbo_department;
dep_id | dep_name
--------+----------
1 | mgr
(1 行记录)
sdbo=# insert into sdbo_department values (2,'dev');
INSERT 0 1
sdbo=# select row_to_json(sdbo_department) from sdbo_department;
row_to_json
-------------------------------
{"dep_id":1,"dep_name":"mgr"}
{"dep_id":2,"dep_name":"dev"}
(2 行记录)
sdbo=# alter table sdbo_department add sex char(1);
ALTER TABLE
sdbo=# select row_to_json(sdbo_department) from sdbo_department;
row_to_json
------------------------------------------
{"dep_id":1,"dep_name":"mgr","sex":null}
{"dep_id":2,"dep_name":"dev","sex":null}
(2 行记录)
sdbo=# insert into sdbo_department values (3,'test',1);
INSERT 0 1
sdbo=# select row_to_json(sdbo_department) from sdbo_department;
row_to_json
------------------------------------------
{"dep_id":1,"dep_name":"mgr","sex":null}
{"dep_id":2,"dep_name":"dev","sex":null}
{"dep_id":3,"dep_name":"test","sex":"1"}
(3 行记录)
sdbo=# select row_to_json(row(dep_id,dep_name)) from sdbo_department;
row_to_json
----------------------
{"f1":1,"f2":"mgr"}
{"f1":2,"f2":"dev"}
{"f1":3,"f2":"test"}
(3 行记录)
sdbo=# select json_build_object('id',dep_id,'name',dep_name) from sdbo_department;
json_build_object
-----------------------------
{"id" : 1, "name" : "mgr"}
{"id" : 2, "name" : "dev"}
{"id" : 3, "name" : "test"}
(3 行记录)
sdbo=# insert into sdbo_department values (3,'test2',1);
INSERT 0 1
sdbo=# select * from sdbo_department;
dep_id | dep_name | sex
--------+----------+-----
1 | mgr |
2 | dev |
3 | test | 1
3 | test2 | 1
(4 行记录)
sdbo=# select json_agg(sdbo_department) from sdbo_department group by dep_id;
json_agg
---------------------------------------------
[{"dep_id":3,"dep_name":"test","sex":"1"}, +
{"dep_id":3,"dep_name":"test2","sex":"1"}]
[{"dep_id":2,"dep_name":"dev","sex":null}]
[{"dep_id":1,"dep_name":"mgr","sex":null}]
(3 行记录)
sdbo=# select json_agg(dep_name) from sdbo_department group by dep_id;
json_agg
-------------------
["test", "test2"]
["dev"]
["mgr"]
(3 行记录)
sdbo=# select dep_id,json_agg(dep_name) from sdbo_department group by dep_id;
dep_id | json_agg
--------+-------------------
3 | ["test", "test2"]
2 | ["dev"]
1 | ["mgr"]
(3 行记录)
ORACLE
SQL> @cc pdb1
ALTER SESSION SET container = pdb1;
Session altered.
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS PDB1-anbob19c oel7db1 1 1 17152 19.0.0.0.0 20210922 2031 33 1938 0000000078881028 00000000794F7F48
SQL> create table anbob.emp(emp_id int,emp_name varchar2(100));
Table created.
SQL> insert into anbob.emp values (1,'a');
1 row created.
SQL> insert into anbob.emp values (2,'b');
1 row created.
SQL> insert into anbob.emp values (3,'c');
1 row created.
SQL> select json_object(*) from anbob.emp;
JSON_OBJECT(*)
--------------------------------
{"EMP_ID":1,"EMP_NAME":"a"}
{"EMP_ID":2,"EMP_NAME":"b"}
{"EMP_ID":3,"EMP_NAME":"c"}
SQL> alter table anbob.emp add sex char(1);
Table altered.
SQL> insert into anbob.emp values (3,'d',1);
1 row creted.
SQL> select * from anbob.emp;
EMP_ID EMP_NAME S
---------- -------------------- -
1 a
2 b
3 c
3 d 1
SQL> select json_object(*) j from anbob.emp;
J
----------------------------------------
{"EMP_ID":1,"EMP_NAME":"a","SEX":null}
{"EMP_ID":2,"EMP_NAME":"b","SEX":null}
{"EMP_ID":3,"EMP_NAME":"c","SEX":null}
{"EMP_ID":3,"EMP_NAME":"d","SEX":"1"}
SQL> select json_object(key 'eno' value emp_id, key 'ename' value emp_name) from anbob.emp;
JSON_OBJECT(KEY'ENO'VALUEEMP_ID,KEY'ENAME'VALUEEMP_NAME)
----------------------------------------------------
{"eno":1,"ename":"a"}
{"eno":2,"ename":"b"}
{"eno":3,"ename":"c"}
{"eno":3,"ename":"d"}
SQL> select json_array(emp_id,emp_name) from anbob.emp;
JSON_ARRAY(EMP_ID,EMP_NAME)
------------------------------------------
[1,"a"]
[2,"b"]
[3,"c"]
[3,"d"]
SQL> select json_arrayagg(emp_name) from anbob.emp;
JSON_ARRAYAGG(EMP_NAME)
-------------------------------------
["a","b","c","d"]
SQL> select json_arrayagg(json_object(key 'eno' value emp_id, key 'ename' value emp_name)) from anbob.emp;
JSON_ARRAYAGG(JSON_OBJECT(KEY'ENO'VALUEEMP_ID,KEY'ENAME'VALUEEMP_NAME))
---------------------------------------------------------------------------------------------
[{"eno":1,"ename":"a"},{"eno":2,"ename":"b"},{"eno":3,"ename":"c"},{"eno":3,"ename":"d"}]
SQL>
select json_object(
'eno' value emp_id,
'enames' value json_arrayagg(e.emp_name),
'cnt' value (select count(*) from anbob.emp e1 where e1.emp_id=e.emp_id)
)
from anbob.emp e
join anbob.dept d on e.emp_id=d.id
group by e.emp_id;
JSON_OBJECT('ENO'VALUEEMP_ID,'ENAMES'VALUEJSON_ARRAYAGG(E.EMP_NAME),'CNT'VALUE(SELECTCOUNT(*)FROMANBOB.EMPE1WHEREE1.EMP_ID=E.EMP_ID))
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"eno":1,"enames":["a"],"cnt":1}
{"eno":3,"enames":["c","d"],"cnt":2}