在Oracle中,有些时候我们需要在一个json文档中查数据
这个时候我们可以通过JSON_TABLE
函数来把 json文档 提取成一张可以执行正常查询操作的表
先看JSON_TABLE
函数的基础用法:
JSON_TABLE(json_data, '$.json_path' COLUMNS (column_definitions))
其中:json_data
:要从中提取数据的 JSON文档 或 JSON列$.json_path
:JSON路径表达式,该表达式指定要提取的数据的位置COLUMNS子句
:定义要从JSON数据中提取的列,每个列定义都应该包括列名、数据类型和JSON路径表达式,以指定数据在JSON文档中的位置。
例:
SELECT *
FROM JSON_TABLE('{"name": "John", "age": 30, "city": "New York"}',
'$' COLUMNS (name VARCHAR2(50) PATH '$.name_',
age NUMBER PATH '$.age_',
city VARCHAR2(50) PATH '$.city_'));
这里的json_data
='{"name": "John", "age": 30, "city": "New York"}'
$
的含义是从JSON文档的根路径提取数据COLUMNS子句
表示将从JSON文档中提取name_
、age_
、city_
这三列数据分别放入name
、age
、city
列中
注意()
中的$
表示的路径是基于COLUMNS
前面指定的那个路径,这里是$.$
函数的结果将是一个包含三列的表:name
、age
、city
,其中包含从JSON文档中提取的相应值。
如果JSON文档是一个JSON数组呢?
例如有这样一个json_list文档:
[
{
"count": "2",
"items": [
{
"key": "keyOne",
"value": "valueOne"
},
{
"key": "keyTwo",
"value": "valueTwo"
}
]
}
]
虽然里面只有一个JSON对象,但是这个JSON文档是一个JSON数组,用[]
包起来的
看看怎么解析它:
json_table(json_list,
'$[*]' columns (colum_1 VARCHAR2(20) PATH '$.count',
colum_2 VARCHAR2(1000) FORMAT JSON PATH '$.count'));
$[*]
的含义是从JSON数组[]
里面提取数据,*
表示所有元素COLUMNS子句
表示将从JSON文档中提取count
、count
这两列数据分别放入colum_1
、colum_2
列中
注意columns ()
中的$
表示的路径是基于COLUMNS
前面指定的那个路径,在这里就是$[*].$
函数的结果将是一个包含两列的表:colum_1
、colum_2
路径格式:
- 取所有元素:
$[*]
,表示取所有元素;- 取指定单个元素:如
'$[0]'
,表示取第一个元素;- 取指定多个元素:如
$[0, 2, 4]
,表示取第一、三、五个元素;- 取范围连续元素:如
$[0 TO 2]
,表示取第一到第三个元素;如果不指定元素,如
$[]
,则会报错
例:存储过程中把接收到的Clob参数转成Json表
现在后台传递一个Json格式的数据给数据库:文章来源:https://www.toymoban.com/news/detail-654426.html
[
{
"fulfillmentId": "12345",
"itemCode": "ABC123",
"dataId": "data123",
"itemQty": 5,
"deliveryId": "deliveryXYZ",
"itemid": "item789"
},
{
"fulfillmentId": "54321",
"itemCode": "XYZ789",
"dataId": "data456",
"itemQty": 3,
"deliveryId": "deliveryQRS",
"itemid": "item098"
},
// 更多对象...
]
存储过程要接收这个数据(这里用了Clob类型来接收),并且把他转成一张表,然后遍历取得里面的数据:文章来源地址https://www.toymoban.com/news/detail-654426.html
PROCEDURE pro_save_data(v_data_list IN CLOB, v_status OUT NUMBER)
IS
v_delivery_id VARCHAR2(20);
# 定义一个游标,接收一个CLOB类型的参数,参数名随意,这里是v_list
CURSOR v_cur(v_list IN CLOB) IS
SELECT t.*
FROM JSON_TABLE(v_data_list, '$[*]' COLUMNS(fulfillment_id VARCHAR2(20) PATH '$.fulfillmentId',
item_code VARCHAR2(20) PATH '$.itemCode',
data_id VARCHAR2(20) PATH '$.dataId',
item_qty NUMBER PATH '$.itemQty',
delivery_id VARCHAR2(20) PATH '$.deliveryId',
item_id VARCHAR2(255) PATH '$.itemid'
)
) t;
BEGIN
# 用For循环遍历游标,把v_data_list传给游标
FOR v_rec IN v_cur(v_data_list) LOOP
INSERT INTO table_one
VALUES( v_rec.data_id ,
v_rec.fulfillmentId,
v_rec.item_code,
v_rec.item_qty,
v_rec.delivery_id ,
v_rec.item_id,
NULL);
END LOOP;
SELECT json_value(v_data_list, '$[0].deliveryId')
INTO v_delivery_id
FROM dual;
COMMIT;
v_status := 0;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_status := 1; -- Fail to save
END pro_save_data;
到了这里,关于Oracle/PL/SQL奇技淫巧之Json转表的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!