3 Replies Latest reply on Jun 20, 2019 8:31 PM by dmcmahon-Oracle

    JSON_TABLE - how to extract columns when the first key value dynamically changes

    OracleDaveS

      Hi

       

      I have the json output data below. As you can see the first value/key returned is the lowercase currency code, followed by the nested values for that currency code. How can I use the JSON_TABLE function to return relational data in the form currency, code, rate, date?

       

      Tks

       

      {

      eur: {

      code: "EUR",

      rate: 0.88628275220025,

      date: "Tue, 11 Jun 2019 12:00:01 GMT"

      },

      gbp: {

      code: "GBP",

      rate: 0.786891038587,

      date: "Tue, 11 Jun 2019 12:00:01 GMT"

      },

      chf: {

      code: "CHF",

      rate: 0.99284202217432,

      date: "Tue, 11 Jun 2019 12:00:01 GMT"

      }

      }

       

       

        • 1. Re: JSON_TABLE - how to extract columns when the first key value dynamically changes
          Gaz in Oz

          Depends on your Oracle database version, jdon_table() was introduced in Oracle 12.1.0.2.

          For 12.1.0.2 and above using json_table and regexp_substr to get the key "currency":

          with x (json_str) as (
            select '
          {
          eur: {
          code: "EUR",
          rate: 0.88628275220025,
          date: "Tue, 11 Jun 2019 12:00:01 GMT"
          },
          gbp: {
          code: "GBP",
          rate: 0.786891038587,
          date: "Tue, 11 Jun 2019 12:00:01 GMT"
          },
          chf: {
          code: "CHF",
          rate: 0.99284202217432,
          date: "Tue, 11 Jun 2019 12:00:01 GMT"
          }
          }' from dual
          )
          select REGEXP_SUBSTR(x.json_str, '^(.+): {', 1, j.ocurrence, 'm', 1) currency,
                 j.code,
                 j.rate,
                 TO_DATE(j.dt, 'Dy", "dd Mon yyyy hh24:mi:ss "GMT"') dt
          from   x,
                 json_table(json_str, '$.*'
                            columns ocurrence for ordinality,
                                    code VARCHAR2(6)  path '$.code',
                                    rate NUMBER      path  '$.rate',
                                    dt  VARCHAR2(30) path  '$.date'
                 ) j;
          
          CURRENCY   CODE               RATE DT
          ---------- ------ ---------------- -------------------
          eur        EUR     .88628275220025 2019-06-11 12:00:01
          gbp        GBP       .786891038587 2019-06-11 12:00:01
          chf        CHF     .99284202217432 2019-06-11 12:00:01
          
          

          For versions < 12.1.0.2 you could do it with regexp_substr for all columns

          . use APEX_JSON function

          https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#AEAPI29699

          . Use PL/JSON

          https://github.com/pljson/pljson

          ...and for Oracle db versions > 12.1.0.2 you have more options working natively with JSON in the database.

          Later versions of db have a lot of enhancements and new functionalty to work with JSON.

          • 2. Re: JSON_TABLE - how to extract columns when the first key value dynamically changes
            Gaz in Oz

            Here's an example just using regexp_substr if your db version doesn't have json_table:

            with x (json_str) as (
              select '
            {
            eur: {
            code: "EUR",
            rate: 0.88628275220025,
            date: "Tue, 11 Jun 2019 12:00:01 GMT"
            },
            gbp: {
            code: "GBP",
            rate: 0.786891038587,
            date: "Tue, 11 Jun 2019 12:00:01 GMT"
            },
            chf: {
            code: "CHF",
            rate: 0.99284202217432,
            date: "Tue, 11 Jun 2019 12:00:01 GMT"
            }
            }' from dual
            )
            select REGEXP_SUBSTR(x.json_str, '^(.+): {',              1, o.occurrence, 'm', 1)                                        currency,
                   REGEXP_SUBSTR(x.json_str, 'code: "(.+)",',         1, o.occurrence, 'm', 1)                                        code,
                   TO_NUMBER(REGEXP_SUBSTR(x.json_str, 'rate: (.+),', 1, o.occurrence, 'm', 1))                                       rate,
                   TO_DATE(REGEXP_SUBSTR(x.json_str, 'date: "(.+)"',  1, o.occurrence, 'm', 1), 'Dy", "dd Mon yyyy hh24:mi:ss "GMT"') dt
             from   x,
                  (select level occurrence from dual connect by level <= 3) o;
            
            CURRENCY CODE               RATE DT
            -------- ------ ---------------- -------------------
            eur      EUR     .88628275220025 2019-06-11 12:00:01
            gbp      GBP       .786891038587 2019-06-11 12:00:01
            chf      CHF     .99284202217432 2019-06-11 12:00:01
            
            
            • 3. Re: JSON_TABLE - how to extract columns when the first key value dynamically changes
              dmcmahon-Oracle

              This is a so-called "associative array" use case. In that use case, the field names are data, not metadata, which doesn't fit with the flattened model JSON_TABLE is trying to produce. What you'd need is a way to pivot the associative array into name/value pair rows. We're looking to add that in a later release, but it's tricky because there's a semantics issue with allowing a trailing function on a path to operate on anything other than the value found at that path location.

              I'm going to forward this link to the lead developer who works on JSON_TABLE to see if he has additional comments, and/or whether he can share the longer-term solution publically.

              For now, I think you'd have to extract the name/value pairs using the PL/SQL JSON DOM interfaces.