Unleashing the Potential of JSON inside Oracle Database

Unleashing the Potential of JSON inside Oracle Database

JSON (JavaScript Object Notation) is a lightweight data-interchange format primarily for transferring data between systems. In most cases, we parse the JSON object to store the values in relational tables but we can also store the JSON object in a single table column and extract the data using SQL.

In my use case below, using a public API (https://postcodes.io), I will extract and save the data related to a postcode.

To begin, we need to create a table column to store the JSON Document this could be either:

  • JSON datatype - Oracle 21c Database

  • VARCHAR2 - small documents

  • CLOB

  • BLOB

For Oracle 21c users, JSON datatype is the best option; however, for other database versions, the best option is to use a BLOB column, as it stores the exact JSON document without any character set conversions, resulting in less storage in most cases. VARCHAR2 is only recommended when we can ensure that the JSON document won't exceed the maximum size of 32767.

create table location (
    id                 number generated by default on null as identity 
                          constraint location_id_pk primary key,
    postcode              varchar2(10),                               
    postcode_json_content blob

and a trigger calling the API and save the request as a blob.

create or replace trigger "BI_LOCATION"   
 before insert on "LOCATION"               
 for each row  
 l_json_postcode blob;
     l_json_postcode := apex_web_service.make_rest_request_b(
       p_url => 'https://api.postcodes.io/postcodes/'||:new.postcode,
       p_http_method => 'GET');
       :NEW.postcode_json_content := l_json_postcode;

If we insert any valid postcode at this point, the trigger will call the API and save the blob content, but it is only a blob content. If we try to save an image in postcode_json_content, that will work because there is nothing to prevent only JSON valid data. To solve that, we add a constraint as follows.

alter table LOCATION
 add constraint postcode_data_json 
 check ( postcode_json_content is json );

The column postcode_json_content now only accepts valid JSON objects. Now we can add some data.

insert into location(postcode) values ('SW1A1AA');

If a valid JSON is returned from the API, we have the following

select to_clob(postcode_json_content) postcode_json_content from location;

    "status": 200,
    "result": {
        "postcode": "SW1A 1AA",
        "quality": 1,
        "eastings": 529090,
        "northings": 179645,
        "country": "England",
        "nhs_ha": "London",
        "longitude": -0.141588,
        "latitude": 51.501009,
        "european_electoral_region": "London",
        "primary_care_trust": "Westminster",
        "region": "London",
        "lsoa": "Westminster 018C",
        "msoa": "Westminster 018",
        "incode": "1AA",
        "outcode": "SW1A",
        "parliamentary_constituency": "Cities of London and Westminster",
        "admin_district": "Westminster",
        "parish": "Westminster, unparished area",
        "admin_county": null,
        "admin_ward": "St James's",
        "ced": null,
        "ccg": "NHS North West London",
        "nuts": "Westminster",
        "codes": {
            "admin_district": "E09000033",
            "admin_county": "E99999999",
            "admin_ward": "E05000644",
            "parish": "E43000236",
            "parliamentary_constituency": "E14000639",
            "ccg": "E38000256",
            "ccg_id": "W2U3Z",
            "ced": "E99999999",
            "nuts": "TLI32",
            "lsoa": "E01004736",
            "msoa": "E02000977",
            "lau2": "E09000033"

Now we can easily extract the data and specify the correct data type to be returned.

select l.postcode_json_content.result.latitude.number() as latitude,
        l.postcode_json_content.result.longitude.number() as longitude,
        l.postcode_json_content.result.region.string() as region,
        l.postcode_json_content.result.country.string() as country,
        l.postcode_json_content.result.parish.string() as longitude
   from location l

Important! We need to prefix the columns alias to avoid the error below.

ORA-00904: "POSTCODE_JSON_CONTENT"."RESULT"."LONGITUDE": invalid identifier

Did you find this article valuable?

Support Rodrigo Mesquita by becoming a sponsor. Any amount is appreciated!