JSON, JSON, JSON - i've been hearing a lot about it lately and we even have systems now where we are storing JSON data in oracle (and by storing i mean 'dumping' the json data into a clob column).
I like to refer to this as DBaaB (database as a bucket - which sadly is where a lot of development seems to be headed nowadays).
Anyway some of our rocket scientist developers, wrote a whizzy new app that has everything stored as json as it made the app development easy (and to be fair created a quick nice to use app) - however there is now a problem - the original design didn't really have reporting requirements - but now it does and the data is not really in a format where that is easy to produce.
So what do we do?
We could just kick it back and let it all be sorted out by the app developers in .net (or whatever is the latest comically named tool in use) or we could try and be helpful and make use of some of the new features in Oracle 12.
I decided on the latter (though at many points i wished i hadn't) - let me share what i finally built in SQL as i think it will be useful for others as i struggled to find any code to borrow to get me started - though Tim (as always) had some good stuff to start me off
Let me first share with you an extract from the JSON data so you can get a fell of what we're dealing with - i loaded it up out of the database column (using cut and paste....) into the following website as it makes the data easier to browse
http://jsoneditoronline.org/
You can see it's a complex JSON document - there are multiple levels of arrays all over the place and the document itself is several thousand lines long....
Looking into whats possible and there seem to be a few paths to take - some of them rely on the column having an 'is json' check constraint defined against it which i currently didn't have so i went down the route of using the JSON_TABLE syntax (which is kind of similar to the XMLTABLE syntax i featured a couple of posts ago)
This proved initially difficult to get working, partly due to understanding the syntax and partly due to the complexity of the document i was working with.
After a lot (and by lot i mean really a lot) of trial and error i discovered that the JSON document, while 'valid' json, has sometimes used [[ instead of [ - oracle does not like this and this was causing a lot of my issues - you can see this on lines 6/7 in the screenshot above. Once i removed this extra [ and the corresponding ] further down it started working.
The SQL i eventually built looks like this (revenue being the clob column in the json_test table)
select revenue,casex
,casey
,casez ,item1,Mel
from json_test x,
json_table(revenue,'$'
columns
(casex varchar2(32) PATH '$.case',
nested path '$.values[*]'
columns (
casey varchar2(2000) PATH '$.case'
,nested path '$.values[*]' columns (casez varchar2(2000) PATH '$.case'
,nested path '$.values[*]' columns (item1 varchar2(2000) PATH '$.Item1',Mel varchar2(2000) PATH '$.AdditionalInformation.Mel.Value'))
)
)
) as jt
where x.servicename='FR'
which outputs something like this (revenue column included too but it's the later columns that are of interest)
Not nice at all to read - but it shows you the constructs for multiple levels of arrays and how to access elements further down the tree - once we have it in this 'SQL' format we can then work with it as normal and do any kind of aggregate reports we like with it.
The issue with the [[ bothered me though so i looked into metalink to see if it was recorded as a bug and had a surprise - it seems there are now json database patch bundles which are on top of database PSU's! - obviously as this is very new there are a lot of fixes being found for it - see link below and a quick screengrab from it
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1992767.1
I'm still to try these patches out to see if it resolves the problem (hopefully it will) but the json related functions actually seem really good once you get the syntax right.
Comments
Post a Comment