JSON Unique Keys

When it comes to JSON unique key values there are two schools of thought – those that want keys within objects (squiggly brackets) to be unique and those that don’t care. This can present some interesting outcomes when parsing the data as shown in this post using the SQL JSON_QUERY function.

Note that array pointers start at zero

-- allow non-unique keys is the default when
-- adding a JSON column to a table
SQL> CREATE TABLE json_table
 2   ( json_col CLOB
 3      CONSTRAINT just_json
 4      CHECK ( json_col IS JSON ) );
Table created.

-- insert a row with duplicate keys
SQL> INSERT INTO json_table
 2   VALUES(
 3   '{ "field1" : "value1",
 4      "field1" : "also value1"
 5    }');
1 row created.

-- specifying field1 directly returns the first value
SQL> SELECT JSON_QUERY(jt.json_col,'$.field1' WITH WRAPPER ) AS field1
 2     FROM json_table jt;
FIELD1
------------------------------
["value1"]

-- specifying the first array pointer (zero) returns both
-- values
SQL> SELECT JSON_QUERY(jt.json_col,'$.field1[0]' WITH WRAPPER ) AS field1
 2     FROM json_table jt;
FIELD1
------------------------------
["value1","also value1"]

-- specifying all array values be returned does as well...
SQL> SELECT JSON_QUERY(jt.json_col,'$.field1[*]' WITH WRAPPER ) AS field1
 2     FROM json_table jt;
FIELD1
------------------------------
["value1","also value1"]

-- specifying the second array pointer (1) returns an error
-- meaning its an array of length 1 with 2 values...?
SQL> SELECT JSON_QUERY(jt.json_col,'$.field1[1]' WITH WRAPPER ERROR ON ERROR ) AS field1
 2     FROM json_table jt;
 FROM json_table jt
 *
ERROR at line 2:
ORA-40462: JSON_VALUE evaluated to no value

This JSON Expedition is getting more and more interesting.

Thanks for reading!