JSON Support in Oracle Database. Try It with livesql.oracle.com


livesql2

Oracle Database (relational) supports JSON very well and easy.

In this post we are creating a table, a view and a couple of indexes for storing JSON data and then querying it.

livesql1

Don’t worry if you don’t have a database for testing, Oracle gives you an environment, try here

livesql.oracle.com let you share scripts and code with the community. I’m sharing the scripts in this blog just for your convenience here

JSON DATA

The Oracle Cloud REST API for billing/metering retrieves data with the following format, let’s use it as an example:

{   "subscriptionId": "nnnnnnn",
    "subscriptionType": "PRODUCTION",
    "serviceName": "Compute",
    "resourceName": "BLOCK_STORAGE",
    "currency": "EUR",
    "gsiProductId": "B88274",
    "startTimeUtc": "2019-01-01T00:00:00.000",
    "endTimeUtc": "2019-02-01T00:00:00.000",
    "dataCenterId": "EM003",
    "serviceEntitlementId": "mmmmmmmmm",
    "costs": [{
      "computedQuantity": 1047.4112903225807,
      "computedAmount": 29.0429677444,
      "unitPrice": 0.0295,
      "overagesFlag": "N",
      "computeType": "Usage"
    }]
}

TABLE & INDEXES & VIEW

CREATE TABLE metering (id RAW (16) NOT NULL, date_loaded TIMESTAMP WITH TIME ZONE, label varchar2(20), jsonmeteringdata CLOB CONSTRAINT ensure_metering_json CHECK (jsonmeteringdata IS JSON));
CREATE BITMAP INDEX metering_servicename_idx ON metering (json_exists(jsonmeteringdata,'$.serviceName'));

CREATE BITMAP INDEX has_resourcename_idx ON metering (json_exists(jsonmeteringdata,'$.resourceName'));
create view v_metering as select met.id, met.jsonmeteringdata.startTimeUtc, met.jsonmeteringdata.endTimeUtc, met.jsonmeteringdata.serviceName, met.jsonmeteringdata.resourceName, met.jsonmeteringdata.costs.unitPrice P, met.jsonmeteringdata.costs.computedQuantity Q, met.jsonmeteringdata.costs.computedAmount Money, met.jsonmeteringdata.costs.overagesFlag Overage, met.jsonmeteringdata.costs.computeType comptype from metering met;

INSERT SOME DATA

INSERT INTO metering
VALUES (
SYS_GUID(),
SYSTIMESTAMP,
'{
    "subscriptionId": "1234567",
    "subscriptionType": "PRODUCTION",
    "serviceName": "Compute",
    "resourceName": "BLOCK_STORAGE",
    "currency": "EUR",
    "gsiProductId": "B88274",
    "startTimeUtc": "2019-01-01T00:00:00.000",
    "endTimeUtc": "2019-02-01T00:00:00.000",
    "dataCenterId": "EM003",
    "serviceEntitlementId": "123456789",
    "costs": [{
      "computedQuantity": 1047.4112903225807,
      "computedAmount": 29.0429677444,
      "unitPrice": 0.0295,
      "overagesFlag": "N",
      "computeType": "Usage"
    }]
  }'
);

QUERY THE DATA

select met.id, met.jsonmeteringdata.startTimeUtc, met.jsonmeteringdata.endTimeUtc, met.jsonmeteringdata.serviceName, met.jsonmeteringdata.resourceName, met.jsonmeteringdata.costs.unitPrice P, met.jsonmeteringdata.costs.computedQuantity Q, met.jsonmeteringdata.costs.computedAmount Money, met.jsonmeteringdata.costs.overagesFlag Overage, met.jsonmeteringdata.costs.computeType comptype from metering met;
select count(*) from met;
This is how it looks like in livesql.oracle.com
livesql
As you can see, it is very easy to query JSON data. Now you have all the power of SQL for reporting, aggregates and many other such as full test search and more.
Enjoy 😉

IT

javier mugueta View All →

Me and my circumstance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: