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!

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

Post Details

Added on Nov 29 2020
16 comments
6,061 views