Skip to content

JSON

This guide provides instructions on how to set up and use JSON files with Team Edition. The JSON driver supports standard JSON files and allows you to work with JSON data as if it were in a database. You can retrieve data and apply filters, sorting, and other operations, even combining data from multiple files.

Important

When using the JSON driver, all connected JSON files are read-only. To make changes, you need to update the original files outside Team Edition.

Before you start, you need to create a connection in Team Edition and select the appropriate JSON driver. If you haven’t done this, see our Database Connection article.

Tip

Team Edition can create a connection directly from AI Chat.

Upload one or more JSON files in AI Chat and click Open as table.

You can open and work with JSON files stored in your Cloud Storage directly in Team Edition. Double-click a file to create a temporary connection. Files opened during a session appear in the File databases folder in Database Navigator. These temporary connections are removed when you close Team Edition.

JSON Files driver connection settings

This section describes how to set up a connection using the JSON driver. The connection settings page requires the following fields:

Field Description
Connect by (Path/URL) Choose whether to connect using a local host path or a URL.
File paths Specify the location of the JSON file(s). Choose an action:

- Edit: Modify an existing file or folder selection.
- Add: Add a new file or folder.
- Remove: Delete a selected file or folder.

When Editing or Adding, choose from:

- File: Select a single file.
- Folder: Choose a directory containing multiple JSON files. For more details, see Folder structure section.
- Remote: Access a remote folder via Cloud Storage. This feature is available only in Ultimate and Team Editions.
Driver name This field will be auto-filled based on your selected driver type.
Driver settings If there are any specific driver settings, configure them here.

For details on driver properties, see File-based driver properties.

Tip

When using the Folder option, Team Edition scans the directory up to two levels deep for JSON files. For more information, see folder structure. If you select a folder, Team Edition organizes files in schemas based on their directory structure.

Features and capabilities

Advanced SQL query capabilities

The JSON driver supports the full range of SQL queries:

  • Simple queries (e.g., SELECT * FROM table): Data is read directly from the JSON file.
  • Complex queries (e.g., using WHERE, JOIN, ORDER BY, GROUP BY): When a complex query is executed for the first time, the driver imports the entire JSON file into an internal database to enable advanced SQL functions. Subsequent queries run faster because the data is already imported into an internal database.

Structuring JSON files with a schema

To enhance how Team Edition interprets JSON files, you can define a schema using a DDL (Data Definition Language) file.

How to create a DDL file

  1. Create a .ddl file with the same name as your JSON file, placing it in the same directory (e.g., employees.json and employees.json.ddl).
  2. Write a schema using the CREATE TABLE statement:
CREATE TABLE employees
(
    id         INTEGER,
    name       TEXT NOT NULL,
    age        INTEGER,
    department TEXT
);

You can also use the WITH clause to set a data range - add firstRow and rowCount to your CREATE TABLE statement:

WITH (firstRow = 2, rowCount = 100)
  • firstRow - row number to start reading from (default: 1)
  • rowCount - maximum number of rows to read

Tip

You can also set firstRow and rowCount in the connection properties. DDL file settings take priority.

Important

If the DDL file contains errors, Team Edition will ignore it.

Folder structure

When working with a folder containing multiple JSON files, Team Edition organizes them as follows:

Folder structure Schema in Team Edition
Root files Default schema
Subfolder files Schema named after the subfolder
Files in deeper folders Ignored

If your folder looks like this:

Data/
├── employees.json
├── sales.json
└── Reports/
    └── monthly.json
    └── yearly.json

Team Edition will create:

  • Default schema: employees, sales
  • Reports schema: monthly, yearly

Tip

To focus on specific files, consider selecting individual files or folders when configuring the connection.

Internal database

When you execute a complex query (such as WHERE, JOIN, GROUP BY, or ORDER BY.), on a JSON file for the first time, the JSON driver processes the data by importing it into a temporary internal SQLite database.

By default, this internal database stores data temporarily on the disk during your session and is cleared when Team Edition restarts. To speed up queries on the same file in future sessions, you can specify the internalDbFilePath option in the Driver properties tab (e.g., C:\User\database.db) to reuse the processed data.

Additional features

Team Edition provides additional features compatible with JSON driver, but not exclusive to it:

Category Feature
Data Transfer Data Export
Data Visualization Visual Query Builder
Charts