JSON 系列之1：将 JSON 数据存储在 Oracle 数据库中

🕗 发布于 2024-12-25 23:56 json oracle database

本文为Oracle数据库JSON学习系列的第一篇，讲述如何将JSON文档存储到数据库中，包括了版本为19c和23ai的情形。

19c中的JSON

先来看一下数据库版本为19c时的情形。

创建表colortab，其中color列的长度设为4000。若color的长度需要设为32767，则init.ora 参数 MAX_STRING_SIZE 必须设置为 EXTENDED。

DROP TABLE colortab PURGE;

CREATE TABLE colortab (
    id    NUMBER,
    color VARCHAR2(4000)
);

插入4条数据：

INSERT INTO colortab VALUES ( 1,
                              '
 {
  "color": "black",
  "rgb": [0,0,0],
  "hex": "#000000"
 }
' );

INSERT INTO colortab VALUES ( 2,
                              '
 {
  "color": "orange red",
  "rgb": [255,69,0],
  "hex": "#FF4500"
 }
' );

INSERT INTO colortab VALUES ( 3,
                              '
 {
  color: "gold",
  "rgb": [255,215,0],
  "hex": "#FFD700 "
 }
' );

INSERT INTO colortab VALUES ( 4,
                              'I am not valid JSON' );

COMMIT;

查看这些记录，会发现记录3的color字段并没有用双引号括起，于严格的JSON定义不符，但松散的JSON定义是允许的：
在这里插入图片描述
这可以通过如下来证明：

SQL> set echo on
SQL> SELECT id FROM colorTab WHERE color IS NOT JSON;

        ID
----------
         4

SQL> 
SQL> SELECT id FROM colorTab WHERE color IS NOT JSON STRICT;

        ID
----------
         3
         4

在Oracle 23ai JSON Developer’s Guide中，松散的语法称为Lax JSON Syntax，是默认的。严格的则称为Strict JSON Syntax。

插入一条新纪录，此记录符合Strict JSON Syntax，但具有重复的key：“color”。

INSERT INTO colortab VALUES ( 5,
                              '
 {
  "color": "black",
  "rgb": [0,0,0],
  "hex": "#000000",
  "color": "white"
 }
' );

COMMIT;

子句可以排除具有重复key的JSON。不过检查重复键是有代价的，所以一般是不做的：

SQL> SELECT id FROM colorTab WHERE color IS JSON STRICT;

        ID
----------
         1
         2
         5


SQL> SELECT id FROM colorTab WHERE color IS JSON STRICT WITH UNIQUE KEYS;

        ID
----------
         1
         2

如果只想让列存合法的JSON，在19c版本可以通过IS JSON约束。

TRUNCATE TABLE colorTab;

ALTER TABLE colorTab ADD CONSTRAINT ensure_json CHECK (color IS JSON);

此时，插入记录4时报错：

错误报告 -
ORA-02290: 违反检查约束条件 (SSB.ENSURE_JSON)

https://docs.oracle.com/error-help/db/ora-02290/

如果约束是CHECK (color IS JSON STRICT)，则插入记录4时报错同上。

JSON的信息可以从字典视图中查看：

col table_name for a10
col column_name for a16
SELECT * FROM USER_JSON_COLUMNS WHERE table_name = 'COLORTAB';

TABLE_NAME OBJEC COLUMN_NAME      FORMAT    DATA_TYPE    
---------- ----- ---------------- --------- -------------
COLORTAB   TABLE COLOR            TEXT      VARCHAR2

最后再说一点，上例中的JSON是用VARCHAR2来存的，此外还可以用CLOB和BLOB。通常会建议BLOB，因为BLOB占用空间更小，从而引发的I/O更少。

Internally, CLOB encodes characters as UCS2 (similar to UTF16) which means every character takes up two bytes. BLOB does not perform such re-encoding but instead stores the Unicode (UTF8) bytes unmodified - thus requiring half the storage size for ASCII characters, and half the IO to load it.

23ai中的JSON

23ai支持原生JSON，因此表的定义变为：

drop table colortab purge;
CREATE TABLE colortab (
    id    NUMBER,
    color JSON
);

JSON 数据类型的实例使用 OSON 格式存储。OSON 是 Oracle 针对 Oracle 数据库服务器和 Oracle 数据库客户端中的查询和更新而优化的二进制 JSON 格式。

根据Oracle Database JSON Capabilities Specification，单个JSON实例的存储限制为32MB。

此时插入之前的5条数据。

插入记录4时，报错如下：

错误报告 -
ORA-40441: JSON 语法错误
JZN-00078: Invalid JSON keyword 'I' (line 1, position 1)

https://docs.oracle.com/error-help/db/ora-40441/

More Details :
https://docs.oracle.com/error-help/db/ora-40441/
https://docs.oracle.com/error-help/db/jzn-00078/

插入记录5时，报错如下：

错误报告 -
SQL 错误: ORA-40473: JSON 对象中存在重复的键名 'color'
JZN-00007: Object member key 'color' is not unique

https://docs.oracle.com/error-help/db/ora-40473/40473. 00000 -  "duplicate key names '%s' in JSON object"
*Cause:    The provided JavaScript Object Notation (JSON) data had duplicate
           key names in one object.
*Action:   Provide JSON data with unique key names in each JSON object.

More Details :
https://docs.oracle.com/error-help/db/ora-40473/
https://docs.oracle.com/error-help/db/jzn-00007/

这说明23ai JSON默认语法是Lax JSON Syntax，并且不允许重复键。文档也是这么说的：

JSON 标准建议 JSON 对象不要有重复的字段名称。Oracle 数据库通过引发错误来强制 JSON 类型数据遵循此要求。

查看字典视图，数据类型为JSON，存储格式为OSON：

SQL> col table_name for a10
SQL> col column_name for a16
SQL> SELECT * FROM USER_JSON_COLUMNS WHERE table_name = 'COLORTAB';

TABLE_NAME OBJEC COLUMN_NAME      FORMAT    DATA_TYPE    
---------- ----- ---------------- --------- -------------
COLORTAB   TABLE COLOR            OSON      JSON

Oracle称所有非OSON存储的JSON为文本JSON（Textual JSON）。

JSON数据类型无法指定Strict JSON Syntax，按照文档5.3 Specifying Strict or Lax JSON Syntax 的说法：

Oracle 数据库的默认 JSON 语法是宽松的。严格或宽松语法仅对 SQL/JSON 条件 is json 和 is not json 有意义。所有其他 SQL/JSON 函数和条件都使用宽松语法来解释输入，并在返回输出时使用严格语法。

如果您需要确保特定文本 JSON 数据具有严格正确的语法，请先使用 is json 或 is not json 进行检查。

参考

原文地址：https://blog.csdn.net/stevensxiao/article/details/144509657

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：Spring基础分析13-Spring Security框架
下一篇：Oracle中间件 SOA之 OSB 12C服务器环境搭建

RAGFlow 基于深度文档理解构建的开源 RAG引擎 vm.max_map_count配置
flyfish
阅读更多2024-12-26
STM32HAL库中RTC闹钟设置时分秒，年月日
在STM32的HAL库中，RTC（实时时钟）模块提供了多种功能来管理时间和日期，包括设置闹钟。对于RTC闹钟功能，确实主要集中在时、分、秒的配置上，但年、月、日也可以通过RTC日期寄存器进行设置，并且
阅读更多2024-12-26
初始 ShellJS：一个 Node.js 命令行工具集合
Node.js丰富的生态能赋予我们更强的能力，对于前端工程师来说，使用 Node.js 来编写复杂的 npm script 具有明显的 2 个优势：首先，编写简单的工具脚本对前端工程师来说额外的学习成
阅读更多2024-12-26
基于单片机的非接触式红外测温系统
采用51单片机作为主控系统；通过MLX90614红外测温模块，精准测量温度；通过红外传感器反应是否有人体靠近，靠近后开始测温；通过TTS语音模块播报温度；通过按键可以调节温度阀值通过LCD1602显示
阅读更多2024-12-26
音视频入门知识（四）：封装篇
H264封装成mp4、flv等格式，那为什么需要封装？h264也能播放，但是按照帧率进行播放，可能不准。
阅读更多2024-12-26
MR-GDINO: Efficient Open-World Continual Object Detection
摘要开放世界（OW）识别与检测模型展现出了强大的零样本和少样本适应能力，这启发了人们将其作为初始化方法应用于持续学习方法中以提高性能。尽管在已见类别上取得了令人鼓舞的结果，但由于灾难性遗忘，这些模型在
阅读更多2024-12-26
DS查找—二叉树平衡因子
二叉树用数组存储，将二叉树的结点数据依次自上而下,自左至右存储到数组中，一般二叉树与完全二叉树对比，比完全二叉树缺少的结点在数组中用0来表示。对每组测试数据，按后序遍历的顺序输出树中结点的平衡因子（测
阅读更多2024-12-26
iDP3复现代码模型训练全流程（二）——train_policy.py
在 train_policy.sh 接收命令行参数后，通过此脚本加载配置文件、初始化工作空间并运行训练流程
阅读更多2024-12-26
Pytorch分布式训练
pytorch分布式并行训练
阅读更多2024-12-26
C语言介绍
介绍c语言
阅读更多2024-12-26

JSON 系列之1：将 JSON 数据存储在 Oracle 数据库中

19c中的JSON

23ai中的JSON

参考

相关文章