Skip to Main Content

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!

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

Processing

Post Details

Added on Nov 29 2020
16 comments
6,072 views