使用嵌套表将JSON解析为Oracle类型

wrrgggsh 于 2023-05-06 发布在 Oracle

关注(0)|答案(2)|浏览(153)

是否有方法使此Oracle查询更优化？理想情况下，无需从同一JSON_TABLE中选择两次。

SELECT to_dncl_verification(status_code   => t.status,
                            d_valid_from  => to_date(t.d_valid_from, 'yyyy-mm-dd'),
                            d_valid_to    => to_date(t.d_valid_to, 'yyyy-mm-dd'),
                            category_list => CAST(
                                                  MULTISET (SELECT tc.category, CASE WHEN tc.allowed = 'true' THEN 1 ELSE 0 END
                                                              FROM JSON_TABLE(l_clob_response, '$'
                                                                   COLUMNS NESTED PATH '$.categories[*]'
                                                                     COLUMNS (category VARCHAR2(255) PATH '$.category',
                                                                              allowed  VARCHAR2(255) PATH '$.allowed')
                                                                   ) tc 
                                                            ) AS tc_dncl_category
                                                     )
                               )
      INTO y_verification_result
      FROM JSON_TABLE(l_clob_response, '$'
                      COLUMNS status       VARCHAR2(255) PATH '$.status',
                              d_valid_from VARCHAR2(255) PATH '$.dateValidFrom',
                              d_valid_to   VARCHAR2(255) PATH '$.dateValidTo'
                      ) t;

让我们考虑一下类型的创建方式：

create or replace type to_dncl_category is object (
  category_code   varchar2(20),
  is_allowed      number
);

create or replace type tc_dncl_category is table of to_dncl_category;

create or replace type to_dncl_verification is object (
  status_code          varchar2(20),
  d_valid_from         date,
  d_valid_to           date,
  category_list        tc_dncl_category  
);

另外，让我们考虑变量l_clob_response是具有以下数据的JSON：

{
    "id": "123",
    "status": "PARTIALLY_BLOCKED",
    "dateOfCheck": "2023-01-01",
    "dateValidFrom": "2023-05-15",
    "categories": [
        {
            "id": "123",
            "category": "category ABC",
            "allowed": true,
            "dateCreated": "2023-05-05T10:47:19.745Z",
            "recordVersion": 0
        },
        {
            "id": "123",
            "category": "category DEF",
            "allowed": false,
            "dateCreated": "2023-05-05T10:47:19.745Z",
            "recordVersion": 0
        },
        {
            "id": "123",
            "category": "category GHI",
            "allowed": true,
            "dateCreated": "2023-05-05T10:47:19.745Z",
            "recordVersion": 0
        }
    ],
    "dateValidTo": "2023-05-30",
    "recordVersion": 0
}

我的查询工作并返回预期的结果，我只是觉得它可以更优化。

oracle

来源：https://stackoverflow.com/questions/76182307/parsing-json-into-oracle-type-with-nested-table

2条答案

按热度按时间

1sbrub3j1#

Oracle具有基于匹配名称递归的默认JSON到对象反序列化。有关详细信息，请参见JSON_VALUE函数dosc的18.3使用JSON_VALUE示例化用户定义的对象类型示例部分。
为了将JSON数据Map到对象类型，您必须使用JSON_TRANSFORM函数重塑JSON文档：

移除未Map到对象属性的所有额外属性。
重命名所有使用的属性以匹配对象属性。
只要boolean JSON数据类型Map到varchar2或clob，您就需要将true/false替换为1/0（我无法使用set修改）或为此使用varchar2对象属性。

将所有这些组合在一起，有一个查询：

select
 json_value(
  json_transform(
    val,

    keep '$.status', '$.dateValidFrom','$.dateValidTo',
      '$.categories[*].category', '$.categories[*].allowed',

    -- set '$.categories[*].allowed?(@ == true)' = 1 ignore on missing,
    -- set '$.categories[*].allowed?(@ == false)' = 0 ignore on missing,

    
    rename '$.status' = 'status_code' error on missing,
    rename '$.dateValidFrom' = 'd_valid_from' error on missing,
    rename '$.dateValidTo' = 'd_valid_to' error on missing,
    rename '$.categories[*].category' = 'category_code' error on missing,
    rename '$.categories[*].allowed' = 'is_allowed' ignore on missing,
    rename '$.categories' = 'category_list' error on missing
  )
  , '$' returning to_dncl_verification) as res
from sample

RES(STATUS_CODE, D_VALID_FROM, D_VALID_TO, CATEGORY_LIST(CATEGORY_CODE, IS_ALLOWED))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
-----------------------------
TO_DNCL_VERIFICATION('PARTIALLY_BLOCKED', '15.05.23', '30.05.23', TC_DNCL_CATEGORY(TO_DNCL_CATEGORY('category ABC', 'true'), TO_DNCL_CATEGORY('category DEF', 'false'), TO_DNCL_CATEGORY('category GHI', 'true')))

db<>fiddle

赞(0）回复(0）举报 2023-05-06

njthzxwz2#

一种变体是在外部查询中提取categories数组，然后在内部查询中使用它。您需要测试每个版本，并检查哪个版本的性能更好。

DECLARE
  l_clob_response CLOB := '{
    "id": "123",
    "status": "PARTIALLY_BLOCKED",
    "dateOfCheck": "2023-01-01",
    "dateValidFrom": "2023-05-15",
    "categories": [
        {
            "id": "123",
            "category": "category ABC",
            "allowed": true,
            "dateCreated": "2023-05-05T10:47:19.745Z",
            "recordVersion": 0
        },
        {
            "id": "123",
            "category": "category DEF",
            "allowed": false,
            "dateCreated": "2023-05-05T10:47:19.745Z",
            "recordVersion": 0
        },
        {
            "id": "123",
            "category": "category GHI",
            "allowed": true,
            "dateCreated": "2023-05-05T10:47:19.745Z",
            "recordVersion": 0
        }
    ],
    "dateValidTo": "2023-05-30",
    "recordVersion": 0
}';
   y_verification_result to_dncl_verification;
BEGIN
  SELECT to_dncl_verification(
           status_code   => t.status,
           d_valid_from  => to_date(t.d_valid_from, 'yyyy-mm-dd'),
           d_valid_to    => to_date(t.d_valid_to, 'yyyy-mm-dd'),
           category_list => CAST(
                              MULTISET (
                                SELECT tc.category,
                                       CASE WHEN tc.allowed = 'true'
                                       THEN 1
                                       ELSE 0
                                       END
                                FROM   JSON_TABLE(
                                         t.categories,
                                         '$[*]'
                                         COLUMNS 
                                           category VARCHAR2(255) PATH '$.category',
                                           allowed  VARCHAR2(255) PATH '$.allowed'
                                         ) tc 
                              ) AS tc_dncl_category
                            )
         )
  INTO   y_verification_result
  FROM   JSON_TABLE(
           l_clob_response,
           '$'
           COLUMNS
             status       VARCHAR2(255) PATH '$.status',
             d_valid_from VARCHAR2(255) PATH '$.dateValidFrom',
             d_valid_to   VARCHAR2(255) PATH '$.dateValidTo',
             categories   CLOB FORMAT JSON PATH '$.categories'
           ) t;
  DBMS_OUTPUT.PUT_LINE(
    y_verification_result.category_list.COUNT
  );
END;
/

fiddle

赞(0）回复(0）举报 2023-05-06

我来回答

使用嵌套表将JSON解析为Oracle类型

2条答案

相关问题

热门标签

最新问答