Error while querying with JMESPath

Questions
, ,
1

Hello everyone! I’m having a problem while iterating through a JSON to search if a student has passed a test, and retrieving his informations to update his pontuation.

I have his info on a Google Sheets, who I import his basic informations (ID, name, email, etc), a boolean for each test he has passed (test_01, test_02, etc), and a pontuation interger, who has to be updated accordingly to which tests he had passed.

The big problem is, whenever I run the code to iterate over the students and see who passed and retrieve the data, it returns me all the students data, and it doesn’t actually calculates the pontuation.

Here’s my workflow

The output I get:

[
  {
    "ato0Concluido": [
      [
        [
          {
            "ID_cademi": 19637349,
            "nome": "Isis Gonçalves Ferreira",
            "email": "[email protected]",
            "cpf": "484.699.668-98",
            "celular": "11968398010",
            "criado_em_cademi": "2024-09-24 22:13:39",
            "prova_ato_0": true,
            "prova_ato_1": true,
            "prova_ato_2": true,
            "prova_ato_3": false,
            "prova_ato_4": false,
            "prova_ato_5": false,
            "prova_ato_6": false,
            "prova_ato_7": false,
            "curso_bonus": false,
            "pontuacao": 0
          },
          {
            "item": 0
          }
        ]
      ],
      [
        [
          {
            "ID_cademi": 19637680,
            "nome": "Maria Madalena Ferreira ",
            "email": "[email protected]",
            "cpf": "393.100.519-49",
            "celular": "15997626988",
            "criado_em_cademi": "2024-09-24 22:23:41",
            "prova_ato_0": true,
            "prova_ato_1": false,
            "prova_ato_2": false,
            "prova_ato_3": false,
            "prova_ato_4": false,
            "prova_ato_5": false,
            "prova_ato_6": false,
            "prova_ato_7": false,
            "curso_bonus": false,
            "pontuacao": 0
          },
          {
            "item": 1
          }
        ]
      ],
      [
        [
          {
            "ID_cademi": 19637356,
            "nome": "Cinara Soares",
            "email": "[email protected]",
            "cpf": "148.931.228-51",
            "celular": "11997444455",
            "criado_em_cademi": "2024-09-24 22:13:39",
            "prova_ato_0": false,
            "prova_ato_1": false,
            "prova_ato_2": false,
            "prova_ato_3": false,
            "prova_ato_4": false,
            "prova_ato_5": false,
            "prova_ato_6": false,
            "prova_ato_7": false,
            "curso_bonus": false,
            "pontuacao": 0
          },
          {
            "item": 2
          }
        ]
      ],
      [
        [
          {
            "ID_cademi": 20331696,
            "nome": "Isabella Fernanda Fonseca Cabrera",
            "email": "[email protected]",
            "cpf": "",
            "celular": "5561991894556",
            "criado_em_cademi": "2024-10-25T09:41:03.936-03:00",
            "prova_ato_0": false,
            "prova_ato_1": false,
            "prova_ato_2": true,
            "prova_ato_3": false,
            "prova_ato_4": false,
            "prova_ato_5": false,
            "prova_ato_6": false,
            "prova_ato_7": false,
            "curso_bonus": false,
            "pontuacao": 0
          },
          {
            "item": 3
          }
        ]
      ]
    ]
  }
]

The output I want:

[
  {
    "ato0Concluido": [
      [
        [
          {
            "ID_cademi": 19637349,
            "nome": "Isis Gonçalves Ferreira",
            "email": "[email protected]",
            "cpf": "484.699.668-98",
            "celular": "11968398010",
            "criado_em_cademi": "2024-09-24 22:13:39",
            "prova_ato_0": true,
            "prova_ato_1": true,
            "prova_ato_2": true,
            "prova_ato_3": false,
            "prova_ato_4": false,
            "prova_ato_5": false,
            "prova_ato_6": false,
            "prova_ato_7": false,
            "curso_bonus": false,
            "pontuacao": 110
          },
          {
            "item": 0
          }
        ]
      ],
      [
        [
          {
            "ID_cademi": 19637680,
            "nome": "Maria Madalena Ferreira ",
            "email": "[email protected]",
            "cpf": "393.100.519-49",
            "celular": "15997626988",
            "criado_em_cademi": "2024-09-24 22:23:41",
            "prova_ato_0": true,
            "prova_ato_1": false,
            "prova_ato_2": false,
            "prova_ato_3": false,
            "prova_ato_4": false,
            "prova_ato_5": false,
            "prova_ato_6": false,
            "prova_ato_7": false,
            "curso_bonus": false,
            "pontuacao": 110
          },
          {
            "item": 1
          }
        ]
      ]
    ]
  }
]

Has anyone been through this, or something similar? Can gimme any advices?

Sorry for any english mistakes, it’s not my first language.

Information on my n8n setup

  • n8n version: 1.63.4
  • Database: A Google Sheets
  • Running n8n via: Docker on a desktop app.
  • Operating system: Win11
2

It looks like your topic is missing some important information. Could you provide the following if applicable.

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

Welcome to the community @Marcelo_LikeMkt !

Tip for sharing information

Pasting your n8n workflow

Ensure to copy your n8n workflow and paste it in the code block, that is in between the pairs of triple backticks, which also could be achieved by clicking </> (preformatted text) in the editor and pasting in your workflow.

```
<your workflow>
```

That implies to any JSON output you would like to share with us.

Make sure that you have removed any sensitive information from your workflow and include dummy or pinned data with it!

You need to bear in mind that the actual data structure is as in Data structure | n8n Docs. That is, you are missing json - the parent property (which is never displayed in the output).

To get the result you depicted, you need to change the code in the last node to

ato0Concluido = $jmespath($input.all(), "[?json.prova_ato_0].[*]");

return { ato0Concluido };

Note json in [?json.prova_ato_0].

Though, this strructurre of the data is wierd. Not sure why you need it so deeply nested in arrays if you could simply use Filter node to retain only the item where prova_ato_0 == true since you check prova_ato_0 only.