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!