Articles in this section

See more

Writing SQL Queries against JSON Documents with the CData JSON JDBC Driver

JSON, or JavaScript Object Notation, is a lightweight data interchange format commonly used in web applications, document data stores, APIs and numerous other data exchange processes. Because it has become so popular, JSON documents are an obvious target in data testing. JSON can be incorporated in QuerySurge data tests against flat files, XMLs, databases, big data stores and other JSON documents through a JSON-based JDBC driver.  In this article, we show the connection setup for the CData JSON JDBC driver.

mceclip1.png Note: RTTS, the vendor of QuerySurge, partners with CData to make a broad range of JDBC drivers available to QuerySurge users. For information about our partnership, click here. See all of CData's JDBC offerings here. For questions related to ordering, contact us here.

 

Basic JSON Document using Default Schema Configuration

A "basic" JSON document is one that is highly regular in its structure (i.e. high-level objects all have the same substructure underneath), and the overall structure is not terribly deep. We use such a JSON (colors.json) to illustrate using the CData driver's Relational Data Model. The colors.json example contains a JSON array (see below). JSON object arrays are automatically converted by the CData driver to individual tables with a primary and foreign key that links to the parent document.

We show a sample below from the colors.json file (see the Resources section at the end of this article to download the full colors.json file). In this sample JSON, the colors object array is processed as the colors table for SQL purposes by the driver. 

{
  "colors": [
    {
      "color": "black",
      "category": "hue",
      "type": "primary",
      "code": {
        "rgba": [0,0,0,1],
        "hex": "#000000",
        "opacity": "#001"
      }
    },
    {
      "color": "white",
      "category": "value",
      "code": {
        "rgba": [255,255,255,1],
        "hex": "#ffffff",
        "opacity": "#001"
      }
    },
    ...
  ]
}

Each array item is processed as the set of columns (category, code, color, type). The code column is complex, with nested values. These can be accessed using the driver's bracket notation enclosing the path to the item. All of the columns can be retrieved via the following select:

SELECT _id
,category
,[code.hex]
,[code.rgba]
,JSON_EXTRACT([code.rgba],'$[0]') AS r0
,JSON_EXTRACT([code.rgba],'$[1]') AS r1
,JSON_EXTRACT([code.rgba],'$[2]') AS r2
,JSON_EXTRACT([code.rgba],'$[3]') AS r3
,[code.opacity]
,color
,type 
FROM COLORS

See the CData JSON JDBC Driver documentation for full details.

 

Complex JSON Document using Default Schema Configuration

Complex JSON documents are defined as those containing nested object arrays. The CData driver connection can be configured to create a relational model of the data, treating nested object arrays as individual tables. These tables can be joined together within the SQL and return the desired result set. The connection URI string will be modified in order to define the tables from the object arrays within the JSON document. Consider the sample JSON file json_complex.json, from which is shown below (see the the Resources section at end of this article to download json_complex.json).

{
"destination_addresses": [
"Washington, DC, USA",
"Philadelphia, PA, USA",
"Santa Barbara, CA, USA",
"Miami, FL, USA",
"Austin, TX, USA",
"Napa County, CA, USA"
],
"origin_addresses": ["New York, NY, USA"],
"rows": [
{
   "elements": [
      {
         "distance": {
            "text": "227 mi",
            "value": 365468
         },
         "duration": {
            "text": "3 hours 54 mins",
            "value": 14064
         },
         "status": "OK"
      },
      {
         "distance": {
            "text": "94.6 mi",
            "value": 152193
         },
         "duration": {
            "text": "1 hour 44 mins",
            "value": 6227
         },
         "status": "OK"
       },
       {
         "distance": {
            "text": "2,878 mi",
            "value": 4632197
         },
         "duration": {
            "text": "1 day 18 hours",
            "value": 151772
         },
         "status": "OK"
       },
       {
         "distance": {
            "text": "1,286 mi",
            "value": 2069031
         },
         "duration": {
            "text": "18 hours 43 mins",
            "value": 67405
         },
         "status": "OK"
       },
       {
          "distance": {
             "text": "1,742 mi",
             "value": 2802972
          },
          "duration": {
             "text": "1 day 2 hours",
             "value": 93070
           },
           "status": "OK"
        },
        {
           "distance": {
              "text": "2,871 mi",
              "value": 4620514
           },
           "duration": {
              "text": "1 day 18 hours",
              "value": 152913
           },
           "status": "OK"
         }
       ]
    }
],
"status": "OK"
}

The JSONPATH parameter allows for the defining of the individual tables as shown in the sample connection below.

cdatajson.png

  Note: JSONPath='$;$.destination_addresses;$.origin_addresses;$.rows;$.rows.elements'

This defines the following tables:

  • destination_addresses
  • origin_addresses
  • elements

See this CDATA article regarding the relational model and JSONPath parameter

Sample SQL is provided below that will return all relevant data from the complex JSON document.

SELECT
da.id, oa.origin_addresses, da.destination_addresses,
es.[distance.text] as miles, es.[distance.value] as distance_val,
es.[duration.text] as duration_time, es.[duration.value] as duration_val,
es.status
FROM 
(select _id as id, destination_addresses from destination_addresses) as da,
(select origin_addresses from origin_addresses) as oa,
(select _id as esid, [distance.text], [distance.value], [duration.text], [duration.value],
 status from elements) as es
Where
es.esid = da.id

  Note: Select subqueries are used to pull out the individual tables within the JSON document which were defined in the connection string.

See the CData JSON JDBC Driver documentation for full details.

 

Resources

Related articles