Question: How to search in Json file attached in BLOB field using Oracle 12c?

Question

How to search in Json file attached in BLOB field using Oracle 12c?

Answers 2
Added at 2017-01-05 18:01
Tags
Question

I want search text inside of json. that json is persisted in blob field. I have created the follow table

CREATE TABLE APPLICATION
  (
    applicationId   VARCHAR2(36),
    customerId      VARCHAR2(36),
    assignee        VARCHAR2(36),
    status          VARCHAR2(36),
    applicationDate TIMESTAMP,
    closerDueDate   TIMESTAMP,
    closedDate      TIMESTAMP,
    application blob ,
    CONSTRAINT application CHECK(application IS JSON FORMAT JSON)) LOB (application) STORE AS(STORAGE (NEXT 15M));

To persist one row I've used the SQL Develop UI to add all fields value and attach the json file into blob field. The file attached in blob field has json text (huge json).

Example of file

{
   "fields" : {
        "Customerid" : "Organization"
   }     
}
//Huge Json

I want to search inside of blob field using path. Like: fields.Customerid:"Organization"

I am using the follow query but it's not getting any row

select * 
from application app 
where dbms_lob.instr(app.application, utl_raw.CAST_TO_RAW('AutomationRuleSet'), 1, 1) > 0;

I know that value exists inside json persisted


Edit 1

if I try:

Select app.application 
From APPLICATION app 
Where JSON_QUERY(app.application, '$.fields.CustomerID') = 'AutomationRuleSet';

I got:

Error que empieza en la línea 1 del comando: 
Select app.application From APPLICATION app Where JSON_QUERY(app.application, '$.fields.CustomerID') = 'AutomationRuleSet' 
Error en la línea de comandos:2 Columna:6 Informe de error: 
Error SQL: ORA-40499: no format defined for binary data type

what's the problem?

Answers to

How to search in Json file attached in BLOB field using Oracle 12c?

nr: #1 dodano: 2017-01-05 19:01

you can find rows whose JSON BLOBS contain the key path with json_exists():

select * from application app
where json_exists(app.application, '$.fields.Customerid');

You can search for a specific value with json_value():

select * from application app
where json_value(app.application, '$.fields.Customerid') = 'AutomationRuleSet';

As you're using a BLOB you may need to specify format json, though I dont' seem to need to while testing this in 12.2 and your table definition:

select * from application app
where json_exists(application format json, '$.fields.Customerid');

select * from application app
where json_value(application format json, '$.fields.Customerid') = 'AutomationRuleSet';

Oracle Live SQL demo.

nr: #2 dodano: 2017-01-05 21:01

Quick solution: I have found the solution, basically I had to convert the BLOB to VARCHAR2

select UTL_RAW.CAST_TO_VARCHAR2( DBMS_LOB.SUBSTR( app.application, 4000, 1 )) AS aplication
from application app
Where JSON_VALUE(UTL_RAW.CAST_TO_VARCHAR2( DBMS_LOB.SUBSTR( app.application, 4000, 1 )), '$.fields.Customerid') = 'AutomationRuleSet';

Other solution:

Use JSON_TABLE and create table with json field

Source Show
◀ Wstecz