SQL JSON Query Builder

What the builder writes for each engine

Every engine stores JSON differently and names its functions differently. For a path such as $.customer.address.city the builder reads the value as text with JSON_VALUE on SQL Server and Oracle, #>> on PostgreSQL, JSON_UNQUOTE(JSON_EXTRACT(…)) on MySQL and json_extract (cast to text) on SQLite, then compares that text with your value. Ordering comparisons (>, >=, <, <=) cast the text to a number first. The table lists the main functions and operators the builder emits; it does not use PostgreSQL's ->, ->>, @> or ? operators, or MySQL's ->> shorthand (which is the same as JSON_UNQUOTE(JSON_EXTRACT(…))).

Besides the table, contains uses LOWER(…) LIKE LOWER('%…%'), is one of uses IN (…), and the ignore-case equality operations use the jsonpath .lower() method on PostgreSQL. The empty and non-empty checks use OPENJSON row counts on SQL Server, jsonb_typeof and jsonb_array_length on PostgreSQL, JSON_LENGTH on MySQL, JSON_VALUE(col, '$.type()') with JSON_EXISTS on Oracle, and json_type, json_array_length and json_each on SQLite. The valid and not valid JSON checks use ISJSON, IS JSON / IS NOT JSON, JSON_VALID and json_valid on a text column; on a native JSON column they become IS NOT NULL and 1 = 0. PostgreSQL's #>> takes the whole path as one text array, which is why a nested path needs no chain of -> steps. For each engine's JSON storage and functions in depth, see the PostgreSQL JSON and JSONB guide, the SQL Server JSON guide, the MySQL JSON column guide, the Oracle JSON guide, the SQLite JSON guide and JSON in relational databases compared.

Example: orders shipped to Porto

Click Load sample above to get an order document with a nested customer.address object, an items array and a tags array. Click city in the path tree, choose equals (=) and keep the value Porto. With the default column types — nvarchar(max) on SQL Server, jsonb on PostgreSQL, JSON on MySQL, CLOB on Oracle and TEXT on SQLite — the builder writes:

SQL Server

SELECT *
FROM [orders]
WHERE ISJSON([payload]) = 1 AND JSON_VALUE([payload], '$.customer.address.city') = 'Porto';

PostgreSQL

SELECT *
FROM "orders"
WHERE ("payload")::jsonb #>> '{customer,address,city}' = 'Porto';

MySQL

SELECT *
FROM `orders`
WHERE JSON_UNQUOTE(JSON_EXTRACT(`payload`, '$.customer.address.city')) = 'Porto';

Oracle

SELECT *
FROM orders
WHERE payload IS JSON AND JSON_VALUE(payload, '$.customer.address.city') = 'Porto';

SQLite

SELECT *
FROM "orders"
WHERE json_valid("payload") = 1 AND CAST(json_extract("payload", '$.customer.address.city') AS TEXT) = 'Porto';

Each of these statements was run on 23 September 2026 against SQL Server 2025, PostgreSQL 16, MySQL 8.0, Oracle AI Database 26ai Free and SQLite 3.46, with the sample stored as one row next to a second order, a row with a NULL column and — on the text columns — a row holding not json {. Every engine returned only the sample row, and none raised an error on the invalid row. Table and column names are written in square brackets on SQL Server, in backticks on MySQL and in double quotes on PostgreSQL and SQLite. A quoted name is case-sensitive on PostgreSQL, so type it in lower case unless the table was created with a quoted mixed-case name. On Oracle, a plain name (a letter, then letters, digits, _, $ or #, and not a reserved word such as LEVEL) is written without quotes, so Oracle folds it to upper case, the same way it stored the name from a plain CREATE TABLE orders; any other name is quoted exactly as you type it. Type your own names in the Table and JSON column fields.

Filtering on array elements

A path with [*] matches every element of an array, so there is no single value to extract. Use the any element operations (at least one element matches) or the every element operations (there is at least one element and all of them match). Clicking sku under items › [*] and choosing any element equals KB-104 gives, on PostgreSQL and SQL Server:

SELECT *
FROM "orders"
WHERE EXISTS (SELECT 1 FROM jsonb_path_query(("payload")::jsonb, '$.items[*].sku') AS p WHERE (p #>> '{}') = 'KB-104');

SELECT *
FROM [orders]
WHERE ISJSON([payload]) = 1 AND EXISTS (SELECT 1 FROM OPENJSON([payload], '$.items') AS j WHERE JSON_VALUE(j.[value], '$.sku') = 'KB-104');

• PostgreSQL walks the path with jsonb_path_query and handles several [*] steps in one path.
• MySQL and Oracle walk the array with JSON_TABLE. On MySQL the builder writes (SELECT COUNT(*) …) > 0 instead of EXISTS, because EXISTS over a JSON_TABLE that reads the outer row's column was observed to return no rows on MySQL 8.0.45 even when elements matched.
• SQL Server (OPENJSON), SQLite (json_each), MySQL and Oracle (JSON_TABLE) walk one array level, so a path can hold one [*] on those four engines. On MySQL and Oracle a second [*] ends up in the JSON_TABLE column path, which reads one value per element: for {"a":[{"b":[1,2]},{"b":[3]}]}, any element of $.a[*].b[*] equals 1 returned no row on MySQL 8.0.46 and Oracle 26ai. The builder explains this instead of writing SQL for a second [*], and offers the path with the earlier steps set to [0]. The exception is has / does not have the property on MySQL and Oracle, which use JSON_CONTAINS_PATH and JSON_EXISTS on the whole path and handle several [*] steps. OPENJSON needs database compatibility level 130 or higher.
• Recursive descent ($..sku, a key at any depth) works on PostgreSQL (written as $.**.sku) and Oracle, with the has / does not have the property, equals, does not equal and contains operations. SQL Server, MySQL and SQLite do not support it.

Why a validity guard appears on text columns

A native JSON column (jsonb, MySQL JSON, Oracle 21c's JSON type, SQL Server 2025's json type) cannot hold invalid JSON. A text column can, and one bad row changes what the query does. Each engine was checked with the text not json {:

• SQL Server: JSON_VALUE and OPENJSON fail with JSON text is not properly formatted. The builder adds ISJSON(col) = 1 before the JSON function in the AND.
• MySQL: JSON_EXTRACT fails with Invalid JSON text in argument 1 to function json_extract. The builder adds JSON_VALID(col) = 1.
• SQLite: json_extract fails with malformed JSON. SQLite has no JSON column type, so the builder always adds json_valid(col) = 1.
• Oracle: JSON_VALUE returns NULL rather than an error. The builder still adds col IS JSON so the filter only considers valid documents.
• PostgreSQL: the ::jsonb cast fails on invalid text, and PostgreSQL does not promise to evaluate AND conditions left to right. The builder wraps the column as (CASE WHEN col IS JSON THEN (col)::jsonb END) instead, which needs PostgreSQL 16 or later. The CASE returns NULL for invalid rows, so negative filters (does not equal, is null, does not have the property) also match those rows.

SQL Server, MySQL and SQLite do not guarantee that they evaluate the conditions of an AND from left to right, so the guard keeps invalid rows out of the result but is not a promise that the JSON function never sees one. In the tests described on this page none of the three raised an error on the invalid row. The PostgreSQL CASE form does evaluate the check first.

The SQL Server, PostgreSQL, MySQL and Oracle cards have a Column type select that switches between the native JSON type and a text column, so you can see both forms. The SQLite card always uses a TEXT column, because SQLite has no JSON type.

Engine differences the builder points out

The notes under each card depend on the value your sample holds at the path. These are the behaviours they cover, each checked on the engine versions above:

• JSON_VALUE on SQL Server and Oracle returns NULL when the path holds an object or an array. On PostgreSQL, MySQL and SQLite the same extract returns the object or array as JSON text.
• SQLite's json_extract returns 1 and 0 for JSON true and false, so = 'true' matches nothing there; the other four engines return the text true.
• MySQL's JSON_UNQUOTE(JSON_EXTRACT(…)) returns the text null for a JSON null, so is null or missing matches only missing keys on MySQL. On SQLite a key holding null counts as missing for has the property.
• A non-numeric value in a numeric comparison raises an error on PostgreSQL (::numeric) and Oracle (ORA-01722), becomes NULL on SQL Server (TRY_CAST), and becomes 0 on MySQL and SQLite.
• Equals and contains compare the number as the engine prints it: MySQL, Oracle and SQLite return 89.90 as 89.9, and PostgreSQL returns 1e2 as 100. When your sample's number is spelled differently from how an engine prints it, that card says so.

Getting a sample document from your table

Copy one value of the column and paste it into the JSON document box. These queries return one row in a readable form:

• SQL Server: SELECT TOP (1) payload FROM dbo.orders;
• PostgreSQL: SELECT jsonb_pretty(payload::jsonb) FROM orders LIMIT 1;
• MySQL: SELECT JSON_PRETTY(payload) FROM orders LIMIT 1;
• Oracle: SELECT JSON_SERIALIZE(payload RETURNING CLOB PRETTY) FROM orders FETCH FIRST 1 ROWS ONLY; (works for a JSON column and for JSON text in a CLOB; without RETURNING CLOB a document over 4,000 bytes fails with ORA-40478)
• SQLite: SELECT payload FROM orders LIMIT 1;

In Jam SQL Studio, select the cell in the results grid and press Ctrl+C (Cmd+C on a Mac). A value copied as a quoted JSON string ("{\"a\":1}") is decoded once automatically.

Indexing a path you filter on

An equality filter on one path can use an index built on the same expression. On PostgreSQL, an expression index such as CREATE INDEX orders_city_idx ON orders ((payload #>> '{customer,address,city}')); on a jsonb column was used by the builder's ("payload")::jsonb #>> … = 'Porto' filter on PostgreSQL 16. A GIN index on the whole jsonb column serves the @>, ?, @? and @@ operators, which the builder does not emit. On SQL Server, a computed column ALTER TABLE dbo.orders ADD city AS JSON_VALUE(payload, '$.customer.address.city'); with CREATE INDEX ix_orders_city ON dbo.orders (city); gave an index seek for the builder's ISJSON(...) = 1 AND JSON_VALUE(...) = ... filter on a 20,001-row table. MySQL (generated column), Oracle (function-based index) and SQLite (index on an expression) can index the same expressions; the query must use the identical expression for the index to apply.

On a text column, such an index cannot be built while any row holds invalid JSON: SQL Server fails with Msg 13609 (JSON text is not properly formatted), SQLite with malformed JSON, MySQL with error 3141 and PostgreSQL with invalid input syntax for type json. Oracle's JSON_VALUE returns NULL for such a row, so its function-based index builds. Fix or remove the invalid rows first.

Supported paths and limits

• Path steps: $ (the whole value), .key, [N] (an array index, from 0), [*] (every element) and .. (any depth, PostgreSQL and Oracle). customer.address.city without the $. works too. On PostgreSQL a path takes one [N] per step: #>> returns NULL for an index into a nested array ($.m[0][1]), so the builder refuses that path there.
• Not supported: filter expressions ([?(@.qty > 1)]) and quoted keys ($."first name"): PostgreSQL's #>> and SQL Server's has the property keep the quotes as part of the key and match nothing, so the builder refuses quoted keys on every engine. Keys are written unquoted. A key with characters other than A–Z, 0–9 and _ gets a warning, because SQL Server rejects such a key in a path (Msg 13607) and MySQL and Oracle reject most of them. A key that contains ., [, ] or ", or is empty, cannot be written as a path at all ({"x.y": 1} would become $.x.y, key y inside key x); the path tree marks such rows and shows no SQL for them.
• Input: one JSON document of up to 1,048,576 characters (1 MiB of ASCII text). The path tree lists up to 2,000 paths; any other path can be typed in the Path field.
• Numbers: the preview and the prefilled value keep the digits your sample spells a number with (89.90, 1234567890123456789). The numeric comparisons accept decimal notation only and refuse a number that JavaScript cannot hold exactly, such as 1234567890123456789 (it would be written as 1234567890123456800).
• Operations: extract, 13 comparisons on the value at a path, 14 any / every element comparisons, and 4 checks on the whole value (empty, non-empty, valid JSON, not valid JSON).

Everything stays in your browser

The tool is one script, /js/tools/sql-json-query-builder.js, which contains the builder and the JSON SQL code from the Jam SQL Studio desktop app. The page also loads the site's Umami and Google Ads tags and its event helper (/js/jam-events.js); they see the page view but not your input. Your document, path, values and table names are processed in memory by the tool script. They are not sent to any server, not placed in the URL, and not written to localStorage or sessionStorage. Clear empties the document box.

These are all the analytics events the tool sends, with every property:

• tool-sql-json-query-builder-load-sample when you click Load sample (no properties).
• tool-sql-json-query-builder-pick-path when you pick a row in the path tree, with type: the JSON type of that row (object, array, string, number, boolean, null or mixed).
• tool-sql-json-query-builder-operation when you change the operation, with operation: its id, such as path_eq.
• tool-sql-json-query-builder-column-type when you switch a card's column type, with engine (mssql, pgsql, mysql or oracle) and storage (native or text).
• tool-sql-json-query-builder-copy and tool_sql_export when you copy a query: the first with engine, the second with tool (sql-json-query-builder), outcome (copy) and page (this page's path).

Clicking the download or documentation links on this page also records which link was clicked and where it sits on the page.

Limitations

• The builder writes SQL; it does not connect to a database or run anything. The path tree shows the paths in the one document you paste, not every shape your column holds.
• Equality and contains compare text on every engine, so for numbers the result depends on how the engine prints the stored number. For 89.90, SQL Server and PostgreSQL return 89.90 while MySQL, Oracle and SQLite return 89.9; for 1e2, PostgreSQL and Oracle return 100 and MySQL and SQLite 100.0. The cards point this out for the number in your sample. Use the numeric comparisons (>=, <=) for numbers.
• SQL Server's JSON_VALUE returns NULL for a string longer than 4,000 characters (lax mode); this was confirmed on SQL Server 2025 with a 5,000-character value.
• The desktop app's lookup operations (property lookup, any lookup, all lookup) emit the same SQL as equals, any element equals and every element equals, so they are not listed separately. Azure Data Explorer (KQL), which the app also filters, is not covered here.

Frequently asked questions

How do I query a JSON column in SQL?

Use the engine's path function on the column: JSON_VALUE on SQL Server and Oracle, #>> on PostgreSQL, JSON_UNQUOTE(JSON_EXTRACT()) on MySQL and json_extract on SQLite. Paste one of your column values into this builder, click the field you need, and it writes the SELECT or the WHERE clause for all five engines. The path is written as JSONPath ($.customer.address.city) everywhere except PostgreSQL's #>>, which takes a text array ({customer,address,city}).

What is the difference between ->> and #>> in PostgreSQL?

->> reads one key or array index and returns text, so a nested value needs a chain such as payload -> 'customer' -> 'address' ->> 'city'. #>> takes the whole path as a text array and returns text: payload #>> '{customer,address,city}'. The builder emits #>> because a path of keys and array indexes maps to that single operator; both return NULL when the path is missing.

Why does the SQL Server query include ISJSON?

When JSON is stored in an nvarchar column, a row whose text is not valid JSON makes JSON_VALUE and OPENJSON raise an error and stops the query. ISJSON(col) = 1 comes first in the WHERE clause to keep those rows out; SQL Server does not guarantee that it evaluates it first, but it did in the tests described on this page. The builder adds the matching guard on Oracle (IS JSON), MySQL (JSON_VALID) and SQLite (json_valid) when the column type is text, and leaves it out for native JSON columns, which cannot hold invalid JSON.

How do I filter on a value inside a JSON array?

Use a path with [*] and one of the any or every element operations, for example $.items[*].sku with any element equals. SQL Server walks the array with OPENJSON, PostgreSQL with jsonb_path_query, MySQL and Oracle with JSON_TABLE, and SQLite with json_each. The every element operations also require at least one element, so a row with an empty or missing array does not match.

Is my JSON uploaded anywhere?

No. The document is parsed by JavaScript in your browser and kept in memory; it is not sent to a server, not put in the URL and not saved to browser storage. Analytics record which button or option you used: loading the sample, the JSON type of a picked path, the chosen operation, a column type switch with its engine and the type picked, and a copy with its engine. Your JSON, paths, values, table and column names are never sent.

Does the generated SQL work on older database versions?

Mostly. On SQL Server, anything that uses OPENJSON (the array operations, has the property and the empty checks) needs database compatibility level 130 or higher, and each card says when its query uses OPENJSON. On MySQL the array operations use JSON_TABLE, which needs MySQL 8.0. Text columns on PostgreSQL use IS JSON, which needs PostgreSQL 16, the two case-insensitive equality operations use the jsonpath .lower() method of PostgreSQL 19, and the case-insensitive contains operations use LOWER() and LIKE, which work on every version. SQLite has its JSON functions built in from version 3.38.0; older builds need the JSON1 extension.