12C

Case Insensitivity with JSON and Oracle 12c?

About a week ago, I was presenting at ECO on JSON parsing in the Oracle 12c Database and an attendee asked a great question. To summarize, Maria Colgan gave a keynote in which she noted a feature in 12c that allows you to define a column as Case Insensitive. The attendee in my session asked if this also applied to JSON in the Oracle database.

There are enough posts on how to configure your database for case insensitivity so I won’t go into details but check out my friend Tim Hall, @oraclebase for some guidance here. I’d also recommend using the Oracle DBCS to try this. It’s just easier. Here we go:

First  let’s spin up a DBCS instance.

dbcs

Now, we need to connect to the database. I’ll use SQL developer. You’ll need to setup the ssh connection.

 

and the database connection.

db text

Let’s connect and test this out. Remember that you will need to do some additional setup on the database to enable this functionality. Check this link here.

Now that we have our database configured, create two (2) tables.

--Create a normal table. One column to hold a JSON doc and other regular text
CREATE TABLE CI1 (
  id               NUMBER,
  bitcoin_json     VARCHAR2(100),
  name             VARCHAR2(100),
  CONSTRAINT ci1_pk PRIMARY KEY (id)
);

--Create a table with case insensitive columns. One column to hold a JSON doc and other regular text
CREATE TABLE CI2 (
  id               NUMBER,
  bitcoin_json     VARCHAR2(100) COLLATE BINARY_CI,
  name             VARCHAR2(100) COLLATE BINARY_CI,
  CONSTRAINT ci2_pk PRIMARY KEY (id)
);

create table

Next we’ll insert the same data in each table.

INSERT INTO ci1 values (1, '
  {
    "code": "USD",
    "name": "US Dollar",
    "rate": 5645.879004
  }', 'Fred Flinstone');

INSERT INTO ci1 values (2, '
  {
    "code": "EUR",
    "name": "Eurozone Euro",
    "rate": 4781.528803
  }'
, 'Barney Rubble');

------

INSERT INTO ci2 values (1, '
  {
    "code": "USD",
    "name": "US Dollar",
    "rate": 5645.879004
  }', 'Fred Flinstone');

INSERT INTO ci2 values (2, '
  {
    "code": "EUR",
    "name": "Eurozone Euro",
    "rate": 4781.528803
  }'
, 'Barney Rubble');

insert data

 

With our tables populated, we can now run some queries to determine if JSON_VALUE recognizes the case insensitivity on the column.

Test with regular text in a varchar2 defined column

regular text

Test with JSON in a varchar2 defined column

json text

From these examples, it looks as if JSON retains its adherence to Case Sensitivity. I’ll open this post up to discussion.

 

Enjoy!dbaontap

 

Related Posts Plugin for WordPress, Blogger...

Leave a Reply

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