使用PL/SQL从CLOB字段解析JSON数据 [英] Parsing JSON data from CLOB field using PL/SQL
问题描述
我将以下格式的数据存储在表的CLOB字段中-
I have data in the following format stored in a CLOB field of a table-
{
"key" : "PRODUCT_NAME",
"value" : "Myproduct"
}, {
"key" : "PRODUCT_TYPE",
"value" : "Electronics"
}, {
"key" : "PRODUCT_PRICE",
"value" : "123456789.1"
}
我想将它们存储在将包含列PRODUCT_NAME
,PRODUCT_TYPE
,PRODUCT_PRICE
的表中,并将它们存储为
I want to store them in a table which will be having columns PRODUCT_NAME
,PRODUCT_TYPE
,PRODUCT_PRICE
and they will be stored as
PRODUCT_NAME PRODUCT_TYPE PRODUCT_PRICE
MyProduct Electronics 123456789.1
我想使用Pl/SQL执行这些操作.这里有指针吗?
I want to perform these using Pl/SQL. Any pointers here?
推荐答案
您使用的CLOB
字段不是有效的JSON,因此您不能直接使用JSON函数.您需要阅读 JSON文档,并了解如何json应该存储在数据库表中,以使其更易于使用.一个适合您的解决方案是先解析各个json,然后在每个json上应用JSON_OBJECT
.此外,您需要PIVOT
或MAX(CASE)
块将行转换为列.
该查询可在Oracle 12c及更高版本中使用.
The CLOB
field you are using is not a valid JSON, so you can't use the JSON functions directly. You need to go through the JSON documentation and understand how a json should be stored in a database table in order to make it easier to use them. One solution for you would be to parse the individual jsons first and then apply JSON_OBJECT
on each json. Furthermore, you would need a PIVOT
or a MAX(CASE)
block to convert the rows into columns.
This query works in Oracle 12c and above.
样本数据
CREATE TABLE t AS
SELECT 1 AS id,
To_clob('{ "key" : "PRODUCT_NAME", "value" : "Myproduct" }, { "key" : "PRODUCT_TYPE", "value" : "Electronics" }, { "key" : "PRODUCT_PRICE", "value" : "123456789.1" }') AS j
FROM dual
UNION ALL
SELECT 2,
To_clob('{ "key" : "PRODUCT_NAME", "value" : "Myproduct2" }, { "key" : "PRODUCT_TYPE", "value" : "Chemical" }, { "key" : "PRODUCT_PRICE", "value" : "25637.1" }')
FROM dual;
查询
WITH jdt AS
(
SELECT id,
JSON_VALUE(jsons,'$.key') AS k, -- gets the "key"
JSON_VALUE(jsons,'$.value') AS v -- gets the "value"
FROM (
SELECT id,
REGEXP_SUBSTR(j,'(.*?)\}(,|$)',1,LEVEL,'n',1)
|| '}' AS jsons --split the clob field into individual jsons
FROM t
CONNECT BY PRIOR id = id
AND PRIOR SYS_GUID() IS NOT NULL
AND LEVEL <= REGEXP_COUNT(j,'\}(,|$)') ) )
SELECT *
FROM jdt pivot ( max ( v ) FOR k IN ( 'PRODUCT_NAME',
'PRODUCT_TYPE',
'PRODUCT_PRICE' ) );
ID PRODUCT_NAME PRODUCT_TYPE PRODUCT_PRICE
1 Myproduct Electronics 123456789.1
2 Myproduct2 Chemical 25637.1
这篇关于使用PL/SQL从CLOB字段解析JSON数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!