Yesterday my colleague Alex and I had the pleasure to do some extra presentations during AMIS’s Oracle OpenWorld preview evening. While still not getting around to make some more detailed posts about the new Oracle 12.1.0.2 native JSON functionality, you might enjoy the following presentation which is based (mainly) on material from Mark Drake (Product Manager, Oracle), the upcoming Oracle OpenWorld Hands-on Lab material and my experiences during testing the JSON functionality. So in all it is a good overview of the new JSON functionality and of the content given during Oracle OpenWorld.
The following presentation will give you some shortcuts and an introduction into Oracle JSON functionality. It also has the references to the needed Oracle manual, documentation and good tips to get started with JSON, plugin’s, editors, etc.
Among others, the presentation contains info about:
- JSON Path Expressions
- Operators
- >> JSON_VALUE
- >> JSON_QUERY
- >> JSON_TABLE
- >> JSON_EXISTS
- >> JSON_TEXTCONTAINS
- Conditions
- >> IS (NOT) JSON
- Error Handling
- >> ERROR ON ERROR, NULL ON ERROR, etc
- Returning results
- >> PRETTY /ASCII
- >> WITH WRAPPER / WITH CONDITIONAL WRAPPER
- Loading JSON data
- >> SQLLOADER, EXTERNAL TABLE
- Indexing JSON data
- References
- Tips to get started
Join us at Oracle OpenWorld and see for more detailed presentations content and hands-on lab sessions, the overview slide in the following presentation.
HTH / CU@OOW
Nice walk through the JSON functionality, Marco.
One thing I miss in the JSON path expressions is the ability to “look up a node with a certain value” like XPath expressions can handle. Like for example I can write this in XMLTABLE():
, city_name varchar2(100) path ‘result/address_component[type=”locality”]/long_name’
Meaning that “out of the multiple address_component nodes, pick the node that has child type with value locality and give me the value of the child long_name.”
That I can’t do with JSON_TABLE. But then again the NESTED PATH in JSON_TABLE is very nice, so it is possible to work around the limitation 😉 I’ve done a workaround using PIVOT:
http://dspsd.blogspot.dk/2014/09/jsontable-or-xmltable-comparison-with.html
(That post is not an exhaustive list of JSON_TABLE functionality at all, but just a quick little comparison of a single use case of either XMLTABLE or JSON_TABLE 😉 )
(PS. Thanks for page 50 in your presentation. Nice way to load multiple files via external table.)
BTW… Great post (http://dspsd.blogspot.dk/2014/09/jsontable-or-xmltable-comparison-with.html)!
😎
BTW I distilled the SQLLoader/External Table etc syntax from post’s from Tim Hall here:
http://www.oracle-base.com/articles/10g/load-lob-data-using-sql-loader.php
http://www.oracle-base.com/articles/10g/ExternalTablesContainingLobData.php
Thanks Kim.
To be honest, were talking JSON now, not XML.
Initially I had the same notion as you, comparing the operators for the different realms at first as well, but in we are talking, in the case of JSON, about a new query standard which is still evolving. Oracle in conjunction with some other mayor DB / hardware supplier is working towards a new JSON database query standard (as mentioned in the slides). So in all, its new (and far from complete).
Therefore JSON_TABLE and XML_TABLE will be ending up completely different in behavior (they already do “NULL ON ERROR”?) and needs for the specific different kinds of data formats (and standards).
That said, I will point one of the developers in charge to this comment and your post to see if they (and the other company) find it feasible to implement such a functionality.
Sure, I am not arguing that “JPATH” should be practically identical to “XPATH”, not at all. I am not even sure I would like my “wish” to be fullfilled, as that might be a step “in the wrong direction” for the development of JSON. It’ll depend on the way JSON standards will evolve. Maybe some day as there is “simple” XPath and “complex” XQuery, there might be similar (not identical) standards for querying JSON data – and if that happens in JSON standard, then presumably Oracle JSON functions will adapt such methods as they evolve.
My purpose in my post was mainly to show people used to XML_TABLE how they can do similar stuff with JSON_TABLE. Particarly as more and more webservices return JSON results these days, it is nice for an Oracle database developer to be able to utilize the results of such webservice calls as simply as we’ve previously been able to do it for webservices that return XML results.
Apparently the “wish” was something along the lines which was already considered (just asked). Let’s see how the “JPATH” (good abbreviation 😉 btw) evolves over the years. There will probably be a lot of discussions between JSON organizations and DB companies about “the road to travel”.