Oracle SQL JSON_QUERY ignore key field

Question

I have a json with several keys being a number instead of a fixed string. Is there any way I could bypass them in order to access the nested values?

{
   "55568509":{
      "registers":{
         "001":{
            "isPlausible":false,
            "deviceNumber":"55501223",
            "register":"001",
            "readingValue":"5295",
            "readingDate":"2021-02-25T00:00:00.000Z"
         }
      }
   }
}

My expected output here would be 5295, but since 59668509 can vary from json to json, JSON_QUERY(data, '$."59668509".registers."001".readingValue) would not be an option. I'm not able to use regexp here because this is only a part of the original json, which contains more than this.

UPDATE: full json with multiple occurrences:

This is how my whole json looks like. I would like all the readingValue in brackets, in the example below, my expected output would be [32641, 00964].

WITH test_table ( data ) AS (
    SELECT
        '{
   "session":{
      "sessionStartDate":"2021-02-26T12:03:34+0000",
      "interactionDate":"2021-02-26T12:04:19+0000",
      "sapGuid":"369F01DFXXXXXXXXXX8553F40CE282B3",
      "agentId":"USER001",
      "channel":"XXX",
      "bpNumber":"5551231234",
      "contractAccountNumber":"55512312345",
      "contactDirection":"",
      "contactMethod":"Z08",
      "interactionId":"5550848784",
      "isResponsibleForPayingBill":"Yes"
   },
   "payload":{
      "agentId":"USER001",
      "contractAccountNumber":"55512312345",
      "error":{
         "55549271":{
            "registers":{
               "001":{
                  "isPlausible":false,
                  "deviceNumber":"55501223",
                  "register":"001",
                  "readingValue":"32641",
                  "readingDate":"2021-02-26T00:00:00.000Z"
               }
            },
            "errors":[
               {
                  "contractNumber":"55501231",
                  "language":"EN",
                  "errorCode":"62",
                  "errorText":"Error Text1",
                  "isHardError":false
               },
               {
                  "contractNumber":"55501232",
                  "language":"EN",
                  "errorCode":"62",
                  "errorText":"Error Text2",
                  "isHardError":false
               }
            ],
            "bpNumber":"5557273667"
         },
         "55583693":{
            "registers":{
               "001":{
                  "isPlausible":false,
                  "deviceNumber":"555121212",
                  "register":"001",
                  "readingValue":"00964",
                  "readingDate":"2021-02-26T00:00:00.000Z"
               }
            },
            "errors":[
               
            ],
            "bpNumber":"555123123"
         }
      }
   }
}'
    FROM
        dual
)
SELECT
    JSON_QUERY(data, '$.payload.error.*.registers.*[*].readingValue') AS reading_value
FROM
    test_table;

UPDATE 2:

Solved, this would do the trick, upvoting the first comment.

JSON_QUERY(data, '$.payload.error.*.registers.*.readingValue' WITH WRAPPER) AS read_value

Sorry, but I don't believe you. The value 5295 is a single, scalar value in the JSON. JSON_QUERY() does not return such values - it returns JSON fragments. The only workaround (other than using a different function) is to use the with array wrapper clause, but then the output would be '["5295"]', not what you reported. Are you, in fact, using JSON_VALUE(), not JSON_QUERY()? — user5683823
– user5683823, Commented Feb 28, 2021 at 21:59

user5683823 · Accepted Answer · 2021-02-28 22:07:21Z

1

As I explained in the comment to your question, if you are getting that result from the JSON you posted, you are not using JSON_QUERY(); you must be using JSON_VALUE(). Either that, or there's something else you didn't share with us.

In any case, let's say you are using JSON_VALUE() with the arguments you showed. You are asking, how can you modify the path so that the top-level attribute name is not hard-coded. That is trivial: use asterisk (*) instead of the hard-coded name. (This would work the same with JSON_QUERY() - it's about JSON paths, not the specific function that uses them.)

with test_table (data) as (
  select
    '{
       "59668509":{
         "registers":{
           "001":{
             "isPlausible":false,
             "deviceNumber":"40157471",
             "register":"001",
             "readingValue":"5295",
             "readingDate":"2021-02-25T00:00:00.000Z"
           }
         }
       }
     }'     from dual
)
select json_value (data, '$.*."registers"."001"."readingValue"'
                   returning number) as reading_value
from   test_table
;

READING_VALUE
-------------
         5295

As an aside that is not related to your question in any way: In your JSON you have an object with a single attribute named "registers", whose value is another object with a single attribute "001", and in turn, this object has an attribute named "register" with value "001". Does that make sense to you? It doesn't to me.

answered Feb 28, 2021 at 22:07

user5683823

Sign up to request clarification or add additional context in comments.

1 Comment

Javi Torre Over a year ago

This is working like a charm for the json chunk I posted at first, however, I should've posted the full one with the multiple values desired initially. I agree the "001" does not make any sense, since it's already below. However, there's nothing I can do to change the json since it's from a live system.

Collectives™ on Stack Overflow

Oracle SQL JSON_QUERY ignore key field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related