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:

  1. 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.

    ReplyDelete
  2. 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

    ReplyDelete
  3. 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

    ReplyDelete
  4. 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

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

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

    Thanks
    Nitin

    ReplyDelete
  7. 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.

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

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

    ReplyDelete
  10. Matthew Cardozo8/02/2016 7:56 PM

    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

    ReplyDelete
  11. 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

    ReplyDelete