Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sanjay.

Asked: June 12, 2023 - 11:58 am UTC

Last updated: June 13, 2023 - 9:13 am UTC

Version: 19c

Viewed 1000+ times

You Asked

It seems that the json_query from the input are always accepting varchar2 with 4000 character limitation, As soon as the json length is 4000 it shows null from the query. I tried casting as per documentation to clob but it still have same issue. As we add only one record in json it will spit out null record.

li_json is
{"Cust":
[
{"loan_num":"TEST-6498","document_id":"FOR","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6460","document_id":"LNAME","comment_descr":"Test Query","loan_status_descr":"Funded","document_descr":"Signature wrong","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7909","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7946","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7961","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7952","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7948","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7965","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7926","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7913","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-8097","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name 
Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6511","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6530","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6559","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6520","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6557","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6584","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6527","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6587","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6690","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6690","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"}]}


Following is the query
  select * from  JSON_TABLE ( JSON_QUERY(:li_json, '$.Cust'), '$'
                        COLUMNS (
                            NESTED PATH '$[*]'
                                COLUMNS (
                                    cyberridge_loan_number VARCHAR2 PATH '$.loan_num',
                                    custodian_document VARCHAR2 PATH '$.document_id',
                                    comment_description VARCHAR2 PATH '$.comment_descr',
                                    status_description VARCHAR2 PATH '$.status_descr',
                                    loan_status_description VARCHAR2 PATH '$.loan_status_descr',
                                    document_description VARCHAR2 PATH '$.document_descr',
                                    document_status VARCHAR2 PATH '$.status_descr'
                                )
                        )
                    );


Whats the workaround for this, anyone have any solution?

and Chris said...

I'm unclear exactly where the issue is here. When I assign the JSON document to a CLOB variable the query processes it as expected:

var li_json clob;

begin
  :li_json := '{
  "Cust": [
    {
      "loan_num": "TEST-6498",
      "document_id": "FOR",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-6460",
      "document_id": "LNAME",
      "comment_descr": "Test Query",
      "loan_status_descr": "Funded",
      "document_descr": "Signature wrong",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-7909",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-7946",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-7961",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-7952",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-7948",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-7965",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-7926",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-7913",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-8097",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-6511",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-6530",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-6559",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-6520",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-6557",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-6584",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-6527",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-6587",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-6690",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    },
    {
      "loan_num": "TEST-6690",
      "document_id": "FORM",
      "comment_descr": "Test Query",
      "loan_status_descr": "Reviewed",
      "document_descr": "Borrower Name Incorrect",
      "status_descr": "Incomplete/Incorrect"
    }
  ]
}';
end;
/

select * from  JSON_TABLE ( JSON_QUERY(:li_json, '$.Cust'), '$'
    COLUMNS (
        NESTED PATH '$[*]'
            COLUMNS (
                cyberridge_loan_number VARCHAR2 PATH '$.loan_num',
                custodian_document VARCHAR2 PATH '$.document_id',
                comment_description VARCHAR2 PATH '$.comment_descr',
                status_description VARCHAR2 PATH '$.status_descr',
                loan_status_description VARCHAR2 PATH '$.loan_status_descr',
                document_description VARCHAR2 PATH '$.document_descr',
                document_status VARCHAR2 PATH '$.status_descr'
            )
    )
);

CYBERRIDGE_LOAN_NUMBER    CUSTODIAN_DOCUMENT    COMMENT_DESCRIPTION    STATUS_DESCRIPTION      LOAN_STATUS_DESCRIPTION    DOCUMENT_DESCRIPTION       DOCUMENT_STATUS         
TEST-6498                 FOR                   Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-6460                 LNAME                 Test Query             Incomplete/Incorrect    Funded                     Signature wrong            Incomplete/Incorrect    
TEST-7909                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-7946                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-7961                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-7952                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-7948                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-7965                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-7926                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-7913                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-8097                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-6511                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-6530                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-6559                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-6520                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-6557                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-6584                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-6527                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-6587                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-6690                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect    
TEST-6690                 FORM                  Test Query             Incomplete/Incorrect    Reviewed                   Borrower Name Incorrect    Incomplete/Incorrect


Please provide a complete example showing where the problem occurs.

Side note: there's no need to JSON_query to preprocess the data. You can pass the document to JSON_table itself and provide the path you want to search:

SELECT ... FROM JSON_TABLE ( :li_json, '$.Cust' COLUMNS (...

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you for your response

Sanjay, June 13, 2023 - 2:38 am UTC

What I meant was if we add 1 record in the JSON it will max out the varchar2 thus returning 0 rows value. Try adding one data inside the JSON Payload and execute again or try below payload

{"Custodian":
[
{"loan_num":"TEST-6498","document_id":"FOR","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6460","document_id":"LNAME","comment_descr":"Test Query","loan_status_descr":"Funded","document_descr":"Signature wrong","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7909","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7946","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7961","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7952","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7948","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7965","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7926","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-7913","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-8097","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name 
Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6511","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6530","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6559","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6520","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6557","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6584","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6527","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6587","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6690","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6690","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6690","document_id":"FORM","comment_descr":"Test 
Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6690","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6690","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"},{"loan_num":"TEST-6690","document_id":"FORM","comment_descr":"Test Query","loan_status_descr":"Reviewed","document_descr":"Borrower Name Incorrect","status_descr":"Incomplete/Incorrect"}]}



But passing directly on JSON_TABLE worked for me.

Thanks!!
Chris Saxon
June 13, 2023 - 9:13 am UTC

Ah yes :) It helps if you provide JSON that demonstrates the problem you're hitting!

Setting the return type to CLOB also resolve this issue:

JSON_QUERY(:li_json, '$.Cust' RETURNING CLOB )

Removing JSON_query and passing the path directly in JSON_table is better though.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.