- 3,722,813 Users
- 2,244,418 Discussions
- 7,850,109 Comments
Forum Stats
Discussions
Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 2K Databases
- 599 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 32 Multilingual Engine
- 496 MySQL Community Space
- 7 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 420 SQLcl
- 58 SQL Developer Data Modeler
- 185K SQL & PL/SQL
- 21.1K SQL Developer
- 2.4K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.6K Development Tools
- 12 DevOps
- 3K QA/Testing
- 327 Java
- 10 Java Learning Subscription
- 12 Database Connectivity
- 71 Java Community Process
- 2 Java 25
- 11 Java APIs
- 141.2K Java Development Tools
- 8 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 14 Java SE
- 13.8K Java Security
- 4 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 147 LiveLabs
- 34 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 16 Español
- 1.9K Japanese
- 3 Portuguese
ORACLE v12.1 - JSON_TABLE usage for array
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!
Answers
-
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 '$'))));
-
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.
-
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.
-
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 ):
-
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 '$' ) ) ) ); -
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.
-
BTW, same story even in 19C.
SY.
-
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:
-
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.
-
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...
-
Try it in SQL*Plus.
SY.
-
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.
-
What SQL*Developer version do you have? Works fine on SQL*Developer 18.3.0.277.2354:
SY.
-
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!
-
Sorry, I have no APEX experience.
SY.
-
@Solomon Yakobson Thank you for your help, though it did not work for me, I appreciat your help =)
