Query JSON with SQL

How to query JSON with SQL online

jsonsql.dev lets you run SQL queries directly against JSON data in your browser — no database installation needed. Write familiar SELECT...WHERE...GROUP BY statements and get instant results.

SQL syntax reference for JSON data

The SQL engine supports the following clauses and operators. Each JSON object in the array is treated as a row, and object keys are treated as columns.

SELECT

Choose which fields to return. Use * for all fields, or list specific columns. Supports aliases with AS.

SELECT * FROM data
SELECT name, age FROM data
SELECT name AS employee_name, salary AS pay FROM data

FROM

Specify the data source. If your JSON is a root array, use data. For nested arrays, use the key name (e.g., FROM employees).

SELECT * FROM employees
SELECT * FROM data

WHERE

Filter rows using comparison operators: =, !=, >, <, >=, <=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL. Combine conditions with AND and OR.

SELECT * FROM data WHERE age > 30
SELECT * FROM data WHERE department = 'Engineering' AND salary > 100000
SELECT * FROM data WHERE name LIKE '%ali%'
SELECT * FROM data WHERE department IN ('Engineering', 'Sales')
SELECT * FROM data WHERE manager IS NOT NULL

GROUP BY

Group rows by a column and apply aggregate functions: COUNT(*), COUNT(field), SUM(field), AVG(field), MIN(field), MAX(field).

SELECT department, COUNT(*) as headcount, AVG(salary) as avg_salary
FROM data GROUP BY department

HAVING

Filter groups after aggregation. Works like WHERE but applied to grouped results.

SELECT department, AVG(salary) as avg_salary FROM data
GROUP BY department HAVING AVG(salary) > 100000

ORDER BY

Sort results by one or more columns. Use ASC (default) or DESC.

SELECT * FROM data ORDER BY salary DESC
SELECT * FROM data ORDER BY department ASC, salary DESC

LIMIT and OFFSET

Restrict the number of returned rows and skip rows.

SELECT * FROM data ORDER BY salary DESC LIMIT 5
SELECT * FROM data LIMIT 10 OFFSET 20

DISTINCT

Remove duplicate rows from the result set.

SELECT DISTINCT department FROM data

Dot notation for nested fields

Access nested object properties using dot notation in any clause.

SELECT name, address.city, address.country FROM data
WHERE address.country = 'US'

SQL aggregate functions on JSON data

You can run aggregate functions like COUNT, SUM, and AVG on JSON arrays just like a database table — grouping by any field and filtering with HAVING.

COUNT

COUNT(*) counts all rows. COUNT(field) counts non-null values of a specific field.

SELECT COUNT(*) as total FROM data
SELECT department, COUNT(*) as headcount FROM data GROUP BY department

SUM and AVG

Calculate totals and averages for numeric fields.

SELECT SUM(salary) as total_payroll, AVG(salary) as avg_salary FROM data
SELECT department, SUM(salary) as dept_total, AVG(salary) as dept_avg
FROM data GROUP BY department

MIN and MAX

Find the smallest and largest values.

SELECT MIN(age) as youngest, MAX(age) as oldest FROM data
SELECT department, MIN(salary) as lowest, MAX(salary) as highest
FROM data GROUP BY department

Common SQL query examples for JSON

Practical examples using the sample data. Click "Sample Data" in the tool above to load this dataset and try each query.

Filter by a numeric condition

SELECT * FROM data WHERE salary > 100000

-- Result:
-- | id | name  | department  | salary | age |
-- |----|-------|-------------|--------|-----|
-- | 1  | Alice | Engineering | 120000 | 32  |
-- | 3  | Carol | Engineering | 135000 | 35  |
-- | 5  | Eve   | Engineering | 145000 | 29  |
-- | 8  | Hank  | Engineering | 128000 | 38  |

Sort and limit results

SELECT name, salary FROM data ORDER BY salary DESC LIMIT 5

-- Result:
-- | name  | salary |
-- |-------|--------|
-- | Eve   | 145000 |
-- | Carol | 135000 |
-- | Hank  | 128000 |
-- | Alice | 120000 |
-- | Dave  | 92000  |

Group by with aggregates

SELECT department, COUNT(*) as count, AVG(salary) as avg_salary
FROM data GROUP BY department

-- Result:
-- | department  | count | avg_salary |
-- |-------------|-------|------------|
-- | Engineering | 4     | 132000     |
-- | Marketing   | 2     | 88500      |
-- | Sales       | 2     | 80000      |

Search with LIKE

SELECT * FROM data WHERE name LIKE '%a%'

-- Matches: Alice, Carol, Frank, Grace, Hank (case-insensitive)

Multiple conditions

SELECT name, department, salary FROM data
WHERE department = 'Engineering' AND salary > 130000

-- Result:
-- | name  | department  | salary |
-- |-------|-------------|--------|
-- | Carol | Engineering | 135000 |
-- | Eve   | Engineering | 145000 |

Nested field access

-- Given JSON with nested objects:
-- [{ "name": "Alice", "address": { "city": "SF", "country": "US" } }, ...]

SELECT name, address.city FROM data WHERE address.country = 'US'

SQL on JSON vs traditional databases

For JSONPath queries, see JSON Query with PATH, SQL & MongoDB.

Querying JSON with SQL in different tools

For quick, one-off queries on API responses or config files, jsonsql.dev is faster than installing any tool. For production workloads or files larger than a few megabytes, DuckDB or PostgreSQL are better choices.

Related tools

Frequently asked questions