JSONB query builder
The JSONB query builder is a visual constructor for searching jsonb columns. It assembles correct SQL with ->, ->>, @>, ?, jsonb_path_query operators without making you remember them.
Open
- In Schema Browser: right-click a jsonb column → Build query….
- In the JSONB editor: the Find similar button in the header.
- From the palette: ⌘+K →
Build JSONB query.
Looks like a form
At the top — pick the table and column:
FROM users.profile_data
Then a list of conditions with + Add filter.
Condition types
Path equals
"Key at this path has this value":
profile_data → 'name' = 'Alice'
Generates:
WHERE profile_data->>'name' = 'Alice'
Path exists
"This key exists in the object":
profile_data has key 'phone_verified'
→
WHERE profile_data ? 'phone_verified'
Path contains
"Object contains this sub-object" (uses @>):
profile_data contains {"role": "admin", "active": true}
→
WHERE profile_data @> '{"role":"admin","active":true}'::jsonb
The most common and fastest type with a GIN index.
Array element equals
"The array contains this element":
profile_data → 'tags' has element 'premium'
→
WHERE profile_data->'tags' ? 'premium'
Numeric comparison
"Value at path compared with a number":
profile_data → 'age' > 18
→
WHERE (profile_data->>'age')::int > 18
ide99 casts to the right type automatically. If the data isn't numeric — Postgres returns an error and ide99 shows a clear message.
JSON path query
For complex paths with conditions, jsonb_path_query is used:
profile_data path: $.addresses[*] ? (@.country == "DE")
→
WHERE jsonb_path_exists(
profile_data,
'$.addresses[*] ? (@.country == "DE")'
)
Logic between conditions
At the top — an AND / OR switch. Group via + Group (creates a nested block with its own AND/OR).
SQL preview
At the bottom — assembled query:
SELECT id, profile_data
FROM users
WHERE profile_data @> '{"role":"admin"}'::jsonb
AND (profile_data->>'age')::int > 18
ORDER BY id
LIMIT 100;
Buttons:
- Copy — to clipboard, paste anywhere
- Open in editor — opens a new SQL editor tab with this query
- Run — execute and open the result
Autocomplete hints
When typing a path (profile_data → ...), ide99 suggests keys actually present in this column (if jsonb schema scanning isn't disabled). Big speedup — you don't memorize the structure.
Tip: what to use first
Of all condition types, @> (path contains) is the most useful by default:
- Simple syntax
- Uses a GIN index (
CREATE INDEX ... USING gin (col jsonb_path_ops)) for fast lookups
- Works at any depth of nesting
If something doesn't show up — verify with EXPLAIN that the index is used. On big tables without a GIN index, any jsonb search becomes a Seq Scan.
Next