Oracle JSON_EXTRACT函數詳解

一、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函數功能強大，但是也有局限性，無法提取多個值或數組，對數據源有一定限制。