X
  • Wednesday, May 13, 2015

The new SQL/JSON Query operators (Part4: JSON_TABLE)

By: Beda Hammerschmidt | Consulting (Coding) Member of Technical Staff

After reading the previous blog postings you should know
about the new SQL/JSON operators JSON_VALUE, JSON_EXISTS and JSON_QUERY. Now
let’s look at the most powerful SQL/JSON addition: JSON_TABLE:

JSON_TABLE is actually not an operator (or function) but something
we call a ‘row source’: Given one input (JSON data ) JSON_TABLE can give us
multiple outputs (rows). A common use case is to un-nest repeating values
inside the JSON data. Each item of an
array gets its own row, and values from that item (json object) are mapped to
columns. As a consequence we can represent (nested) JSON data fully relational
and leverage any relational mechanism (queries, views, reporting, relational
tools) on top of JSON_TABLE.

To illustrate JSON_TABLE we need a little more complex
(nested) JSON data. Instead of inventing something let’s pull some real data
from a common API: Facebook. I have removed a few fields in this example to
keep it shorter and anonymized some values. But you can pull your own JSON data from the
Facebook Graph API (https://developers.facebook.com/docs/graph-api).

CREATE TABLE fb_tab
(col CLOB, constraint json_con_1 CHECK (col IS JSON));

INSERT INTO fb_tab
VALUES('
{
 "data": [{
   "from": {
    "category": "Computers/technology",
    "name": "Oracle"
   },
   "message": "How are Baxters Food Group and Elsevier taking their businesses to...",
   "link": "http://onforb.es/1JOki7X",
   "name": "Forbes: How The Cloud Answers Two Businesses Need For Speed ",
   "description": "Cloud computing can support a companys speed and agility, ...",
   "type": "link",
   "created_time": "2015-05-12T16:26:12+0000",
   "likes": {
    "data": [{
      "name": "Starup Haji"
     },
     {
      "name": "Elaine Dala"
     }
    ]
   }
  },
  {
   "from": {
    "category": "Computers/technology",
    "name": "Oracle"
   },
   "message": "Its important to have an IT approach that not only red...",
   "link": "http://www.forbes.com/sites/oracle/2015/05/07/3-ways-you-can-avoid-sp...",
   "name": "Forbes: 3 Ways You Can Avoid Spending Too Much On IT ",
   "description": "Oracles suite of SaaS applications not only reduces costs but...",
   "type": "link",
   "created_time": "2015-05-11T19:23:11+0000",
   "shares": {
    "count": 5
   },
   "likes": {
    "data": [{
      "name": "Asal Alibiga"
     },
     {
      "name": "Hasan Reni"
     }
    ]
   },
   "comments": {
    "data": [{
     "from": {
      "name": "Cesar Sanchez"
     },
     "message": "Thanks for this information",
     "created_time": "2015-05-12T02:52:09+0000",
     "like_count": 1
    }]
   }
  }
 ]
}
');

commit;

This (shortened) data contains two posting on the Oracle Facebook
feed. People can like and comment postings. Each posting is an item in the array that can
be accessed with the path expression $.data.

Since JSON _Table produces rows we need to tell it when to start
a new row. This is done by providing a path expression that selects every item
of the collection (array) that we want to project as a separate row. In this
case we want a new row for every posting. The path expression is therefore:
$.data[*].

Then for every item that is selected by this ‘row path expression’ we select
the columns values by providing a relative path expression, for instance
$.message.

So, how does the syntax look like? JSON_TABLE is a new
keyword used in the FROM clause of the query. It uses an implicit LATERAL JOIN,
therefore the base table is selected first and the JSON column is the input to
the JSON_TABLE:

SELECT jt.*
FROM   fb_tab,
       JSON_TABLE(col, '$.data[*]'
         COLUMNS (
           "Message" PATH '$.message'
       )) "JT";

Message

--------------------------------------------------------------------------------
How are Baxters Food
Group and Elsevier taking their businesses...

It's important to
have an IT approach that not only reduces c...

As you can see this query extracts the value of the 'message' field and gives us one row per value.

We can easily add multiple column to the COLUMN clause and customize
them with an optional data type and error clause:

SELECT jt.*
FROM   fb_tab,
       JSON_TABLE(col, '$.data[*]'
          COLUMNS (
            "Message"                 PATH
'$.message',

            "Type"       VARCHAR2(20) PATH
'$.type',

            "ShareCount" NUMBER       PATH
'$.shares.count' DEFAULT 0 ON ERROR

        )) "JT";

 

Message                                            Type ShareCount
--------------------------------------------------------------------------------
How are Baxters Food
Group and Elsevier takin...
link 0
It's important to
have an IT approach that not ...
link 5

After this you could easily filter all posting that where shared very often by adding a simple WHERE clause:
...WHERE "
ShareCount" > 20

 

Each column can also have the semantics of JSON_VALUE,
JSON_QUERY or JSON_EXISTS. The default is JSON_VALUE. The next example shows
you all three semantics:

SELECT jt.*
FROM fb_tab,
     JSON_TABLE(col,
'$.data[*]'

      COLUMNS (
       "Message" PATH
'$.message',

       "Type" VARCHAR2(20)PATH '$.type',
       "ShareCount" NUMBER  PATH '$.shares.count' DEFAULT 0 ON ERROR,
       "HasComments" NUMBER EXISTS  PATH '$.comments',
       "Comments" VARCHAR2(4000) FORMAT JSON PATH '$.comments'
    ))
"JT";

Message                            Type ShareCount  HasComments  Comments
--------------------------------------------------------------------------------
How are Baxters Food Group and... link....0............
It's important to have an IT a... link....5............1.........{"data":[{"i...

JSON_EXISTS semantics uses the keyword EXISTS
JSON_QUERY semantics uses the keywords FORMAT JSON.

All options of the operators (e.g. array wrapper options for JSON_QUERY) can be used on a column of JSON_TABLE, too. 

 

So far today, it is late.
In the next blog I will explain how we can drill into the
nested array. 

Join the discussion

Comments ( 1 )
  • Aidan Connolly Sunday, September 17, 2017
    I followed your code but when I tried to do the INSERT of the data I got the following error:

    (I just copied your code into a file f1.sql)

    AIDAN> @f1

    Table created.

    INSERT INTO fb_tab
    *
    ERROR at line 1:
    ORA-02290: check constraint (AIDAN.JSON_CON_1) violated

    Commit complete.

    AIDAN>
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha