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 1000+ 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 Chris 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)

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

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

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 latest video and Chris's latest video 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.