Oracle JSON_EXTRACT函数详解

小蓝 • 2025-01-05 13:23 • 编程

一、JSON_EXTRACT函数简介

JSON_EXTRACT函数是Oracle 12c版本引入的JSON函数之一，用于从JSON文本中提取一个或多个标量或对象值。它的语法如下：

    
        JSON_EXTRACT(json_string, json_path)

其中，json_string为json文本内容，json_path为json路径表达式，表示需要提取的值在json文本中的位置。

二、JSON路径表达式详解

JSON_EXTRACT函数通过JSON路径表达式来指定json文本中待提取的值。JSON路径表达式的语法与XPath类似，路径表达式由若干个路径段构成，每个路径段以”.”或”[]”分隔：

1、’.’ 表示选取当前节点；

2、'[]’ 表示按照数组下标来选取节点，下标从0开始；

3、’*’ 表示选取所有子节点；

4、’..’ 表示选取当前节点的父节点；

5、’@’ 表示选取属性节点。

以下是一些JSON路径表达式的例子：

    
        $.name                      //选取根节点的name属性
        $.friends[0].age            //选取第一个朋友的age属性
        $.friends[*].name           //选取所有朋友的name属性
        $.friends[?(@.age>25)].name //选取年龄大于25岁的朋友的name属性

三、JSON_EXTRACT函数示例

以下是几个JSON_EXTRACT函数的使用示例：

1、提取标量值

    
        SELECT JSON_EXTRACT('{"name": "Tom", "age": 28}', '$.name') FROM DUAL;
        --结果为Tom

2、提取对象值

    
        SELECT JSON_EXTRACT('{"name": "Tom", "friends": [{"name": "Jack", "age": 27}, {"name": "Lucy", "age": 22}]}', '$.friends[0]') FROM DUAL;
        --结果为{"name": "Jack", "age": 27}

3、提取数组值

    
        SELECT JSON_EXTRACT('{"name": "Tom", "friends": [{"name": "Jack", "age": 27}, {"name": "Lucy", "age": 22}]}', '$.friends[*].name') FROM DUAL;
        --结果为["Jack", "Lucy"]

4、使用通配符

    
        SELECT JSON_EXTRACT('{"name": "Tom", "friends": [{"name": "Jack", "age": 27}, {"name": "Lucy", "age": 22}]}', '$..name') FROM DUAL;
        --结果为["Tom", "Jack", "Lucy"]

四、JSON_EXTRACT函数的返回类型

JSON_EXTRACT函数返回的类型取决于待提取的值的类型。如果待提取的值是标量值，则返回VARCAHR2类型；如果待提取的值是对象或数组，则返回CLOB类型。以下是一些示例：

1、标量值

    
        SELECT JSON_EXTRACT('{"name": "Tom", "age": 28}', '$.name') FROM DUAL;
        --结果为Tom，类型为VARCHAR2

2、对象值

    
        SELECT JSON_EXTRACT('{"name": "Tom", "friends": [{"name": "Jack", "age": 27}, {"name": "Lucy", "age": 22}]}', '$.friends[0]') FROM DUAL;
        --结果为{"name": "Jack", "age": 27}，类型为CLOB

3、数组值

    
        SELECT JSON_EXTRACT('{"name": "Tom", "friends": [{"name": "Jack", "age": 27}, {"name": "Lucy", "age": 22}]}', '$.friends') FROM DUAL;
        --结果为[{"name": "Jack", "age": 27}, {"name": "Lucy", "age": 22}]，类型为CLOB

五、JSON_EXTRACT函数的局限性

JSON_EXTRACT函数虽然功能强大，但是在实际使用中也有一些局限性：

1、只能提取单值

JSON_EXTRACT函数只能提取单个标量或对象值，无法提取多个值，也无法返回数组。如果需要提取多个值或数组，可以通过多次调用JSON_EXTRACT函数来实现。

2、对数据源限制较大

JSON_EXTRACT函数只能操作JSON格式的数据源，对于其他格式的数据源则无法使用。如果需要操作非JSON格式的数据源，则需要对数据做格式转换，或者使用其他函数来实现。

六、结论

JSON_EXTRACT函数是Oracle 12c版本中新增的一项JSON函数，用于从JSON文本中提取标量或对象值。它使用XPath风格的JSON路径表达式来定位待提取的值，返回类型取决于待提取的值的类型。虽然JSON_EXTRACT函数功能强大，但是也有局限性，无法提取多个值或数组，对数据源有一定限制。

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/311383.html