使用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屋！