更新时间:2022-01-12 23:10:09
您使用的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