Forum Stats

  • 3,722,125 Users
  • 2,244,228 Discussions
  • 7,849,648 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

ORACLE v12.1 - JSON_TABLE usage for array

Jorgelina
Jorgelina Member Posts: 33 Red Ribbon
edited November 2020 in SQL & PL/SQL

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!

Tagged:

Answers

  • padders
    padders Member Posts: 1,044 Bronze Trophy

    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
    Jorgelina Member Posts: 33 Red Ribbon

    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.


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,195 Black Diamond

    This doesn't work in 12.1 - bug:

    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.

    Jorgelina
  • Jorgelina
    Jorgelina Member Posts: 33 Red Ribbon

    When I use your query, I get an error for the number part on the dummy:

    If 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 ):


  • Paulzip
    Paulzip Member Posts: 8,170 Gold Crown
    edited December 2020

    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
    Solomon Yakobson Member Posts: 18,195 Black Diamond
    edited December 2020

    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
    Solomon Yakobson Member Posts: 18,195 Black Diamond

    BTW, same story even in 19C.

    SY.

  • Jorgelina
    Jorgelina Member Posts: 33 Red Ribbon

    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:


    And if I change dummy to varchar2, weird results are produced:


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,195 Black Diamond

    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
    Jorgelina Member Posts: 33 Red Ribbon

    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
    Solomon Yakobson Member Posts: 18,195 Black Diamond

    Try it in SQL*Plus.

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,195 Black Diamond

    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
    Solomon Yakobson Member Posts: 18,195 Black Diamond

    What SQL*Developer version do you have? Works fine on SQL*Developer 18.3.0.277.2354:


    SY.

  • Jorgelina
    Jorgelina Member Posts: 33 Red Ribbon
    edited December 2020

    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
    Solomon Yakobson Member Posts: 18,195 Black Diamond

    Sorry, I have no APEX experience.

    SY.

  • Jorgelina
    Jorgelina Member Posts: 33 Red Ribbon

    @Solomon Yakobson Thank you for your help, though it did not work for me, I appreciat your help =)

Sign In or Register to comment.