Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORACLE v12.1 - JSON_TABLE usage for array

JorgelinaNov 29 2020 — edited Nov 29 2020

HI,
I need a little bit of help.
I have a json that looks like:

{
"OfferId": 9390,
"Purchaser": 13235,
"SupplierId": 19154,
"Currency": "USD",
"Comments": "Product comments",
"JsonComments": {	Table: [
               ['PRODUCT', 'MT', '%'],
               ['CAR', '45', '10'],
               ['BIKE', '13', '0'],
             ],
          FirstHeader: true
        } 
}

With a query like this I can obtain the columns/rows inside "Table: "

SELECT *
FROM 
    JSON_TABLE(:the_json, '$'
          COLUMNS (
               OfferId number path '$.OfferId',
               header varchar2 path '$.JsonComments.FirstHeader',
               nested path '$.JsonComments.Table[*]'
                  columns (
                      nested path '$[*]'
                         columns ( 
                              linenum for ordinality,
                              comments varchar2 path '$' 
                         )
                  )
          )
         )
AS jt
;

Problem is, that I thought linenum could tell me which belonged to the same line, it does not.
At the moment All I get, is 9 rows with each of the values, but no way to determine which belongs to the same row. Is there a way to get this information?
Thank you!

Comments

padders

I think you want ORDINALITY column at the level above if you want it to refer to each array (rather than each array element), e.g.

SELECT *
FROM   json_table (
          :the_json
          '$'
          COLUMNS (
             offerid NUMBER PATH '$.OfferId',
             header VARCHAR2 PATH '$.JsonComments.FirstHeader',
             NESTED PATH '$.JsonComments.Table[*]'
                COLUMNS (
                   linenum FOR ORDINALITY, --<-- here
                   NESTED PATH '$[*]'
                      COLUMNS (
                         comments varchar2 path '$'))));
Jorgelina

I did think of that, but it does not repeat itself, it goes from 1 to 9. (Keep in mind your query misses a , after the :the_json).
Here is the result I get, no matter where I put the linenum for ordinality.
image.png

Solomon Yakobson

undefined (0 Bytes)This doesn't work in 12.1 - bug:
undefined (0 Bytes)There are JSON ordinality related bugs in 12.1 In 12.2:

variable the_json varchar2(1000)
begin
    :the_json := '{
"OfferId": 9390,
"Purchaser": 13235,
"SupplierId": 19154,
"Currency": "USD",
"Comments": "Product comments",
"JsonComments": {Table: [
               [''PRODUCT'', ''MT'', ''%''],
               [''CAR'', ''45'', ''10''],
               [''BIKE'', ''13'', ''0''],
             ],
          FirstHeader: true
        }
}';
end;
/
SELECT  offerid,
        header,
        linenum,
        comments
  FROM  json_table(
                   :the_json,
                   '$'
                   COLUMNS(
                           offerid NUMBER PATH '$.OfferId',
                           header VARCHAR2 PATH '$.JsonComments.FirstHeader',
                           NESTED PATH '$.JsonComments.Table[*]'
                             COLUMNS(
                                     linenum FOR ORDINALITY,
                                     NESTED PATH '$[*]'
                                       COLUMNS(
                                               comments varchar2 path '$'
                                              )
                                   )
                          )
                  )
/

   OFFERID HEADER    LINENUM COMMENTS
---------- ------ ---------- --------
      9390 true            1 PRODUCT
      9390 true            1 MT
      9390 true            1 %
      9390 true            2 CAR
      9390 true            2 45
      9390 true            2 10
      9390 true            3 BIKE
      9390 true            3 13
      9390 true            3 0

9 rows selected.

SQL>

Same code in 12.1 returns NULL linenum:



   OFFERID HEADER        LINENUM COMMENTS
---------- ---------- ---------- ----------
      9390 true                  PRODUCT
      9390 true                  MT
      9390 true                  %
      9390 true                  CAR
      9390 true                  45
      9390 true                  10
      9390 true                  BIKE
      9390 true                  13
      9390 true                  0

9 rows selected.

SQL>

It looks like ordinality is set to NULL when it is the only column. Workaround is to add dummy column and reference it in select list:

SELECT  nvl(dummy,offerid) offerid,
        header,
        linenum,
        comments
  FROM  json_table(
                   :the_json,
                   '$'
                   COLUMNS(
                           offerid NUMBER PATH '$.OfferId',
                           header VARCHAR2 PATH '$.JsonComments.FirstHeader',
                           NESTED PATH '$.JsonComments.Table[*]'
                             COLUMNS(
                                     linenum FOR ORDINALITY,
                                     dummy number path '$',
                                     NESTED PATH '$[*]'
                                       COLUMNS(
                                               comments varchar2 path '$'
                                              )
                                   )
                          )
                  )
/


   OFFERID HEADER        LINENUM COMMENTS
---------- ---------- ---------- ----------
      9390 true                1 PRODUCT
      9390 true                1 MT
      9390 true                1 %
      9390 true                2 CAR
      9390 true                2 45
      9390 true                2 10
      9390 true                3 BIKE
      9390 true                3 13
      9390 true                3 0

9 rows selected.

SQL>

SY.

1 person found this helpful
Jorgelina

When I use your query, I get an error for the number part on the dummy:
image.pngIf I change dummy to a varchar, things get even weirder... (I get 12 rows, and instead of bringing the nvl () I bring the dummy and the offerid - btw, bringing the nvl, still brings 12 rows, is just that I separated for clarification to show you ):
image.png

Paulzip

If you put a value for the array ordinality, does it work?

select nvl(dummy, offerid) offerid
     , header
     , linenum
     , arraynum
     , comments
from   json_table(
         q'!{
"OfferId": 9390,
"Purchaser": 13235,
"SupplierId": 19154,
"Currency": "USD",
"Comments": "Product comments",
"JsonComments": {	Table: [
               ['PRODUCT', 'MT', '%'],
               ['CAR', '45', '10'],
               ['BIKE', '13', '0'],
             ],
          FirstHeader: true
        } 
}
!'
       , '$'
         columns(
           offerid number   path '$.OfferId'
         , header  varchar2 path '$.JsonComments.FirstHeader'
         , nested path '$.JsonComments.Table[*]'
             columns(
               linenum for ordinality
             , dummy number path '$'
             , nested path '$[*]'
                 columns(
                   arraynum for ordinality
                 , comments varchar2 path '$'
                 )
             )
         )
       );
Solomon Yakobson

Same story - we need to reference linenum:

elect nvl(dummy, offerid) offerid
     , header
     , linenum
     , arraynum
     , comments
from   json_table(
         q'!{
"OfferId": 9390,
"Purchaser": 13235,
"SupplierId": 19154,
"Currency": "USD",
"Comments": "Product comments",
"JsonComments": {	Table: [
               ['PRODUCT', 'MT', '%'],
               ['CAR', '45', '10'],
               ['BIKE', '13', '0'],
             ],
          FirstHeader: true
        } 
}
!'
       , '$'
         columns(
           offerid number   path '$.OfferId'
         , header  varchar2 path '$.JsonComments.FirstHeader'
         , nested path '$.JsonComments.Table[*]'
             columns(
               linenum for ordinality
             , dummy number path '$'
             , nested path '$[*]'
                 columns(
                   arraynum for ordinality
                 , comments varchar2 path '$'
                 )
             )
         )
       );


   OFFERID HEADER        LINENUM   ARRAYNUM COMMENTS
---------- ---------- ---------- ---------- ----------
      9390 true                1          1 PRODUCT
      9390 true                1          2 MT
      9390 true                1          3 %
      9390 true                2          1 CAR
      9390 true                2          2 45
      9390 true                2          3 10
      9390 true                3          1 BIKE
      9390 true                3          2 13
      9390 true                3          3 0

9 rows selected.

select nvl(dummy, offerid) offerid
     , header
--     , linenum
     , dummy
     , arraynum
     , comments
from   json_table(
         q'!{
"OfferId": 9390,
"Purchaser": 13235,
"SupplierId": 19154,
"Currency": "USD",
"Comments": "Product comments",
"JsonComments": {Table: [
               ['PRODUCT', 'MT', '%'],
               ['CAR', '45', '10'],
               ['BIKE', '13', '0'],
             ],
          FirstHeader: true
        }
}
!'
       , '$'
         columns(
           offerid number   path '$.OfferId'
         , header  varchar2 path '$.JsonComments.FirstHeader'
         , nested path '$.JsonComments.Table[*]'
             columns(
               linenum for ordinality
             , dummy number path '$'
             , nested path '$[*]'
                 columns(
                   arraynum for ordinality
                 , comments varchar2 path '$'
                 )
             )
         )
       )
/


   OFFERID HEADER          DUMMY   ARRAYNUM COMMENTS
---------- ---------- ---------- ---------- ----------
      9390 true                           1 PRODUCT
      9390 true                           2 MT
      9390 true                           3 %
      9390 true                           4 CAR
      9390 true                           5 45
      9390 true                           6 10
      9390 true                           7 BIKE
      9390 true                           8 13
      9390 true                           9 0

9 rows selected.

select nvl(dummy, offerid) offerid
     , header
--     , linenum
--     , dummy
     , arraynum
     , comments
from   json_table(
         q'!{
"OfferId": 9390,
"Purchaser": 13235,
"SupplierId": 19154,
"Currency": "USD",
"Comments": "Product comments",
"JsonComments": {Table: [
               ['PRODUCT', 'MT', '%'],
               ['CAR', '45', '10'],
               ['BIKE', '13', '0'],
             ],
          FirstHeader: true
        }
}
!'
       , '$'
         columns(
           offerid number   path '$.OfferId'
         , header  varchar2 path '$.JsonComments.FirstHeader'
         , nested path '$.JsonComments.Table[*]'
             columns(
               linenum for ordinality
             , dummy number path '$'
             , nested path '$[*]'
                 columns(
                   arraynum for ordinality
                 , comments varchar2 path '$'
                 )
             )
         )
       )
/


   OFFERID HEADER       ARRAYNUM COMMENTS
---------- ---------- ---------- ----------
      9390 true                1 PRODUCT
      9390 true                2 MT
      9390 true                3 %
      9390 true                4 CAR
      9390 true                5 45
      9390 true                6 10
      9390 true                7 BIKE
      9390 true                8 13
      9390 true                9 0

9 rows selected.

SQL>

As you can see, arraynum without referencing linenum produces wrong results.
SY.

Solomon Yakobson

BTW, same story even in 19C.
SY.

Jorgelina

There must be some setup or something different, because in the database I work in, 12.1... when I use your query and dummy is setup as number I get this:
image.png
And if I change dummy to varchar2, weird results are produced:
image.png

Solomon Yakobson

Works fine for me:

SQL> select version from v$instance;


VERSION
-----------------
12.1.0.2.0


SQL> SELECT  nvl(dummy,offerid) offerid,
  2          header,
  3          linenum,
  4          arraynum,
  5          comments
  6    FROM  json_table(
  7                     :the_json,
  8                     '$'
  9                     COLUMNS(
 10                             offerid NUMBER PATH '$.OfferId',
 11                             header VARCHAR2 PATH '$.JsonComments.FirstHeader',
 12                             NESTED PATH '$.JsonComments.Table[*]'
 13                               COLUMNS(
 14                                       linenum FOR ORDINALITY,
 15                                       dummy number path '$',
 16                                       NESTED PATH '$[*]'
 17                                         COLUMNS(
 18                                                 arraynum FOR ORDINALITY,
 19                                                 comments varchar2 path '$'
 20                                                )
 21                                     )
 22                            )
 23                    )
 24  /


   OFFERID HEADER        LINENUM   ARRAYNUM COMMENTS
---------- ---------- ---------- ---------- ----------
      9390 true                1          1 PRODUCT
      9390 true                1          2 MT
      9390 true                1          3 %
      9390 true                2          1 CAR
      9390 true                2          2 45
      9390 true                2          3 10
      9390 true                3          1 BIKE
      9390 true                3          2 13
      9390 true                3          3 0


9 rows selected.


SQL>

Make sure you are on the latest 12.1 patchset.
SY.

Jorgelina

I have same version... but it does not work for me... so I am guessing there is a database setup or something... though I am using SQLDeveloper, not sure if that could be part of the difference here...

Solomon Yakobson

Try it in SQL*Plus.
SY.

Solomon Yakobson

You can also try:

SELECT  offerid,
        nvl(dummy,header) header,
        linenum,
        arraynum,
        comments
  FROM  json_table(
                   :the_json,
                   '$'
                   COLUMNS(
                           offerid NUMBER PATH '$.OfferId',
                           header VARCHAR2 PATH '$.JsonComments.FirstHeader',
                           NESTED PATH '$.JsonComments.Table[*]'
                             COLUMNS(
                                     linenum FOR ORDINALITY,
                                     dummy varchar2 path '$',
                                     NESTED PATH '$[*]'
                                       COLUMNS(
                                               arraynum FOR ORDINALITY,
                                               comments varchar2 path '$'
                                              )
                                   )
                          )
                  )
/

SY.

Solomon Yakobson

What SQL*Developer version do you have? Works fine on SQL*Developer 18.3.0.277.2354:
image.png
SY.

Jorgelina

undefined (0 Bytes)I have 19.something version.. .so it should not be that... so again I think might be related to something in the configuration of the server... because I have no idea, otherwise, why it is not working for me.
In any case I had to move forward into a pure pl/sql solution, to gather the data.
However I do have a question in case you may know...
When you have something like:

{
   "tk": 123,
   "List": {
                 "number":  123,
                "quote" : "hello world"
              } 
}

In an sql query you can get "List" as the whole json, by using "JSON FORMAT PATH...".
Is there a similar way to get the whole content of an object, inside a json, using apex_json? I looked at all the get_, but none seemed to be able to get me what I needed, which is the WHOLE content of an object inside the json. The json being the JsonComments in my previous examples... I had to resort to parse each part of it, and recreate it, in order to have it full in another place... but still I wonder if there is a better way to do than had to parse it, if in the future there is a change, I will have to change the code, however, if I were able to use something like "JSON FORMAT", I would not have to change anything, since in that case I need the whole object to be passed down to another function.
Thanks in advance!

Solomon Yakobson

Sorry, I have no APEX experience.
SY.

Jorgelina

@solomon-yakobson Thank you for your help, though it did not work for me, I appreciat your help =)

1 - 16

Post Details

Added on Nov 29 2020
#plsql, #sql
16 comments
6,057 views
1 mention