Does `Defined automatically byt he model` can handle data in nested structure column?

Questions
1

Describe the problem/error/question

Hi, I use Postgres tool node in my AI agent and the data inside the database table’s column is nested structure. In the Postgres tool node settings I use Tool Description as Set Automatically, Return All as Defined automatically by the model, for Select Rows for each column that I want to search I use Defined automatically by the model including the nested structure column. My question is does Defined automatically by the model can still works when the data is nested?

Please share your workflow

Information on your n8n setup

  • Database (default: SQLite): Postgres
  • n8n EXECUTIONS_PROCESS setting (default: own, main): main
  • Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
  • Operating system: Windows 11
2

My take… “Defined automatically by the model” setting will work (the LLM will provide a value), but the “Select” operation will likely fail to find your data.

When you use the Select operation in the Postgres Tool node, n8n generates a standard SQL query. If your column is event_understanding and the model provides a value like "Positive", the resulting SQL looks like this:

SELECT * FROM news_articles WHERE event_understanding = 'Positive';

If event_understanding is a nested structure (JSONB/JSON), Postgres will look for a row where the entire column is exactly the string 'Positive'. It will not look inside the JSON object for a key that equals “Positive”. To search inside nested data, Postgres requires specific operators (like ->>, @>, or jsonb_extract_path), which the standard “Select” operation does not use.

The “Defined automatically” setting only tells the LLM: “You decide what value to put in this box.”

  • The LLM might be smart enough to know the data is nested and try to pass a JSON string.
  • However, the Postgres Tool node still treats that value as a single string to be compared against the whole column using the = operator.
  • Unless the LLM provides the exact, complete JSON object stored in that cell, the query will return no results.

To properly query nested structures, you should change the Operation in your Postgres Tool node from Select to executeQuery.

When you use executeQuery, you aren’t limited to the “Select Rows” table. Instead, the AI Agent can write the actual SQL query itself. A capable model (like Gemini or Gemma) knows how to handle Postgres JSONB syntax. It will generate a query like:

-- Example of what the AI will generate using executeQuery
SELECT * FROM news_articles WHERE event_understanding->>'sentiment' = 'Positive';

Does that help, @ezraluandre ?

3

I understand that it only select Positive data in event_understanding and if let’s say those data is located nested object inside event_understanding it will return nothing.

You said that executeQuery doesn’t act like “Select Rows” and I believe it can handle nested object data but I need the tool node capability to Define automatically by the model so hardcoded string isn’t really working, is there a way to have that capability in executeQuery?

4

In the executeQuery operation, you can set the entire SQL query to be defined automatically by the model. This allows the AI to decide not just the value, but also the JSON path (the nested key) it needs to look into.

You can do it this way

  1. Change Operation​: Set the operation to executeQuery.

  2. Set Query to AI​: In the Query field, click the expression icon (the gear or the {{ }}) and use the $fromAI expression. It should look like this: ={{ $fromAI('sql_query', 'The full SQL query to execute', 'string') }}

  3. Update Tool Description (Crucial Step): Since the AI is now writing the whole query, you must tell it exactly how your data is structured in the Tool Description​. If you don’t, it might guess the wrong column names or JSON keys.

    Example Tool Description to use:

    “Use this tool to retrieve news articles. The table is ‘news_articles’. Note: The columns ‘event_understanding’ and ‘institutional_understanding’ are JSONB structures. To filter by nested values, use the Postgres JSONB operator ‘->>’. For example, to find positive sentiment, use: SELECT * FROM news_articles WHERE event_understanding->>‘sentiment’ = ‘Positive’;”

5

I tired to use this syntax and it works for me but when I tried to incorporate it I found that I should tell agent to create SQL query that will be pass onto the Postgres node, is this correct or I just create unnecessary instruction?

7

You have to trial and error.
This can be model dependent as well.

Try to remove the instruction from your Agent’s main prompt to save tokens and reduce “noise.” Just ensure your Tool Description contains the technical details about your JSONB columns

Does it work?

This should give you a clearer picture

image
image753×220 3.68 KB

8

Yes, that is correct, but I would keep the responsibility clearly separated.

The Agent needs to understand when it should use the Postgres tool and what information it is trying to retrieve.

The Postgres tool description needs to explain how the database is structured and how the SQL must be written.

So I would not put the full SQL-generation instructions inside the Agent’s main system prompt unless the model is consistently failing to use the tool correctly. That creates duplicate instructions, uses more tokens and can sometimes make the Agent less predictable.

Inside the Postgres Query field you can use:

{{ $fromAI(
  'sql_query',
  'Generate the complete read-only PostgreSQL query required to answer the user request.',
  'string'
) }}

Then put the database-specific information inside the tool description, for example:

Use this tool to retrieve data from the news_articles table.

The event_understanding and institutional_understanding columns use the JSONB data type.

Use PostgreSQL JSONB operators when accessing nested properties.

For a top-level text value, use:
event_understanding-\">>'sentiment'

For deeper nested values, use:
event_understanding-\">>'analysis'-\">>'sentiment'

Only generate SELECT queries.
Never generate INSERT, UPDATE, DELETE, DROP, ALTER or TRUNCATE queries.
Use only the tables and columns described here.

One important distinction is that Define automatically by the model does not magically inspect or understand your nested JSONB schema.

It can generate a query that accesses nested values, but only when the model knows:

  • the table name,
  • the JSONB column name,
  • the available nested keys,
  • their expected value types,
  • and preferably one or two valid query examples.

Without that information, the model may invent keys or produce SQL that is syntactically valid but does not match your actual data.

Also, be careful with the operator being used:

json_column-\">>'nested_object'

returns JSON or JSONB, while:

json_column-\">>'nested_value'

returns the value as text.

For example:

SELECT *
FROM news_articles
WHERE event_understanding-\">>'sentiment' = 'Positive';

For a deeper structure:

SELECT *
FROM news_articles
WHERE event_understanding-\">>'classification'-\">>'sentiment' = 'Positive';

So your additional Agent instruction was not necessarily wrong, but it is probably unnecessary if the Agent already understands that it should call the Postgres tool.

I would keep the main Agent prompt focused on behaviour and put the SQL schema, nested JSON paths and restrictions inside the Postgres tool description.

And because the model is generating the complete query, I would strongly restrict the database credentials to read-only access. Prompt instructions are useful, but they are not a security boundary.

9

Thanks everyone I understand now, after a workflow run the agent is working correctly, but I found that the agent doesn’t always run Postgres tool node. In my agent there’s a section on how to outputting the query that will be used in Postgres tool node, the snippet is like this:

# PART 3 — REPOSITORY RETRIEVAL EXECUTION

The Repository Retrieval Tool is mandatory.

It is not an optional information source.

It is the only authoritative source of historical institutional evidence stored within the database repository.

Before constructing any historical section, you MUST execute the Repository Retrieval Tool.

This requirement applies to every workflow execution.

---

## Repository Retrieval Process

Step 1

Identify the institutional concepts requiring historical evidence from:

• Institutional Adaptation
• Adaptation Objective
• Operational Capability
• Operational Transformation

Step 2

Generate one or more repository searches targeting:

1. Previous adaptations by the same institution.

2. Similar institutional adaptations by different institutions.

3. Similar operational capabilities.

4. Relevant regulatory or geographic history.

Step 3

Invoke the Repository Retrieval Tool.

Do not skip this step.

Do not substitute your own knowledge.

Do not assume historical context without retrieval.

---

# SQL GENERATION REQUIREMENTS

The Repository Retrieval Tool executes PostgreSQL queries against the database repository.

When generating SQL, you MUST follow all of the following rules.

## Rule 1 — PostgreSQL Only

Generate PostgreSQL-compatible SQL only.

---

## Rule 2 — Repository Scope

Only query:

institutional_onchain_adoption.news_articles

Never invent table names.

Never invent column names.

---

## Rule 3 — Searchable Columns

Only perform text searches against these JSONB columns:

• event_understanding
• institutional_understanding

No other columns may be searched.

---

## Rule 4 — JSONB Text Search

Both searchable columns are JSONB values.

Before performing any text comparison, you MUST cast the JSONB column to text.

Correct:

event_understanding::text

institutional_understanding::text

Incorrect:

event_understanding

institutional_understanding

---

## Rule 5 — Case-Insensitive Matching (MANDATORY)

Every text comparison MUST use ILIKE.

LIKE is NEVER permitted.

Do not generate LIKE under any circumstance.

Correct:

event_understanding::text ILIKE '%Turkey%'

institutional_understanding::text ILIKE '%tokenization%'

Incorrect:

event_understanding::text LIKE '%Turkey%'

institutional_understanding::text LIKE '%tokenization%'

event_understanding LIKE '%Turkey%'

institutional_understanding LIKE '%tokenization%'

---

## Rule 6 — Multiple Search Terms

When searching multiple concepts, combine them using OR.

Example:

SELECT *
FROM institutional_onchain_adoption.news_articles
WHERE
    event_understanding::text ILIKE '%Turkey%'
    OR event_understanding::text ILIKE '%Inveo%'
    OR event_understanding::text ILIKE '%Moca Network%'
    OR event_understanding::text ILIKE '%Animoca Brands%'
    OR institutional_understanding::text ILIKE '%Turkey%'
    OR institutional_understanding::text ILIKE '%Inveo%'
    OR institutional_understanding::text ILIKE '%Moca Network%'
    OR institutional_understanding::text ILIKE '%Animoca Brands%';

This is the expected SQL format.

---

## Rule 7 — SQL Validation Before Tool Invocation

Before invoking the Repository Retrieval Tool, verify that the generated SQL satisfies ALL of the following:

✓ PostgreSQL syntax

✓ Correct table name

✓ Only approved columns

✓ Every JSONB column cast using ::text

✓ Every text comparison uses ILIKE

✓ No LIKE statements exist anywhere in the query

If any LIKE statement exists, rewrite the SQL before calling the Repository Retrieval Tool.

---

## Repository Retrieval Authority

Repository evidence may only originate from successful execution of the Repository Retrieval Tool.

If repository retrieval returns relevant evidence:

• Preserve it faithfully.

If repository retrieval returns no relevant evidence:

• Preserve empty evidence sections.

• Explain the coverage limitation.

• Populate Remaining Historical Gaps where appropriate.

Do not fabricate historical evidence under any circumstance.

and this is tool description of Postgres tool node:

Use this tool to retrieve historical evidence from the database repository.

This is the only source of repository evidence.

Call this tool exactly once for every workflow after identifying retrieval targets.

Repository table:

institutional_onchain_adoption.news_articles

Searchable columns (JSONB):

- event_understanding
- institutional_understanding

Search only within these two columns.

The returned rows are the only valid source for:
- Historical Summary
- Institutional History
- Industry Precedent
- Regulatory History
- Technology Evolution
- Geographic Context
- Contradictory Evidence
- Repository References

If the tool returns no rows, use empty arrays and populate Remaining Historical Gaps.

Do not use your own knowledge to populate these fields.

Prioritize searches based on:

1. Previous institutional adaptations by the same institution.
2. Comparable institutional adaptations by other institutions.
3. Similar operational capabilities.
4. Relevant regulatory or geographic history.

Do not retrieve articles simply because they mention the same company.

Retrieve evidence based primarily on institutional adaptation, operational capability, operational transformation, and historical context.

After more than 5 runs, agent only searches for database using Postgres tool node only twice, does my prompt or tool description is too complex?