Last updated: 2026-05-20

JSON Columns

Filter, inspect, and navigate JSON and JSONB data across all five engines. Jam SQL Studio gives JSON columns a JSONPath-aware filter operator, an inline shape-peek, a collapsible tree / code cell viewer, and the ability to declare long-text columns as JSON or attach loose foreign keys to JSON properties — all without changing your schema.

What counts as a JSON column

Jam SQL Studio sorts every column into one of four buckets, which determines where the json filter operator appears:

• Native JSON — the engine's own JSON type (PostgreSQL json/jsonb, MySQL json, SQL Server json, Oracle json). The json operator is the default selection.
• Declared JSON — a column (native or long-text) that has a JSON declaration in MetaInfo. This is the metadata anchor for JSON-path descriptions and JSON-path loose FKs. The json operator is the default.
• Eligible long-text — a long-text column you could declare as JSON (per-engine eligibility below). The json operator is last in the dropdown, or first when the column name ends in json (case-insensitive: payload_json, userJSON).
• Not JSON — everything else; the json operator is hidden.

Declaring a native json / jsonb column does not change SQL emission — it only attaches user metadata (path descriptions, loose-FK targets). The engine already queries the column natively.

Declaring a text column & configuring a native column

When you pick the json operator on an eligible long-text column that hasn't been declared yet, Jam SQL Studio opens the "Declare Column as JSON?" dialog. It samples the loaded rows, shows how many look like JSON and the inferred shape, and — on confirm — writes a JSON declaration into the database's MetaInfo file (the same store that holds loose foreign keys). From then on the column is treated as JSON anywhere Jam SQL Studio renders it.

For a column that is already a native JSON type, the same dialog opens in "Configure JSON column" mode: there is nothing to convert, so it instead lets you persist JSON-path metadata and loose-foreign-key targets for that column. Either way the declaration is per-(connection, database) and roundtrips via MetaInfo import/export.

JSON Column Operators

For native JSON columns (PostgreSQL json/jsonb, MySQL json, SQL Server json, Oracle json) and long-text columns you've declared as JSON via the MetaInfo manager, Table Explorer adds a top-level json operator. Selecting it reveals a second dropdown with JSONPath-aware sub-operators that work uniformly across engines:

The *_lookup sub-operators combine path selection with a row picker against a related table — they're the discoverable way to declare and reuse a loose foreign key on a JSON property without leaving the json operator. The first time you use property lookup on a (column, path) pair, the picker opens in target-selection mode so you can declare the relationship; on every subsequent use, the picker opens directly in the row grid. The same JSON column can host more than one such loose FK — for example orders.metadata can independently declare $.customer_id → customers.id and $.warehouse_id → warehouses.id, and each filter resolves to the right declaration based on the path you've set. See the Loose Foreign Keys guide for the full lifecycle.

Engine-correct SQL is generated under the hood. Path syntax accepts $.address.city (JSONPath) or shorthand address.city — Jam SQL Studio normalizes either form. Rough engine equivalents for property = on path $.address.city:

Array wildcards ([*]). JSONPaths can include a single array wildcard — for example $.items[*].name matches against any element of an items array. The wildcard is supported by the existence and string sub-operators (has property, has not property, property =, property !=, property contains) and by the aggregate sub-operators below. Under the hood Jam SQL Studio splits the path at the first [*] and emits a per-row lateral walk: jsonb_path_query on PostgreSQL, JSON_TABLE on MySQL/Oracle, OPENJSON on SQL Server, json_each on SQLite. Filter expressions (?(@...)) are not yet supported.

Aggregate sub-operators (any_* / all_*). Aggregate predicates walk every match of a wildcard path and ask whether any element satisfies a comparison (any =, any contains, any >, any ≥, any <, any ≤) or every element satisfies it (all =, all >, all ≥, all <, all ≤). For example any > 100 on path $.items[*].qty matches rows where at least one item's qty exceeds 100; all ≥ 0 on the same path matches rows whose items all have a non-negative qty. all_* requires at least one matched element — a row whose array is missing or empty does not match. Numeric variants cast each matched value via the engine's numeric cast (::numeric on PostgreSQL, CAST(... AS DECIMAL) on MySQL, TRY_CAST on SQL Server, TO_NUMBER on Oracle, CAST(... AS REAL) on SQLite); on PostgreSQL a non-numeric element will raise invalid input syntax for type numeric — combine with a path filter when your arrays are mixed-type. All five engines support aggregates.

SQL Server: compatibility level 130+ required for wildcard and aggregate filters. Anything that emits OPENJSON — every wildcard ([*]) path, every any_* / all_* aggregate, and is empty / is not empty — needs the target database's compatibility level to be at least 130 (SQL Server 2016). On lower levels (e.g. 100 on a legacy database hosted by an otherwise modern SQL Server 2019/2022), SQL Server's binder can't resolve OPENJSON and reports Invalid object name 'OPENJSON'. Jam SQL Studio detects the compatibility level when you open the filter and surfaces an inline warning in the sub-operator dropdown ("Array filters need compat 130+.") with a Recheck link that refreshes the cached level after a DBA bumps it. The sub-operators stay selectable — if you do pick one, the runtime error is rewritten to include an actionable suggestion. Scalar (non-wildcard) operators — property =, property !=, property contains, has property, is valid JSON — work at any compatibility level because they emit JSON_VALUE / ISJSON instead of OPENJSON. To unlock wildcard / aggregate filters, a DBA can run ALTER DATABASE [<your-db>] SET COMPATIBILITY_LEVEL = 130; (optionally paired with ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; to keep the legacy query planner).

Recursive descent (..). JSONPaths can use .. to match a property at any depth — for example $..name matches every name key anywhere in the document. Engine support is uneven, so Jam SQL Studio surfaces an inline hint in the filter chip when the active engine can't lower the path:

On engines without native support, the filter chip shows "Recursive descent (..) not supported on this engine" next to the path input as soon as you type ... Like the wildcard, recursive descent is supported only by the existence and string sub-operators (has property, has not property, property =, property !=, property contains).

Operator placement. On native JSON columns, json is the default selection. On long-text columns whose name ends in json (case-insensitive — payload_json, metadataJson, …), json is offered first in the dropdown but isn't auto-selected. On other long-text columns it sits at the bottom of the list — a discoverable opt-in.

Declaring a text column as JSON. When you pick the json operator on a long-text column that hasn't been declared yet, Table Explorer asks "Declare this column as JSON?" Confirming saves a declaration to the database's MetaInfo file (the same store that holds loose foreign keys). From then on, the column is treated as JSON anywhere Jam SQL Studio renders it. On engines that need it, declared-on-text predicates are automatically wrapped with the engine's JSON-validity guard so rows holding non-JSON text are filtered out instead of producing engine errors:

Peek the JSON shape. Next to the JSONPath input is an eye-icon button that opens a path-discovery popover. The popover lists every property path Jam SQL Studio has observed for that column — sourced first from a cached structure scan (refreshed within the last 7 days) and otherwise from values rendered in the current grid session. Click any path to paste it into the filter chip. If the loaded page didn't surface a path you need, click Scan 1000 from server to run a fresh sample query (with the engine-appropriate validity guard for declared-on-text columns) and feed the results into the cache. The cache is shared with future sessions and the rest of Jam SQL Studio, but only stores skeleton path/type/prevalence — never sample values — so exporting MetaInfo never leaks user data.

Reading and editing JSON cells

JSON cells render as syntax-coloured single-line strings with an expand button. Expanding opens a dialog with two modes: an in-house collapsible, keyboard-navigable JSON tree, and a Monaco code editor with JSON syntax highlighting and validation. You can edit JSON in place from either mode; saving minifies the value before writing. A column declared on text whose value fails to parse falls back to plain text with a small warning glyph.

Column header glyphs — three states

Result-grid headers summarise the JSON state of each column with one glyph:

• Blue {} — declared. The column has a JSON declaration in MetaInfo. Click to open the JSON details inspector (path list, refresh, undeclare). Native columns show an informational note.
• Gray {} — undeclared native. A native JSON type with no declaration row yet. Click to open the "Configure JSON column" dialog and add one.
• Gray {}? — eligible long-text. A long-text column whose sampled rows look like JSON. Click to open the "Declare Column as JSON?" dialog.

Loose foreign keys on JSON paths

A loose foreign key's source side can be a JSONPath inside a JSON column — for example orders.metadata $.customer_id → customers.id. A single JSON column can host several, one per path. See the Loose Foreign Keys guide for the full lifecycle, the property_lookup / any_lookup / all_lookup sub-operators, and the multi-FK picker.

Enums on JSON properties

If a property inside a JSON column always comes from a known set of values — a status, a kind, a list of tags — you can declare just that path as an enum and get a searchable dropdown in the filter chip. The peek popover and the JSON structure details dialog both surface inline glyphs next to each property name: a blue ▾ for already-declared paths and a muted ▾? on paths whose name hints at an enum. The structure details dialog also has a "Declare property as enum…" link that lets you mark any property as an enum, including ones whose name doesn't hint at one. See the Enum Columns guide for the three sub-operators (property_enum, any_enum, all_enum) and the full declare flow.

Engine-specific JSON guides

Deep dives into how each engine stores and queries JSON, and how Jam SQL Studio works with it:

• PostgreSQL JSON & JSONB — the complete guide to operators, indexing and query patterns
• MySQL JSON columns — the native JSON type, functions and path syntax
• SQL Server JSON support — JSON_VALUE, OPENJSON and compatibility levels
• Oracle JSON support — the 21c JSON type and JSON_VALUE / JSON_TABLE
• SQLite JSON support — the JSON1 functions and TEXT-affinity storage
• JSON in relational databases compared — how the five engines differ

Related topics

• Table Explorer — the primary surface for filtering and browsing JSON data
• Loose Foreign Keys — including JSON-path FKs and MetaInfo import/export
• Enum Columns — including JSON-path enums (property_enum / any_enum / all_enum)
• Query Editor — JSON cells and header glyphs work the same in query results