Error on querying FIWARE Orion with orderBy custom attribute

Question

I am facing the following issue while querying Orion with orderBy, asking the results to return on chronological order. My data looks like this:

{
    "id": "some_unique_id",
    "type": "sensor",
    "someVariable": {
        "type": "String",
        "value": "1",
        "metadata": {}
    },
    "datetime": {
        "type": "DateTime",
        "value": "2018-09-21T00:38:57.00Z",
        "metadata": {}
    },
    "humidity": {
        "type": "Float",
        "value": 55.9,
        "metadata": {}
    },
    "someMoreVariables": {
        "type": "Float",
        "value": 6.29,
        "metadata": {}
    }

My call looks like this:

http://my.ip.com:1026/v2/entities?type=sensor&offset=SOMENUMBERMINUS30&limit=30&orderBy=datetime

Unfortunately, the response is the following:

{
"error": "InternalServerError",
"description": "Error at querying MongoDB"}

Both tenant and subtenant are used in the call, while the Orion version is 1.13.0-next and tenant has been indexed inside the MongoDB. I am running Orion and MongoDB from different Docker instances in the same server. As always, any help will be highly appreciated.

EDIT1: After fgalan's recommendation, I am adding the relative record from the log (I am sorry, I didn't do it from the beginning):

BadInput some.ip time=2018-10-16T07:47:36.576Z | lvl=ERROR | corr=bf8461dc-d117-11e8-b0f1-0242ac110003 | trans=1539588749-153-00000013561 | from=some.ip | srv=some_tenant | subsrv=/some_subtenant | comp=Orion | op=AlarmManager.cpp[211]:dbError | msg=Raising alarm DatabaseError: nextSafe(): { $err: "Executor error: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.", code: 17144 } time=2018-10-16T07:47:36.576Z | lvl=ERROR | corr=bf8461dc-d117-11e8-b0f1-0242ac110003 | trans=1539588749-153-00000013561 | from=some.ip | srv=some_tenant | subsrv=/some_subtenant | comp=Orion | op=AlarmManager.cpp[235]:dbErrorReset | msg=Releasing alarm DatabaseError

From the above, it is clear that indexing is required. I have already done that according to fgalan's answer to another question I had in the past: Indexing Orion

EDIT2: Orion response after indexing:

[
    {
            "v" : 2,
            "key" : {
                    "_id" : 1
            },
            "name" : "_id_",
            "ns" : "orion.entities"
    },
    {
            "v" : 2,
            "key" : {
                    "location.coords" : "2dsphere"
            },
            "name" : "location.coords_2dsphere",
            "ns" : "orion.entities",
            "2dsphereIndexVersion" : 3
    },
    {
            "v" : 2,
            "key" : {
                    "creDate" : 1
            },
            "name" : "creDate_1",
            "ns" : "orion.entities"
    }
]

Could you edit your question to include contexBroker logs, pls? They would be very useful to debug the case. Thx! — fgalan
Thank you very much @fgalan for your quick response. I have edited my question and included the log. — Anastasios Oikonomidis
We are getting closer... ;) Could you also add the index configuration in your database, pls? I mean to run mongo <db> (where <db> is typically orion if you are using default configuration), then db.entities.getIndexes(). Thx! — fgalan
By executing use orion and after the getIndexes, I received the following response: [{"v":2,"key":{"id":1},"name":"_id","ns":"orion.entities"},{"v":2,"key":{"location.coords":"2dsphere"},"name":"location.coords_2dsphere","ns":"orion.entities","2dsphereIndexVersion":3},{"v":2,"key":{"creDate":1},"name":"creDate_1","ns":"orion.entities"}] — Anastasios Oikonomidis
Pls, add the response in the question body (as EDIT 2 block). Thx! — fgalan

fgalan fgalan · Accepted Answer · 2018-10-16T11:05:50

You have an index {creDate: 1} which is the fine if you order by entity creation date using dateCreated (or doesn't specify the orderBy parameters, as creation date is the default ordering):

GET /v2/entities
GET /v2/entities?orderBy=dateCreated

However, if you plan to order by a different attribute defined by you (as I understand datetime is) and get the OperationFailed: Sort operation used more than the maximum error, then you have to create an index for the value of such attribute. In particular you have to create this index:

{ attrs.datetime.value: 1 }

EDIT: as suggested by a comment to this answer, the command for creating the above index typically is:

db.entities.createIndex({"attrs.datetime.value": 1});

EDIT2: have a look to this section in documentation for more detail on this kind of indexes.

Error on querying FIWARE Orion with orderBy custom attribute

1 Answers