Case Insensitivity with JSON and Oracle 12c?

3 min readNov 15, 2017

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.

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.

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)
);

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');