Parsing JSON into Oracle type with nested table

PeterGubik
May 5, 2023
372 views
0 votes
2 Answers

Is there a way to make this Oracle query more optimal? Ideally without selecting from the same JSON_TABLE twice.

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;

Let’s consider that types are created as follows:

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  
);

Also let’s consider variable l_clob_response is JSON with following data:

{
    "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
}

My query works and returns an expected result, I just feel like it could be more optimal.

Answers

One variant is to extract the categories array in the outer query and then use that in the inner query. You would need to test each version and check which is more performant.

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

Oracle has default JSON to Object deserialization recursively based on the matched names. See 18.3 Using JSON_VALUE To Instantiate a User-Defined Object Type Instance section of JSON_VALUE function dosc for details.

In order to map your JSON data to your object type you have to reshape the JSON document with, for example, JSON_TRANSFORM function:

Remove all extra attributes that are not mapped to object attributes.
Rename all used attributes to match object attributes.
As long as boolean JSON datatype is mapped to varchar2 or clob, you need to either replace true/false with 1/0 (which I didn’t manage to work with set modification) or use varchar2 object attribute for this.

Combining all together, there’s a query:

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

Please signup or login to give your own answer.

Click here to cancel reply.