Oracle/PL/SQL奇技淫巧之Json转表

在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

路径格式：

1. 取所有元素：$[*]，表示取所有元素；
2. 取指定单个元素：如'$[0]'，表示取第一个元素；
3. 取指定多个元素：如$[0, 2, 4]，表示取第一、三、五个元素；
4. 取范围连续元素：如$[0 TO 2]，表示取第一到第三个元素；

如果不指定元素，如$[]，则会报错

例：存储过程中把接收到的Clob参数转成Json表

现在后台传递一个Json格式的数据给数据库：

[  
  {  
    "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类型来接收），并且把他转成一张表，然后遍历取得里面的数据：

	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;