Friday, April 17, 2015

JSON for APEX Developers (part 3): Querying JSON in Oracle DB 12c

In previous blog posts I talked about JSON for APEX Developers:

In this post I want to show how you store and query JSON data straight in the database.

To start, create a table to store the JSON object:

CREATE TABLE ssn_json
   (id          NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL,
    date_loaded DATE,
    json_document CLOB
    CONSTRAINT ensure_json CHECK (json_document IS JSON));


I inserted a couple of records in the table:


Here's in more detail the JSON in the json_document column:

Now to query the JSON data I can do :


You basically say j (table) . json_document (column) . analyses (first entry in JSON) . ... (other fields of the hierarchy you want to navigate to)
 
You see the data I get back is actually two records as I actually get back the JSON array.

To go into the array and see for example the first record, I can use json_value:


Note that the array start with 0.

And finally to see both records that are in the JSON array I can make use of json_table like this:


The above is just a grasp of what you can do with JSON directly in the database (12c) by using SQL.
If you're interested to read more about how to manipulate JSON in the database, have a look at the documentation, it contains a wealth on information with great examples.

As you can basically query the JSON with SQL you can use this SQL in your APEX reports... in the coming days I'll show you a real case where I used the above techniques to do some interesting searching in data.

11 comments:

Anonymous said...

HI,Dimitri!
Just executed in APEX SQL Workshop a query:

select JSON('{"foo":3,"bar":[1,2,3,4]}') from dual

Result is :

NULL

1 rows returned in 0.00 seconds

But there is no JSON function in Oracle 12c function list!
Also there is no such a name in all_objects and all_synonyms!

How can you explain this?

Thanks in advance,
Andrew.

Dimitri Gielis said...

Hi Andrew,

Not sure where you saw I did JSON() in my example?

In your table you define a CHECK CONSTRAINT CHECK IS JSON.

If you want to parse JSON, you can do:
begin
apex_json.parse('{ "type": "circle", "coord": [10, 20] }');
sys.htp.p('Point at '||
apex_json.get_number(p_path=>'coord[1]')||
','||
apex_json.get_number(p_path=>'coord[2]'));
end;

Hope that helps,
Dimitri

Anonymous said...

Hi,Dimitri!
I mean not apex_json packcage, I mean Oracle 12c itself.It's wonderful to get result
from undocummented function. I want to hear your opinion about this "phantom" function)

Thanks in advance,
Andrew

Dan McGhan said...

Hi Andrew,

I'm guessing you've installed PL/JSON, which adds JSON, JSON_ARRAY, and a couple other object types:
https://github.com/pljson/pljson

Check your object types to verify.

I wrote about using PL/JSON for creating JSON here:
https://jsao.io/2015/07/relational-to-json-with-pljson/

Regards,
Dan

Eternal soul said...

I get this error
ORA-00904: "MSG"."MESSAGES": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 1 Column: 7

Eternal soul said...

fyi i am using 11g. If it is not supported, is there a way? i need this badly

Thanks
Nitin

oracleapex said...

Hello Dimitri Gielis ,

Following your blog since a long time but this my first ever comment :)
i am trying to integrate RESTfull services with HTML 5 or MAF (Mobile Application Framework) is it possible to do in such a manner that i can access all my data of oracle Apex 5.0 in MAF or HTML5.

Dimitri Gielis said...

sure you can do that, that is why REST webservice are there :)

iris said...

I would recommend to use JSON tool for debugging JSON data http://codebeautify.org/jsonviewer and http://jsonformatter.org

Matthew Cardozo said...

Dimitri,

Thanks for your many helpful posts related to APEX.

I've been reading through your series of posts on using APEX to create and consume JSON and have a couple of questions...

I have an apex application where currently I'm getting a csv file from another group and uploading it in to a table on my schema daily. I'd like to automate this using REST services.

1. which method would be recommended from the APEX side: a GET to pull the JSON or POST to receive the JSON?
2. I see in this post that you have a table of json files in clob format...is this a preferred method for dealing with the JSON if I'm planning to put the contained data into another table anyway? What would I need to do to store the JSON to this type of store table (requires a conversion etc?)

If you've already posted about this in another blog I apologize and would appreciate a link to said post.

Thanks

Frank said...

Hi all, we have a unsolved problem with this JSON structure:
DECLARE
s varchar2(32767) := '{"lastUpdateId":10563648449,"bids":[["58999.99000000","0.10705400"],["58998.97000000","0.15232600"],["58995.54000000","0.02962200"],["58995.53000000","0.00108400"],["58993.29000000","0.04216300"]],"asks":[["59000.00000000","3.92435300"],["59000.01000000","0.04115200"],["59000.65000000","0.17911800"],["59003.10000000","0.08673100"],["59005.89000000","0.02280500"]]}';
BEGIN
apex_json.parse(s);
-- sys.dbms_output.put_line('a is '||apex_json.get_varchar2(p_path => 'lastUpdateId'));
dbms_output.put_line('b is '||apex_json.get_varchar2(p_path => '[%d].bids[0][0]'));
dbms_output.put_line('c is '||apex_json.get_varchar2(p_path => 'lastUpdateId.asks'));
END;
... the result is NULL
b is null
c is null
-------------------other test--------
we try with this code:
declare i number := 0;
begin
for i in 0..5
loop
insert into opertest (pre_b,cant_b,pre_a,cant_a) (
SELECT js.*
FROM tablajson,
JSON_TABLE(campojson, '$[*]'
COLUMNS (Lastupdateid number PATH '$.LastUpdateId',
b_price NUMBER PATH '$.bids[i][0]',
b_quant number PATH '$.bids[i][1]',
a_price NUMBER PATH '$.asks[i][0]',
a_quant number PATH '$.asks[i][1]')) as js);
end loop;
end;


give an ERROR
Informe de error -
ORA-06550: línea 5, columna 1:
PL/SQL: ORA-40598: La expresión de la ruta de acceso de JSON contiene un índice de matriz no válido ('$.bids[i][0]')
JZN-00211: Invalid array subscript
en la posición 9
ORA-06550: línea 5, columna 1:
PL/SQL: SQL Statement ignored

I dont know how to tranform this JSON in a table oracle...
the bids element have (price,quantity),(price,quantity)..until 5 iterations and
the element asks the same..
I need to record in this format;
LastupdateID B_price B_quant A_price A_quant
------------ ------ ------ ------ ------
10563648449 58999.99 0.107054
10563648449 58998.97 0.152326
10563648449 58995.54 0.029622
10563648449 58995.53 0.001084
10563648449 58993.29 0.042163
10563648449 59000.00 3.924353
10563648449 59000.01 0.041152
10563648449 59000.65 0.179118
10563648449 59003.10 0.086731
10563648449 59005.89 0.022805

but cannot found the solution...please can help me? thanks