首页 » Cloud, ORACLE 9i-23ai, 开发语言 » Oracle & PostGreSQL 生成 JSON 数据

Oracle & PostGreSQL 生成 JSON 数据

在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}
打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：