How to access upstream values in items downstream from MySQL or HTTP?

Questions
,
1

Describe the problem/error/question

I cannot understand how upstream values work when processing items. Please help!

It is VERY common for me to have a downstream module that needs to access some of the info from upstream. For example, after an API query or SQL query, I want to do something that depends on both the parameters that were fed to the query and the results of the query.

What is the problem?

The problem is that my intuition is failing me! This example has 6 parameters which each return 6 values, so we get 36 items. Downstream, I’d like to do something with each of those 36 results that relies on the original 6 parameters. But the linkage seems to get lost after the HTTP or SQL module. I’ve tried all kinds of complicated schemes to get around this. I’m not sure why this is hard. Shouldn’t upstream results just be “carried along”, or at least easily referenced?

Someone please help me understand what’s happening?

Please share your workflow

This example should run with any MySQL credential. If you don’t happen to have a MySQL database, the same query will probably work in other SQL modules and will probably have the same effect.

Share the output returned by the last node

The final Set module tries to pull upstream values. My intuition says that the values it sets for “From_Step_2_Splitout” and “From_Step_1” should be the same as the values in MySQL_Step_1 and MySQL_Step_2. But they’re always just the first values output by those upstream steps.

Why? How do I access the upstream values that generated the specific downstream values that I’m processing for each item?



[
  {
    "sql": "select 'A' as MySQL_Step_1, 'A-A' as MySQL_Step_2",
    "data": {
      "MySQL_Step_1": "A",
      "MySQL_Step_2": "A-A"
    },
    "From_Step_2_splitout": "A-A",
    "From_Step_1": "A"
  },
  {
    "sql": "select 'A' as MySQL_Step_1, 'A-B' as MySQL_Step_2",
    "data": {
      "MySQL_Step_1": "A",
      "MySQL_Step_2": "A-B"
    },
    "From_Step_2_splitout": "A-A",
    "From_Step_1": "A"
  },
{"..."}
  {
    "sql": "select 'B' as MySQL_Step_1, 'B-D' as MySQL_Step_2",
    "data": {
      "MySQL_Step_1": "B",
      "MySQL_Step_2": "B-D"
    },
    "From_Step_2_splitout": "A-A",
    "From_Step_1": "A"
  },
{"..."}
  {
    "sql": "select 'F' as MySQL_Step_1, 'F-F' as MySQL_Step_2",
    "data": {
      "MySQL_Step_1": "F",
      "MySQL_Step_2": "F-F"
    },
    "From_Step_2_splitout": "A-A",
    "From_Step_1": "A"
  }
  ]

Information on your n8n setup

  • n8n version: 0.236.2
  • Database (default: SQLite): MySQL
  • n8n EXECUTIONS_PROCESS setting (default: own, main): own
  • Running n8n via (Docker, npm, n8n cloud, desktop app): npm & pm2
  • Operating system: Ubuntu
3

My intuition says that the values it sets for “From_Step_2_Splitout” and “From_Step_1” should be the same as the values in MySQL_Step_1 and MySQL_Step_2.

Yep, I think your intuition very much makes sense here. The expression you are using in your From_Step_2_splitout field is {{ $('Split out Step 2').item.json.Step_2 }}. The .item expressions will attempt to identify whatever item on “Split out Step 2” is the source of the corresponding item on the current Set node. This usually works fine but fails for database nodes when using the execute query operation as n8n wouldn’t be able to interpret your custom query.

Without the MySQL node you’d actually see the expected result:

image
image1893×685 68.6 KB

So when using custom queries not changing the number or order of items, I’d suggest you use a pattern like below:

The Merge node would preserve the incoming data after the MySQL node in this workflow, and you can reference it afterwards in your expressions:

image
image1913×876 170 KB

Hope this helps!

4

I’m not sure I understand why that’s the case, especially when the SQL module is configured with “Independent” query batching and to “Always Output Data”. In that case, each “row” of input should be guaranteed to produce one or more rows of results, and it should always be possible to maintain the linkage.

Feature suggestion: Add an option to query batching called “One-to-Many” which ensures that the linkage with upstream data will be maintained, and avoids the hack of adding a merge module after every step.

I’m pretty sure I’ve seen the same loss of linkage with the HTTP module. It’d take me a little time to work up an example, but if you’re already aware that it’s possible to lose linkage in the HTTP module, then I’d make the same feature suggestion to add a configuration option which ensures that the upstream linkage is maintained and that there’s always a one-to-many relationship.

More broadly: this seems to be a situation where n8n “silently” fails to behave the way someone - at least a non-specialist like me - would expect. I’d like to make another feature suggestion: every module should have some visual queue about whether the upstream linkage is maintained or lost. I’m not sure exactly what that would look like, but at the very least something on the module configuration screen should indicate that upstream linkage is maintained/lost.

In addition, if upstream linkage is lost then the UI shouldn’t offer the upstream data as an option on the left side. That’s just setting people up for problems. And in the expressions fields, it should only offer the .all, .first, .last options, and should not offer the .item option because that won’t work as expected.

With a few UI changes like that, folks like me who are building at 2am will find it harder to think their systems are working only to discover (days weeks or months later) that the automations are misbehaving.

(I’m coming to N8N from Make. Developing in Make takes MUCH less time, because I’m rarely scratching my head trying to understand why it’s not behaving the way I expect. It almost always just works as expected. OTOH, actually running the automations in Make is a lot more expensive, which is why I’m getting up to speed on N8N.)