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!

Segue From Table Functions To JSON SQL

Its been a while since my last post. There are two reasons for that; one – I’ve said all I wanted to about table functions and such and two – I’ve started on a new direction (tangent?). ¬†JavaScript Object Notation (JSON) and SQL. This post is my segue from the table functions series into a JSON SQL series.

-- create a table with a JSON column
CREATE TABLE json_table
( json_column CLOB,
 CONSTRAINT must_be_json
 CHECK ( json_column IS JSON ) );

-- insert some json data
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "1 and 2"} }');
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "2 and 2"} }');
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "3 and 2"} }');

-- create object types for use in the table function
CREATE TYPE json_o AS OBJECT ( json_text VARCHAR2(30) );
/
CREATE TYPE json_t AS TABLE OF json_o;
/

-- and now for the table function
/*--------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION get_json_one ( p_curs SYS_REFCURSOR )
                  RETURN json_t
                  PIPELINED AS
/*--------------------------------------------------------------------*/
  v VARCHAR2(30);
BEGIN
  LOOP
    FETCH p_curs INTO v;
    EXIT WHEN p_curs%NOTFOUND;
    PIPE ROW(json_o(v));
  END LOOP;
END;
/

Then the function is called from a query like this:

SQL> SELECT JSON_VALUE(j.json_text,'$.fieldtwo') two
 2     FROM TABLE(get_json_one(CURSOR(SELECT jt.json_column.fieldone
 3                                      FROM json_table jt))) j
 4 /

TWO
--------------------
1 and 2
2 and 2
3 and 2

3 rows selected.

I’ve already covered table functions a lot in other posts so I’ll just explain the JSON portions of the code. First up is the REF Cursor. When run in SQL*Plus it looks like this:

SQL> SELECT jt.json_column.fieldone
 2 FROM json_table jt;

FIELDONE
------------------------------
{"fieldtwo":"1 and 2"}
{"fieldtwo":"2 and 2"}
{"fieldtwo":"3 and 2"}

3 rows selected.

It uses dot notation to get the value of fieldone from the json column in the table returning it as a JSON Object (squiggly brackets}. The JSON object is then passed to the JSON_VALUE function which extracts the value for fieldtwo.

JSON_VALUE(j.json_text,'$.fieldtwo')

You are probably thinking that is a lot of code to do such a simple thing and you are right. But its a nice way to change over from table functions to JSON SQL.

Here is a much simpler way to do the same thing as the table function.

SQL> SELECT JSON_VALUE(j.f1,'$.fieldtwo') f2
  2    FROM ( SELECT jt.json_column.fieldone AS f1
  3             FROM json_table jt ) j;0

F2
------------------------------
1 and 2
2 and 2
3 and 2

3 rows selected.

The next few posts will explore JSON SQL in more detail.

Thanks for reading!