oracle json笔记

原创于 2025-04-08 11:59:23 发布 · 846 阅读

CC 4.0 BY-SA版权

文章标签：

有些数据是以json格式存储，常规的sql语句操作起来不太方便，别担心，数据库早就有对应的函数了。

适用于从json中提取单个值。

语法：

JSON_VALUE(expression, path RETURNING data_type DEFAULT default_value ON ERROR error_clause)

用.(点号)表示上下级关系。
属性的表示方法： $.一级属性.二级属性.三级属性…
数组的表示方法： $.columns[0].title

SELECT 
json_value(字段名, '$.user.name' RETURNING VARCHAR2) AS name
FROM 表名 t;

这个好像和pl/sql编程有关，直接写在语句里面好像不成立，有时间研究下。

适用于从json中提取对象或数组，如果是个值，反而会返回空。

语法：

JSON_QUERY(expression, path [ RETURNING data_type ] [ PRETTY ] [ WITH UNIQUE KEYS ] [ error_clause ])

示例：

SELECT json_query(字段名,'$.user') FROM 表名

将json数据展开为关系表形式，允许使用sql查询json的各个部分。

语法：

JSON_TABLE(expression, path
  COLUMNS (column_name column_type PATH 'json_path' [ DEFAULT default_expr ] [ error_clause ] ...)
)

SELECT jt.* FROM 表名, 
json_table(
字段名,'$.*'
columns(
	title VARCHAR2(100) PATH '$.*'
	
)
) jt

说实话，没太看懂怎么用，拿的数据也感觉不对。
对数据有什么要求呢? 是以数组的形式还是对象的形式呢? 有时间看下。TODO

正确示例：

 SELECT *
 FROM 表名
 WHERE JSON_EXISTS(字段名, '$.aaa');

注：json_exists()一般用于判断条件中，如果用在select的列中，会报错，[ORA-40458: 在谓词外部使用了 JSON_EXISTS]。

错误示例：

SELECT JSON_EXISTS('{"name": "John", "age": 30, "address": {"city": "New York", "zipcode": "10001"}}', '$.address') AS address_exists
FROM dual;

这条语句从网上找的，实测报错，所以实践出真知是很有道理的。