Oracle 12c JSON Support

Hi everyone,

In this article I’ll mention JSON support which has come with Oracle DB 12c 12.1.0.2

I hope this will be a useful article in terms of awareness.

With its new version, Oracle Oracle 12c 12.1.0.2 is now capable of knowing and querying JSON data. I liked this feature because we would usually need to write some extra things in order to query and analyze application logs after saving them to DB. With this feature now there’s no need for such an effort.
Now, let’s take a look at what we can do over an example. Let’s start with creating a table first.

create table applicationLog
(
appId number,
appName varchar2(30),
appLog clob,
  CONSTRAINT applicationLogCons CHECK (appLog IS JSON)
);

We created our table. What we need to pay attention to here is the last row in which we defined row constraint. At this point we told Oracle DB that we would save JSON data in appLog column. From now on, the system knows that here will be kept JSON data.

Now let’s add a few rows.

 

insert into applicationLog values (1,'WebContent','{
		"ad"  				: "emrah",
		"soyad"				: "mete",
		"tckn"				: "147456741232",
		"dogumTarihi"		: "01.01.1987",
		"bolum"				: "Bilgisayar Muhendisligi",
		"okul"				: "Yildiz Teknik Universitesi",
		"sinif"				: "Mezun",
		"adres"				: {	"ilce"	:"kadikoy",
								"sokak"	:"yurt",
								"numara":"56",
								"il"	:"Istanbul"}
}
');

insert into applicationLog values (2,'WebContent','{
		"ad"  				: "ali",
		"soyad"				: "veli",
		"tckn"				: "98752145232",
		"dogumTarihi"		: "01.01.1989",
		"bolum"				: "Bilgisayar Muhendisligi",
		"okul"				: "İstanbul Teknik Universitesi",
		"sinif"				: "Mezun",
		"adres"				: {	"ilce"	:"kartal",
								"sokak"	:"sehirci",
								"numara":"789",
								"il"	:"Istanbul"}
}
');

insert into applicationLog values (3,'WebContent','{
		"ad"  				: "ahmet",
		"soyad"				: "mehmet",
		"tckn"				: "21352145232",
		"dogumTarihi"		: "01.01.1989",
		"bolum"				: "Bilgisayar Muhendisligi",
		"okul"				: "Orta Doğu Teknik Universitesi",
		"sinif"				: "Mezun",
		"adres"				: {	"ilce"	:"maltepe",
								"sokak"	:"sakinlik",
								"numara":"12",
								"il"	:"Istanbul"}
}
');
commit;

We have added three-row registry and JSON data exist in all of their appLog columns. If there had been a registry which did not fit this format, the system would’ve given an error.

Now, let’s query our data over appLog column and see how we will insert in our query the elements in JSON data. Say we want our query to put the registries that include “Yildiz Teknik Üniversitesi” in the element OKUL, which resides in JSON data in appLog column.

select  * from applicationLog t1 where t1.applog.okul='Yildiznik Universitesi';

121

As we can see in the result, we managed to perform a query over JSON data. The point which needs our attention is that we can use each element in appLog as appLog”.”

Now, let’s write one more query data example to practice the usage better. This query will bring the “tckn” information of the “maltepe” registries which are in the element ilce, which is in the element adres, and which resides in the data in appLog column.

33

By defining a constraint we said that we would keep JSON data in the appLog column while creating our table in the example above. Now, let’s see how we can query JSON data in a table in which there is no constraint defined.

First, I’ll create a table and I’ll insert one registry with JSON data, and another one without JSON data.

create table applicationLog2
(
appId number,
appName varchar2(30),
appLog clob
);

insert into applicationLog2 values (1,'WebContent','{
		"ad"  				: "emrah",
		"soyad"				: "mete",
		"tckn"				: "147456741232",
		"dogumTarihi"		: "01.01.1987",
		"bolum"				: "Bilgisayar Muhendisligi",
		"okul"				: "Yildiz Teknik Universitesi",
		"sinif"				: "Mezun",
		"adres"				: {	"ilce"	:"kadikoy",
								"sokak"	:"yurt",
								"numara":"56",
								"il"	:"Istanbul"}
}
');

insert into applicationLog2 values (2,'WebContent','Bu data JSON degil');

commit;

Now, let’s find registries which have JSON data in appLog column.

select  * from applicationLog2 t1 where t1.appLog is JSON;

untitled

We’ve listed registries which include JSON data in appLog column by using the phrase “IS JSON” and now let’s see how registries which include JSON data are used in queries.

select  JSON_VALUE(t1.appLog, '$.ad') from applicationLog2 t1 where t1.appLog is JSON;

With the command “JSON_VALUE” we managed to actively insert in the query the elements of the data in appLog.

In the same way, we could’ve used it like this in where clause.

select  * from applicationLog2 t1 where t1.appLog is JSON and JSON_VALUE(t1.appLog, '$.ad') = 'emrah';

untitled

As we can comprehend from the examples above, JSON support which has arrived with Oracle 12 12.1.0.2 makes it much easier to process especially application logs. You can follow the links below for more detailed information on this topic. I hope this has been a helpful article in terms of awareness.

REFERENCES
https://docs.oracle.com/database/121/ADXDB/json.htm

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Uncategorized and tagged , , , , , , . Bookmark the permalink.

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