--- title: "SQL JSON Query Builder" description: "Paste a JSON column value, click a field and get the SELECT or WHERE clause for SQL Server, PostgreSQL, MySQL, Oracle and SQLite in your browser." url: "https://jamsql.com/tools/sql-json-query-builder/" html_url: "https://jamsql.com/tools/sql-json-query-builder/" generated: "2026-09-24T15:18:13.025Z" --- # SQL JSON Query Builder Paste one value from a JSON column, click a field in the path tree, and get the SQL that extracts it — or filters rows on it — for SQL Server, PostgreSQL, MySQL, Oracle and SQLite side by side, with the value your sample holds at that path. The SQL comes from the JSON filter code of the Jam SQL Studio desktop app, and your document never leaves the browser. Load sample Clear One JSON document, up to 1,048,576 characters. It is parsed in your browser. JSON document (one column value) Paths in the document Click a path, or Tab into the tree and use the arrow keys and Enter. Schema (optional) Table JSON column Path Operation Value Values, one per line In your sample ## Filter JSON columns on a live connection in Jam In Jam SQL Studio's Table Explorer, the `json` filter operator writes this same SQL when you pick a path and a sub-operator, lists the paths it has seen in the column in a peek popover, and adds the validity guard for text columns you declare as JSON. [Get Jam SQL Studio — free](/#download) [JSON column filters](/docs/json-columns/) With JavaScript on, the path, operation and the SQL for the five engines appear here. ## 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(…))`). Functions and operators emitted per engine. `col` is your JSON column; the text-column guard is added only when the column type is text. Engine Extract and compare Has the property Array elements (\[\*\]) Text-column guard Numbers SQL Server `JSON_VALUE(col, '$.a.b')` `EXISTS (SELECT 1 FROM OPENJSON(col, '$.a') WHERE [key] = 'b')` `OPENJSON(col, '$.items')` with `JSON_VALUE(j.[value], '$.sku')` `ISJSON(col) = 1` `TRY_CAST(… AS DECIMAL(38,10))` PostgreSQL `(col)::jsonb #>> '{a,b}'` `(col)::jsonb #> '{a,b}' IS NOT NULL` `jsonb_path_query((col)::jsonb, '$.items[*].sku')`, `jsonb_path_exists` `CASE WHEN col IS JSON THEN (col)::jsonb END` `::numeric` MySQL `JSON_UNQUOTE(JSON_EXTRACT(col, '$.a.b'))` `JSON_CONTAINS_PATH(col, 'one', '$.a.b')` `JSON_TABLE(col, '$.items[*]' COLUMNS (v VARCHAR(4000) PATH '$.sku'))` in `(SELECT COUNT(*) …)` `JSON_VALID(col) = 1` `CAST(… AS DECIMAL(38,10))` Oracle `JSON_VALUE(col, '$.a.b')` `JSON_EXISTS(col, '$.a.b')` `JSON_TABLE(col, '$.items[*]' COLUMNS (v VARCHAR2(4000) PATH '$.sku'))` `col IS JSON` `TO_NUMBER(…)` SQLite `CAST(json_extract(col, '$.a.b') AS TEXT)` `CAST(json_extract(…) AS TEXT) IS NOT NULL` `json_each(col, '$.items')` with `json_extract(je.value, '$.sku')` `json_valid(col) = 1` `CAST(… AS REAL)` 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](/blog/postgresql-json-jsonb-complete-guide/), the [SQL Server JSON guide](/blog/sql-server-json-support-guide/), the [MySQL JSON column guide](/blog/mysql-json-column-guide/), the [Oracle JSON guide](/blog/oracle-json-support-guide/), the [SQLite JSON guide](/blog/sqlite-json-support-guide/) and [JSON in relational databases compared](/blog/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. ## Filter JSON columns on a live connection in Jam Jam SQL Studio's Table Explorer has a `json` filter operator that writes the same SQL as this page on SQL Server, PostgreSQL, MySQL, Oracle and SQLite. A peek popover lists the paths observed in the column with their prevalence, text columns can be declared as JSON so the validity guard is added automatically, and JSON cells open in a collapsible tree or code view. [Get Jam SQL Studio — free](/#download) [JSON column filters](/docs/json-columns/) ## 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. ## Filter JSON columns by path in Jam SQL Studio Jam SQL Studio is a desktop SQL client for SQL Server, PostgreSQL, MySQL, Oracle and SQLite. Its Table Explorer filters JSON columns by path with this same SQL, lists the paths it has observed in each column, and opens JSON cells as a collapsible tree. [Download Free](/#download) [JSON Columns Docs](/docs/json-columns/) Free for personal use • No account required • Mac, Windows, Linux ## More free SQL tools [ ### CSV to JSON Converter Paste or upload CSV and get a pretty-printed JSON array or NDJSON, with numbers and booleans inferred per column. ](/tools/csv-to-json/)[ ### SQL Formatter Format and beautify SQL with dialect-aware rules for T-SQL, PostgreSQL, MySQL, PL/SQL, and SQLite. ](/tools/sql-formatter/)[ ### SQL Data Type Converter Map data types across SQL Server, PostgreSQL, MySQL, Oracle, and SQLite — with precision and pitfall notes. ](/tools/sql-data-type-converter/)