QuerySurge supports reading JSON files using a JDBC driver that is deployed automatically. Using this driver data testers can use familiar SQL syntax to retrieve data from JSON files and validate it against any QuerySurge supported Source or Target.
Note: All files referenced in this article are available for download in the Resources section at the end of the article.
Connection Setup
JSON File connection can be set up in the QuerySurge Admin view, and be utilized in the same manner as any other Connection types (i.e. in QueryPairs and Staging Queries, and in Test Suite Connection overrides).
For details on setting up a Connection to JSON File, see Configuring Connections: JSON File
Querying JSON Files
The JSON File JDBC driver supports a SQL syntax that is familiar to users of relational databases such as PostgreSQL, SQLite, and MySQL. The main difference is in the FROM clause, where a special function named read_json is used to load data from a JSON file. Below is an example of this syntax where filename is the full file path to the JSON file being queried.
SELECT * FROM read_json(filename);Querying Basic JSON DATA
A basic JSON document is one that is highly regular in its structure and the overall structure is not too deep. We use one such file users.json to illustrate use of the JSON JDBC driver.
[
{
"id": 1,
"name": "Alice Johnson",
"created at": "2026-06-03T09:15:00Z",
"active": true
},
{
"id": 2,
"name": "Bob Smith",
"created at": "2026-06-03T10:30:00Z",
"active": false
},
{
"id": 3,
"name": "Carol Davis",
"created at": "2026-06-03T11:45:00Z",
"active": true
},
{
"id": 4,
"name": "David Wilson",
"created at": "2026-06-03T13:00:00Z",
"active": false
}
]
users.json
Assuming file is located in the directory C:\myData following is a sample sql to query the file:
SELECT * FROM read_json('C:\myData\users.json');When executed in QuerySurge, the following results are returned:
Queries can include standard syntax like JOINs, GROUP BYs, single-valued functions, aggregate functions, aliases, and the like. Note that references to column names with spaces, special characters, and starting numerical characters must be surrounded by double quotes:
select id,
name,
"created at",
active
from read_json('C:\myData\users.json');Note: Duplicate column names are not supported. Therefore all columns (and as such fields in JSON file) must be unique.
Querying Nested Objects And Arrays
In practice, JSON files are often more complex than the simple example shown earlier. Many JSON documents contain nested objects and arrays that form a hierarchical structure. The colors.json file below is one such example.
{
"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"
}
},
...
]
}colors.json
To access the data contained within the colors array, the JSON driver provides an additional function named unnest. This function expands a list of objects into individual rows, allowing the nested data to be queried in a tabular format. The array to be expanded—in this case, colors—is passed as an argument to the function.
A table alias and column alias should be specified when using unnest, as they enable access to the resulting fields using dot (.) notation. Also note that array indexing is 1-based, meaning the first element is referenced using index 1 rather than 0.
SELECT c.color,
c.category,
c.type,
c.code.rgba[1] As red,
c.code.rgba[2] As green,
c.code.rgba[3] As blue,
c.code.rgba[4] as alpha,
c.code.hex,
c.code.opacity
FROM read_json('C:\myData\colors.json'), UNNEST(colors) As t(c);Executing the above query produces the following result in QuerySurge:
Note: When using UNNEST, usage of the . notation is required to access fields within the nested objects. A simple SELECT * FROM read_json(...) does not flatten nested objects into individual columns.
Explicitly Specifying Data Types
The JSON JDBC driver automatically attempts to infer the data type of each field when reading a JSON file. While this is often sufficient, data types can also be defined explicitly by supplying a columns argument to the read_json function. For the users.json file, the query specifying column names would be written as follows:
Select * from read_json('C:\myData\users.json',
columns={id: INTEGER, name: VARCHAR, 'created at': DATETIME, active: BOOLEAN });When working with nested objects, the format of the columns argument differs slightly. Nested objects are represented internally as STRUCT data types by the driver, so their field definitions must be enclosed within a STRUCT declaration. Since the data in colors.json contains nested objects that are expanded into columns, the column definitions are specified using STRUCT types, as shown in the following query:
SELECT
c.color,
c.category,
c.type,
c.code.rgba[1] AS red,
c.code.rgba[2] AS green,
c.code.rgba[3] AS blue,
c.code.rgba[4] AS alpha,
c.code.hex,
c.code.opacity
FROM read_json(
'C:\myData\colors.json',
columns = {
colors: 'STRUCT(
color VARCHAR,
category VARCHAR,
type VARCHAR,
code STRUCT(
rgba INTEGER[],
hex VARCHAR,
opacity VARCHAR
)
)[]'
}
),
UNNEST(colors) AS t(c);Multiple Files
In some situation data might be split amongst multiple files. The read_json function provides a simple syntax to join these records by provided a comma separated list as seen below.
SELECT c.color,
c.category,
c.type,
c.code.rgba[1] As red,
c.code.rgba[2] As green,
c.code.rgba[3] As blue,
c.code.rgba[4] as alpha,
c.code.hex,
c.code.opacity
FROM read_json(['C:\myData\colors1.json','C:\myData\colors2.json']),
UNNEST(colors) As t(c);Metadata
Metadata information on JSON file(s) can be extracted by the driver such as the column names and data types. This information is returned in a tabular format using. The following query retrieves the metadata contained in a JSON file:
SELECT column_name, column_type FROM
(DESCRIBE SELECT * FROM read_json('C:\myData\users.json'));When executed in QuerySurge, the following results are returned:
Other Parameters
maximum_object_size - Maximum Object Size controls the largest single object that the JSON driver is allowed to allocate in memory. Increase this setting only when your workload requires larger individual objects to be processed successfully.
Default - 16777216 bytes
Example, following query sets maximum object size to 20000000 bytes or 2MB.
SELECT c.color FROM read_json('C:\myData\colors1.json', maximum_object_size=20000000),
UNNEST(colors) As t(c);
Resources