Connecting QuerySurge to JSON Documents with the CData JSON JDBC Driver

Versions: 8.0+

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.

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.

Adding a JSON Data Source Connection using the CData JDBC Driver

To connect to a JSON data source, you'll need to use the Connection Extensibility feature of the QuerySurge Connection Wizard.

1. Download the JSON JDBC driver from CData.
   
   
2. Deploy the JSON JDBC driver to your Agent(s). To install the CData driver, unzip the driver download and run their driver installer (setup.jar) to install the driver on your Agent box(es) (instructions for doing so can be found in the readme.txt file). We recommend that you install the driver in the default location. Once installed, copy the driver file(s) and the license file from the installation directory to your QuerySurge Agent jdbc directory (see the relevant Knowledge Base article for deploying drivers to Agents on Windows or Agents on Linux). You'll need to deploy a driver for each Agent box on which you intend to use the driver. 
   

   Note: For this example the deployed files are: a) cdata.jdbc.json.jar and b)  cdata.jdbc.json.lic.
   
   

3. Login to QuerySurge as a QuerySurge Admin user, and navigate to the Admin view. Select Connections in the leftnav tree, and click the Add button (at the bottom left of the main panel). Leave the Advanced Mode checkbox unchecked. You'll need the following standard information for the next few steps.
   

   Driver Class: cdata.jdbc.json.JSONDriver

   Connection URL template: jdbc:rest:DataModel=<model_type>URI=<file_location>

   
   We suggest using a DataModel of 'Relational', but see the CData driver documentation for more options. An actual URL (using a JSON file at path C:\colors.json) looks like (see below for file details):

   jdbc:json:DataModel=Relational;URI=C:\colors.json

4. Enter a Connection Name, and select * All Other JDBC Connections (Connection Extensibility) from the Data Source dropdown menu.
   
   
   
5. Enter the Driver Class.
   
   
   
6. Enter the Connection URL. 
   
   You can enter an optional Test Query. A test query should only return one row and one column. The table name, in this case, is derived from the top-level JSON property name (see below).
   
   
7. Verify the connection details, and optionally test the connection before saving.
   

You're all set! You can write and execute QueryPairs against the JSON data source of choice. The CData documentation discusses different driver configuration options, advanced settings, and supported SQL syntax and functions.

Default Schema Configurations

Using CData's Relational Data Model, object arrays are automatically converted to individual tables with a primary and foreign key that links to the parent document.

Consider the sample JSON file colors.json, from which we show a sample here (you can download the full JSON file from the Resources section at the end of this article)

{
  "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"
      }
    },
    ...
  ]
}

In this sample JSON, the colors object array in this JSON is processed as the colors table for SQL purposes. 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.

Resources