Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mansoor.

Asked: September 06, 2021 - 11:50 am UTC

Last updated: September 07, 2021 - 7:16 am UTC

Version: 12.1.0.2.0 Enterprise

Viewed 100+ times

You Asked

Hi,

I have the following JSON_OBJECT

Tablename: trans_history


[
  {
    "stepname":"CreateTransFromPain",
     timestamp:"2021-09.."
   },
   {
    "stepname":"TransacionDuplicateValidation",
     timestamp:"2021-09.."
   },
.
.
.
]



Iam using Oracle 12.1.0.2.0 Enterprise


What I need now is to check if the "stepname" -> "XXX" is existing in the DataSet. Either we give the "xxxx" out or just a True or False.



and we said...

There are many ways, depending on exactly what result you want and which version you're on.

In 12.1.0.2, probably the easiest way is JSON_table. Use this to generate a row for each element in the array. Then filter the generated column(s) to see if they match your search:

with rws as (
  select '[
  {
    "stepname":"CreateTransFromPain",
     timestamp:"2021-09.."
   },
   {
    "stepname":"TransacionDuplicateValidation",
     timestamp:"2021-09.."
   }
]' jdoc
  from   dual
)
  select t.*
  from   rws r, json_table (
    r.jdoc, '$[*]'
    columns ( 
      stepname varchar2(100) path '$.stepname'
    )
  ) t
  where  t.stepname = 'CreateTransFromPain';
  
STEPNAME              
CreateTransFromPain    


From 12.2 you can use predicates in JSON_exists, so you could do something like:

with rws as (
  select '[
  {
    "stepname":"CreateTransFromPain",
     timestamp:"2021-09.."
   },
   {
    "stepname":"TransacionDuplicateValidation",
     timestamp:"2021-09.."
   }
]' jdoc
  from   dual
)
  select 'true'
  from   rws r
  where  json_exists (
    r.jdoc, '$?(@[*].stepname == "CreateTransFromPain")'
  );
  
'TRUE'   
true 

Rating

  (2 ratings)

Comments

Asker, September 06, 2021 - 4:12 pm UTC

Thank you for the fast and good response. I most likely have explained my problem not good enough. So Basically I have a select query : SELECT * FROM RTE_OWNER.TRANS_HISTORY where id = '1235537096597307393' ⇾ this query is giving me the JSON Object back from where I need to do the filtering. So I directly need to implement the query into my logic to check if the stepname is existent.


[

   {

      "stepName":"CreateTransFromPain",

      "timestamp":"2021-09-01T17:11:39.515Z",

      "entityReference":{

         "CustomerCreditTransferInitiation":{

            "mapName":"inbound_pain",

            "id":"1235537096597307393",

            "timestamp":"2021-09-01T17:11:39.516Z"

         }

      },

      "latency":3

   },

   {

      "stepName":"TransactionEnrichment",

      "timestamp":"2021-09-01T17:11:39.519Z",

      "latency":0

   },

   {

      "stepName":"ResolveOptyLebe",

      "timestamp":"2021-09-01T17:11:39.521Z",

      "latency":4

   },

   {

      "stepName":"TransactionDuplicateValidation",

      "timestamp":"2021-09-01T17:11:39.533Z",

      "latency":1

   },

   {

      "stepName":"RequestedExecutionDateTimeCheck",

      "timestamp":"2021-09-01T17:11:39.537Z",

      "latency":0

   }
]





with rws as (

SELECT * FROM RTE_OWNER.TRANS_HISTORY where id = '1235537096597307393';

'jdoc from dual)select t.* from rws r, json_table (

    r.jdoc, '$[*]'

    columns ( 

      stepName varchar2(100) path '$.stepName'

    )

  ) t

  where  t.stepName = 'CreateTransFromPain';
STEPNAME
CreateTransFromPain 


This is not working somehow, it's giving me several errors

How about a test case to reproduce your errors ?

Rajeshwaran Jeyabal, September 07, 2021 - 6:48 am UTC

Rather than this
....
This is not working somehow, it's giving me several errors
....

How about a test case to reproduce your errors ?

Here is what we ran based on the data you provided to us.
drop table t purge;
create table t(x int, y clob check(y is json) );
insert into t(x,y) values(1235537096597307393, '[
   {
      "stepName":"CreateTransFromPain",
      "timestamp":"2021-09-01T17:11:39.515Z",
      "entityReference":{
         "CustomerCreditTransferInitiation":{
            "mapName":"inbound_pain",
            "id":"1235537096597307393",
            "timestamp":"2021-09-01T17:11:39.516Z"
         }
      },
      "latency":3
   },
   {
      "stepName":"TransactionEnrichment",
      "timestamp":"2021-09-01T17:11:39.519Z",
      "latency":0
   },
   {
      "stepName":"ResolveOptyLebe",
      "timestamp":"2021-09-01T17:11:39.521Z",
      "latency":4
   },
   {
      "stepName":"TransactionDuplicateValidation",
      "timestamp":"2021-09-01T17:11:39.533Z",
      "latency":1
   },
   {
      "stepName":"RequestedExecutionDateTimeCheck",
      "timestamp":"2021-09-01T17:11:39.537Z",
      "latency":0
   }
]');
commit;

demo@XEPDB1> select t2.*
  2  from t , json_table( y, '$[*]'
  3      columns(
  4          stepName varchar2(60) path '$.stepName') ) t2
  5  where x = 1235537096597307393
  6  /

STEPNAME
------------------------------------------------------------
CreateTransFromPain
TransactionEnrichment
ResolveOptyLebe
TransactionDuplicateValidation
RequestedExecutionDateTimeCheck

demo@XEPDB1> select t2.*
  2  from t , json_table( y, '$[*]'
  3      columns(
  4          stepName varchar2(60) path '$.stepName') ) t2
  5  where x = 1235537096597307393
  6  and t2.stepname ='CreateTransFromPain'
  7  /

STEPNAME
------------------------------------------------------------
CreateTransFromPain

demo@XEPDB1>

dont see any errors as you describe - if that is still not working, you have to show us via a reproducible test case.

PS: Also dont forget to wrap your code with "code" tag while you post something here
Connor McDonald
September 07, 2021 - 7:16 am UTC

Could not have said it any better myself :-)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.