Mongodb query with array item

Questions
1

Hi

I am trying to do a query for a dataset where the data is in an array item. But I only get

ERROR: Unexpected token $ in JSON at position 6

query

{
  "assetLocation.voyageId" : "VOY-1014" ,
  "contextData.voyagedata" : {
    $elemMatch : {
      "id" : "time_departure_estimated" 
    }
  }
}

then I would also like to limit the result by sorting on eventData.timeStampUTC and limit the result to only the latest.

dataset:

{ 
    "_id" : ObjectId("5e95bced12d1d56174f51259"), 
    "eventId" : "sdfsdfsdfsdfdf23sdf", 
    "assetLocation" : {
        "voyageId" : "VOY-1014", 
        },
    "eventData" : {
        "timeStampUTC" : "2020-03-27T10:55:08.5010000Z", 
        "timeZoneUTC" : "+1", 
        "geoTimeZone" : "", 
        "action" : "Update", 
        "version" : "0", 
        "rev" : "1", 
        "userdata" : {
            "createdByRole" : "Admin", 
            "createdByUser" : "stla"
        }
    }, 
    "contextData" : {
        "voyagedata" : [
            {
                "id" : "time_departure_estimated", 
                "time:" : "2018-05-12T12:00:00", 
                "format" : "dateTime", 
                "falId" : ""
            }, 
            {
                "id" : "port_of_departure", 
                "port:" : [
                    {
                        "name" : "Gothenburg", 
                        "code" : "SEGOT"
                    }
                ], 
                "format" : "string", 
                "falId" : ""
            }
        ]
    }
}

thanks for your assistance

2

To answer your first question. I guess the problem is that you have to put quotes around $elemMatch

Your second one I can sadly not answer as it is not n8n related rather MongoDB specific and do honestly not have much experience with it at all. Maybe somebody else who does can be of more help here.

3

Thanks for you answer

Did you mean like this?

{ 
  "assetLocation.voyageId": "VOY-1014", 
  "contextData.voyagedata": { 
    "$elemMatch": {
      "id": "time_departure_estimated"
    }
  }
}}

Did not seem to help i tried the first query in 3t studio and there it worked.

Any other idea?

4

I did reformat your example above into multiple lines. Like you can see is there one } too much. So if you get the error message Unexpected token '}' you should be able to fix it if you remove the last one.

At least like that, it should be valid JSON for sure. If you still get an error message, it sounds like it would be also something MongoDB specific.

5

Okey thanks!

Was the same case as last time, when i created a new one on copied the nodes to that one then it did work.

Hmm maybe something that is catching or something?

Thanks for the help anyhow

6

No there is normally no cache. So sadly no idea what could cause that.

7

Thank anyhow now it work.

I will come with a more complicated question tommorow that we have tried to solve for a while.

Have a nice evening.

1 Like
8

Just an update question 2

Query Code in mongoShell

use DomainData;
db.getCollection("McTestData").find (
  {
    "assetLocation.voyageId" : "VOY-1014",
    "contextData.voyagedata": {
    "$elemMatch": {
      "id": "time_departure_estimated"
    }
  }
})
.sort(
  {
    "eventData.timeStampUTC": 1.0
  }
);

I talked with one of our devs, that sorting.limit etc cannot be done via json to mongoDB so i guess then the best way is to just processes the results in new function node and then sort and get the with latest date.

Kind regards
Mattias

9

Again, I have sadly no idea as it is again MongoDB related. But if your developer is right (what I assume) then for now the Function-Node is the way to go. In the long-term we would have to add this missing functionality to the Mongo-Node.

On a side note: Please make sure to always mark code blocks like described here:

and also use the proper indentation. Only then do they get displayed correctly and it is easy to understand what is happening. Currently I always have to fix your posts manually which is quite time intensive. Thanks!

12

HI

Sorry for disturbing again, we ware sitting, me dan the devs to try to solve the function but limit by function. We are still struggling with how to get the array. please see example below

13

Like that it should work:

You had two problems:

  1. you accessed the wrong data
  2. you had a typo in your MongoDB-Mock data
14

Hi Thanks!

Regarding the type sorry for that, must be when we copied the data.

What we are struggling with is to understand how to get the right data. We are trying to get the pattern based on the things you helped us with before. The challenge for us is to get when to use what.

Do you have any documentation on just data mapping into functions?

Great with all support from your side but of course we don´t want to be a burden for the community for those basics.

Thanks for the assistance.

Kind regards
Mattias

15

No problem.

Ah yes can understand that. It definitely not as simply as I like it to be. Hope we can improve that in the future with additional nodes and improved documentation!

About existing documentation. Information about the general structure of data in n8n can be found here:

All the functions which exist and how they can be used is documented here:

Apart from that it is JavaScript programming with functions that are available there like the sort function you used above.

I hope that helps! But it is currently definitely not your “fault” if you have problems. Still much to improve from our side.

16

Thanks Tried it but i only get items not defined… any idea?

Kind regards
Mattias

17

I guess your n8n version is to old.