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!

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

Gaz in Oz 
<random hex like string> wrote:
The above approach won't work on the procedure (above) because the SelectCommand.ExecuteNonQuery() throws the following exception:
   ORA-06550: line 1, column 39:
   PLS-00103: Encountered the symbol "@" when expecting one of the following:

...so what does the "NonQuery" look like, that is trying to be executed?

Print it out and paste it here, that way some one actually seeing the query might be able to help.

1 - 1

Post Details

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