How to Query JSON with SQL in Retool and Filter Nested Fields

If you're trying to query JSON with SQL in Retool and filter on a nested field like creator.name, you've probably already hit the same wall dozens of builders hit: the dot notation doesn't work. This post walks you through the exact fix, plus how to display your filtered JSON results cleanly inside your Retool app.

Why Dot Notation Fails in Retool's Query JSON with SQL

Retool's Query JSON with SQL query type is powered by an open-source library called AlaSQL. AlaSQL is great for running SQL-style queries directly against in-memory JSON data, but it uses a different syntax for accessing nested object properties. Instead of the standard dot notation you might expect (e.g. creator.name), AlaSQL uses the arrow operator: creator->name.

This small syntax difference is the root cause of almost every "Query JSON with SQL not working" issue you'll find in the Retool community. Once you know it, the fix takes five seconds.

How to Filter Nested JSON Fields Using AlaSQL Arrow Syntax

Let's say you have a REST query named answers that returns a JSON array from an API — in this case, a list of Basecamp thread replies. Each record looks something like this:

• id: a unique numeric identifier
• content: the HTML body of the answer
• creator: a nested object containing name, id, and role

To create a Query JSON with SQL query that filters only answers where the creator's name is "Jeremy", your SQL should look like this:

SELECT * FROM {{ answers.data }} WHERE creator->name = 'Jeremy'

Notice two things: the double curly braces reference your REST query's data directly as the table source, and the nested field creator.name is accessed with creator->name using the arrow operator. Single quotes are required around string values in AlaSQL, just like standard SQL.

Step-by-Step: Setting Up the Query JSON with SQL Query in Retool

• Step 1: Make sure your REST query (e.g. answers) is already created and returning data successfully. Verify it by checking answers.data in the query panel.
• Step 2: Create a new query. Set the query type to Query JSON with SQL.
• Step 3: In the SQL editor, write your query using the AlaSQL arrow syntax for any nested fields: SELECT * FROM {{ answers.data }} WHERE creator->name = 'Jeremy'
• Step 4: Run the query. You should now see only the records where creator.name equals "Jeremy".
• Step 5: Bind the results to your component using the new query's name, e.g. {{ filteredAnswers.data }}.

Displaying Filtered JSON Results Without a Table Component

Your first instinct might be to dump the filtered results into a Table component. That works, but if you only need to surface a handful of fields — like id, content, and creator.name — a table is overkill and harder to style. A better approach is to use Text components nested inside a Container component.

Here's how to reference individual fields from your query results inside a Text component:

• Creator name: {{ filteredAnswers.data[0].creator.name }}
• Content: {{ filteredAnswers.data[0].content }}
• Answer ID: {{ filteredAnswers.data[0].id }}

Wrap those Text components in a Container and give the Container a title to create a clean, card-style layout. If you need to iterate over multiple results, a Listview component is the right tool — it lets you repeat a component template for each record in your dataset without manually wiring up indexes.

Triggering Your REST Query: Buttons vs. Automatic Refresh

One question that comes up often for new Retool builders: does your REST query automatically re-run when new data is posted to your external system (like Basecamp or any other API)? The short answer is no, not by default.

Retool does not natively support inbound webhooks. External systems cannot push data into a running Retool app to trigger a query. The most practical patterns for keeping data fresh are:

• Attach a Button component and set its click event to run your query. Simple and explicit — the user controls when data refreshes.
• Enable automatic query re-runs on a timer using the query's "Run on a schedule" or "Run on page load" settings in the query editor.
• Use the Run on input change option if your query depends on a variable that changes in the UI.

If you need a true event-driven trigger from an outside service, you'd currently need an intermediary like Zapier or a custom webhook relay to bridge the gap — though this is a commonly requested feature in the Retool community.

Quick Reference: AlaSQL Nested Field Syntax

• Accessing a nested property: creator->name (not creator.name)
• Filtering on a nested property: WHERE creator->name = 'Jeremy'
• Selecting a specific nested field: SELECT creator->name AS creatorName FROM {{ query.data }}
• Multiple nested levels: creator->address->city

The arrow operator works at any depth, so deeply nested objects are no problem once you know the pattern. Keep this syntax in your back pocket — it's the single most common stumbling block when using Query JSON with SQL in Retool, and knowing it will save you a frustrating hour of debugging.