Mongodb sort the find query

[Malaga982]

Hello,
i’m trying to create my own rest api to use as an alternative to mlab api ( sooner or later it will shutdown ).
I’m able to do a find search, with something like ?q={“field”:“name”} .
but when i have to sort the find query( like ?q={“field”:“name”}&s={“id”:-1} ), i can’t find how to do it in losant.
In mongoshell it will be something like:

db.getCollection(“Mycollection”).find({}).sort(
{
“id” : 1.0
}
);

My final goal is something like this: https://docs.mlab.com/data-api/#list-documents
Can someone help me with that?
I don’t know if it’s a limit of losant mongodb or a different way to write it.
Thanks

[JuliaKempf]

Hello!

I do not have experience with mLab, but after some poking around I believe there is a comparable way to do this in Losant. It looks as though mLab “documents” are comparable to Losant Data Tables. You would be able to store you data in a data table, and access it in a workflow with a Table: Get Rows Node (for more advanced querying, you can also use the Losant API Node). You will then be able to query the table for certain values and keys, set limits, and sort by ascending or descending direction.

If I misinterpreted you question, please let me know!
Thanks,
Julia

[Malaga982]

Hello, thanks for the reply.
So the sort method is not implemented in losant mongodb? In case not, it will be useful, since sorting is a must.
Or the orderby op: https://docs.mongodb.com/manual/reference/operator/meta/orderby/

Save in a data table, manipulate the array…could do the job but i’m aware it’s time consuming, since i need to purge the data table after every sorting…and my web application use sorted results most of the time.

[JuliaKempf]

Hi @Malaga982,

The MongoDB Node currently supports the following operations:

• Count
• Delete Many
• Delete One
• Distinct
• Find
• Find One
• Insert
• Insert Many
• Insert One
• Remove
• Update
• Update Many
• Update One

However, optional settings are available once an operation is selected within the Query Arguments and Options Arguments. Within these arguments you have the options for various methods like sort, limit, and so on, depending on your selected operation. For example, with the findOne operation, there are two arguments: the query itself, and any options for the query.

Let me know how else I can help!
Julia

[Malaga982]

Within these arguments you have the options for various methods like sort, limit, and so on, depending on your selected operation

Thanks, but i can’t find in docs the sort usage, will you be kind to give me an hint?

This is my current mongodb node, with a {{data.query.s}} for sorting that i can’t find a way to use it

I tried sort({{data.query.q}}), .sort({{data.query.q}}) but no way to achieve sorting

[JuliaKempf]

Hi @Malaga982,

I will work on this and get back to you with an example!

Thanks,
Julia

[Malaga982]

Thanks, appreciate that!
To add additional info, i pass everything in JSON format, as you can see, like trigger…?q={“username”:“test”}&s={“id”:-1} with &s the sorting method i pass to the webhook trigger.

[JuliaKempf]

Hi @Malaga982,

Just wanted to update you that I am still working on this. I have also hit a bit of a snag, but will update you when I am past it.

I have learned, however, we do slim down Mongo operations. I assumed the MongoDB Node to have similar functionality to Data Tables, but we do not provide all of the functionality that MongoDB provides. I will let you know what I find about find and sort!

Thanks for your patience,
Julia

[Malaga982]

Thanks,
that’s a good and a bad news
i hope you can find a way to do it orin case if it can be requested as an incomung feature
I didn’t mind that not all methods was available, good catch!
In the meantime I pause the rest api implementation a bit, just to know if sort can be done or it will be tricky.
Thanks to you for the infos and to hear you soon with good news

[Malaga982]

any news???

[JuliaKempf]

Hi @Malaga982,

Sorry for the delay, and thank you for your patience! We were able to determine that the functionality you have above (find and sort) is also available within Losant.

The MongoDB Node supports Query Options, so in addition to using the find operation, you can further query with options. Here is an example:

I have a MongoDB with 44,215 documents. Using find I am able to get all of them on my payload.

I set my Options Argument to limit my number of documents to 10, and get back that many.

Here is the payload expanded:

When I change my Options Argument to include sort, my payload now looks like this:

I am given the 10 smallest readings of temperature as I chose to sort by temperature. Thus, find and sort will work together!

Further documentation on querying can be found here and here.

Thanks,
Julia

[Malaga982]

Great news!
I’ll try this evening to see that, you made my day!
I will update you soon.
Thanks again @JuliaKempf

[Malaga982]

well, i did some tests
i found i have to use {“sort”:{“year”:-1, “id_month”:-1} } as argument for my sort purpose, but if i use {“sort”:{{data.query.s}} } as argument value, with data.query.s like &s={“year”:-1, “id_month”:-1}, it throws me an error or it doesn’t sort correctly. Dunno why.
I tried with string template but i didn’t find a way to format the argument value the right way i want to give me results.
I found that i can pass all the command as the argument, like &s={“sort”:{“year”:-1, “id_month”:-1} } , with the {“sort”: thing and it returns me the right data
I’m just a perfectionist and will try to avoid the “sort” thing passed by the argument, just need to find the right way, or if someone know what i’m doing wrong

[JuliaKempf]

Hi @Malaga982,

You should be able to query by two parameters by providing an array of sort preferences:

{ "sort": [["field1","asc"], ["field2","desc"]] }

Let me know if this syntax does not work for you!

Thanks,
Julia

[Malaga982]

I have this error trying, maybe i’m using the wrong syntax?!

I was trying with:

and it’s strange because data.query.s should be in a valid JSON format, but nope.
If i add { “sort”: "{{data.query.s}}" } ( the double quote ) it appears to be a valid JSON but it doesn’t sort correctly.
Could it be a parsing error or am i doing something wrong with the passing value?
I can leave with the “sort” passed as an argument, it’s for talking and learning

[JuliaKempf]

Hi @Malaga982,

I just did some testing myself and was able to get both the payload reference and multi-sort to function correctly. I will look further into the multi-sort syntax, as the double brackets [[]] are throwing an error for the JSON Template when specifying sort direction.

To access a value on the payload for a JSON value type, you will need to add jsonEncode as well as another set of curly brackets, like so:

The documentation explains the need for jsonEncode like so:

Any objects or arrays being referenced by a string helper should always use the jsonEncode Handlebars helper to ensure that all string values are wrapped in quotes and all objects print as valid JSON, and should be wrapped in triple curly braces to prevent escaping of characters.

I was able to get the double-sort to work, but did not specify direction as it was throwing errors for the brackets. I will look further into this. However, here as an example of successfully sorting by two values:

Thanks!
Julia

[JuliaKempf]

@Malaga982,

As it turns out, the brackets were not to blame! The error was being thrown on the quotes ('asc' should be "asc"). I apologize for giving incorrect syntax above, here is the correct syntax for specifying sort direction for multi-sort:

Thanks!
Julia

[Malaga982]

Thanks, i’ll give it a try when i have a couple of hours these evenings
There is always something to learn!
Will keep you informed, and thanks for the great support