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.
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, 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 v_metering;
This is how it looks like in livesql.oracle.com
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 😉
approaching off the bow 