Oracle 21c对JSON支持功能增强

在本文中《Oracle和JSON介绍了结合Oracle对JSON有朋友提出了两个问题，

问题1，Oracle是否可以直接存储字段JSON串并支持检索？

问题2，试过几次，读和约束都可以，但是存和修改还是不容易。DB2之前的XmlQuery语法，门槛太高，意外出错

(1) 针对问题1

时代在进步，技术也在发展，Oracle 21c(准确地说，有些功能是20c已经引入，因为20c只是预览版，主要是21c以引入为准JSON数据类型的目的是改进检索和DML操作对JSON数据效率，

https://docs.oracle.com/en/database/oracle/oracle-database/21/nfcon/application-development.html#GUID-47BBA332-62C9-41AD-A1D2-51EE02379171

如下所示，

SQL>createtabletjson(idnumber,json_datajson); Table created.

可按常规字符串类型插入，

SQL>insertintotjsonvalues(1,'{"name":"CristianoRonaldo","goal":30}'); 1rowcreated.

还可以使用JSON插入构造函数，

SQL>insertintotjsonvalues(2,json('{"name":"LeoMessi","goal":25}')); 1rowcreated.

可直接检索，

SQL>select*fromtjson;         ID JSON_DATA ---------- -------------------------------------------------------          1 {"name":"Cristiano Ronaldo","goal":30} 2{"name":"LeoMessi","goal":25}

也可以使用常用的写程序"."检索操作符，

SQL>selectj.json_data.namefromtjsonj; NAME -------------------------------------------------------------------------------- "Cristiano Ronaldo" "LeoMessi"

json_value具体类型的函数可以返回节点数据，

SQL> select j.id, json_value(j.json_data, '$.name') as name,   2  json_value(j.json_data, '$.goal' returning number) as goal 3fromtjsonj;         ID NAME                    GOAL ---------- ---------------------- ----------          1 Cristiano Ronaldo       30 2LeoMessi  25

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_VALUE.html#GUID-C7F19D36-1E75-4CB2-AE67-ADFBAD23CBC2

json_query函数可以完成相同的操作，

SQL> select j.id, json_query(j.json_data, '$.name' returning varchar2) as name,   2  json_query(j.json_data, '$.goal' returning varchar2) as goal 3fromtjsonj;         ID NAME                        GOAL ---------- -------------------------- ---------------          1 "Cristiano Ronaldo"         30 2"LeoMessi"25

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_QUERY.html#GUID-6D396EC4-D2AA-43D2-8F5D-08D646A4A2D9

还可以使用json_table函数，

SQL> select j.id, jt.name, jt.goal   2  from tjson j,   3  json_table(j.json_data, '$' columns(name varchar2(50 char) path '$.name', 4goalnumberpath'$.goal'))jt;         ID NAME                                                     GOAL ---------- -------------------------------------------------- ----------          1 Cristiano Ronaldo                                          30 2LeoMessi25

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_TABLE.html#GUID-3C8E63B5-0B94-4E86-A2D3-3D4831B67C62

(2) 针对问题2

21c引入了json_transform函数，他的作用就是可以通过指定一个或多个操作符改JSON数据或片段，

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_TRANSFORM.html#GUID-DD2A821B-C688-4310-81B5-5F45090B9366

如下所示，修改id=1的记录中goal这个节点的值，

SQL> update tjson set json_data = json_transform(json_data, set '$.goal' = 35) where id = 1;
1 row updated.

检索进行验证，

SQL> select j.id, json_value(j.json_data, '$.name') as name,
  2  json_value(j.json_data, '$.goal' returning number) as goal
  3  from tjson j;
        ID NAME                                                     GOAL
---------- -------------------------------------------------- ----------
         1 Cristiano Ronaldo                                          35
         2 Leo Messi                                                  25

还支持同时进行多种变更操作，

SQL> update tjson set json_data = json_transform(json_data, set '$.name' = 'L.Messi', replace '$.goal' = 30 returning json) where id = 2;
1 row updated.

支持这些操作，

removeOp | insertOp | replaceOp | appendOp | setOp | renameOp | keepOp

可以看到更新，

SQL> select j.id, json_value(j.json_data, '$.name') as name,
  2  json_value(j.json_data, '$.goal' returning number) as goal
  3  from tjson j;
        ID NAME                        GOAL
---------- -------------------------- ----------
         1 Cristiano Ronaldo           35
         2 L.Messi                     30

至少从肉眼观测效率上和普通数据类型的增删改相差无几，如果比较关注，可以进行一些实际的压测。虽然语法略复杂，但至少从操作层面上，还是比较简洁的，对于JSON数据的处理，不再只能通过程序来做了，数据库提供了另外一种方案，尤其是使用存储过程，PL/SQL中同样能用到这些函数和数据类型，会更加便捷。

参考资料，

https://www.modb.pro/db/110452

https://blog.csdn.net/horses/article/details/120369571

https://docs.oracle.com/en/database/oracle/oracle-database/21/nfcon/application-development.html#GUID-47BBA332-62C9-41AD-A1D2-51EE02379171

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_VALUE.html#GUID-C7F19D36-1E75-4CB2-AE67-ADFBAD23CBC2

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_QUERY.html#GUID-6D396EC4-D2AA-43D2-8F5D-08D646A4A2D9

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_TABLE.html#GUID-3C8E63B5-0B94-4E86-A2D3-3D4831B67C62

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_TRANSFORM.html#GUID-DD2A821B-C688-4310-81B5-5F45090B9366

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，

近期更新的文章：

《翻译专业资格(水平)考试介绍》

《MySQL忘了账号密码，除了跑路，还能补救么？》

《非标准数据块的表空间使用》

《数据库安全的重要性》

《CentOS 7.9安装Oracle 21c历险记》

近期的热文：

《"红警"游戏开源代码带给我们的震撼》

文章分类和索引：

《公众号1000篇文章分类和索引》