FTS Sort by a fiel is not sorted

Full Text Search
1

Hi, I’m trying to search and get a SORT result, for some reason it seems to fail on the sort. Is a field called “created” with a timestamp. The sort is called like ["-created"]. The query use a limit of 20, but on those 20 the order just looks wrong, is mixed order. What I’m missing?

here is the field that I am trying to sort.

“created”: {
“dynamic”: false,
“enabled”: true,
“fields”: [
{
“analyzer”: “”,
“include_in_all”: false,
“include_term_vectors”: true,
“index”: true,
“name”: “created”,
“store”: false,
“type”: “number”
}
]
}

2

This does not work also:

[{“mode”:“max”,“field”:“created”,“type”:“number”,“descending”:true,“by”:“field”}]

example results:

{
“server”: “stage”,
“eventId”: “a6db3370-544b-46f6-8746-43cb26a41c52:stage”,
“created”: 1527100079286,
“domain”: “mxhero.com”,
“action”: “input”,
“type”: “email”
}
{
“server”: “stage”,
“eventId”: “43ee09ae-9a6b-49d4-950d-b756dab5a762:stage”,
“created”: 1527099925895,
“domain”: “mxhero.com”,
“action”: “input”,
“type”: “email”
},
{
“server”: “stage”,
“eventId”: “46644da0-47a4-4a54-919b-057731e18f56:stage”,
“created”: 1527099965286,
“domain”: “mxhero.com”,
“action”: “input”,
“type”: “email”
},
{
“server”: “stage”,
“eventId”: “39da1426-f798-42cd-b14a-cb8d2461ad98:stage”,
“created”: 1527100054998,
“domain”: “mxhero.com”,
“action”: “input”,
“type”: “email”
}

3

Hi, are you able to share your full query and full FTS index JSON?

4

Here is the index:

{
  "type": "fulltext-index",
  "name": "emails",
  "uuid": "379e14bc8bf91d49",
  "sourceType": "couchbase",
  "sourceName": "stats",
  "sourceUUID": "77671521a9eb12fc3911e22b0761b422",
  "planParams": {
"maxPartitionsPerPIndex": 171
  },
  "params": {
"doc_config": {
  "mode": "type_field",
  "type_field": "action"
},
"mapping": {
  "default_analyzer": "standard",
  "default_datetime_parser": "dateTimeOptional",
  "default_field": "_all",
  "default_mapping": {
    "dynamic": true,
    "enabled": false
  },
  "default_type": "_default",
  "index_dynamic": true,
  "store_dynamic": false,
  "types": {
    "input": {
      "dynamic": false,
      "enabled": true,
      "properties": {
        "created": {
          "dynamic": false,
          "enabled": true,
          "fields": [
            {
              "analyzer": "",
              "include_in_all": false,
              "include_term_vectors": true,
              "index": true,
              "name": "created",
              "store": false,
              "type": "number"
            }
          ]
        },
        "data": {
          "dynamic": false,
          "enabled": true,
          "properties": {
            "email": {
              "dynamic": false,
              "enabled": true,
              "properties": {
                "bcc": {
                  "dynamic": false,
                  "enabled": true,
                  "fields": [
                    {
                      "analyzer": "",
                      "include_in_all": true,
                      "include_term_vectors": true,
                      "index": true,
                      "name": "bcc",
                      "store": false,
                      "type": "text"
                    }
                  ]
                },
                "cc": {
                  "dynamic": false,
                  "enabled": true,
                  "fields": [
                    {
                      "analyzer": "",
                      "include_in_all": true,
                      "include_term_vectors": true,
                      "index": true,
                      "name": "cc",
                      "store": false,
                      "type": "text"
                    }
                  ]
                },
                "date": {
                  "dynamic": false,
                  "enabled": true,
                  "fields": [
                    {
                      "analyzer": "",
                      "include_in_all": false,
                      "include_term_vectors": true,
                      "index": true,
                      "name": "date",
                      "store": false,
                      "type": "number"
                    }
                  ]
                },
                "from": {
                  "dynamic": false,
                  "enabled": true,
                  "properties": {
                    "aliases": {
                      "dynamic": false,
                      "enabled": true,
                      "fields": [
                        {
                          "analyzer": "",
                          "include_in_all": true,
                          "include_term_vectors": true,
                          "index": true,
                          "name": "aliases",
                          "store": false,
                          "type": "text"
                        }
                      ]
                    }
                  }
                },
                "messageId": {
                  "dynamic": false,
                  "enabled": true,
                  "fields": [
                    {
                      "analyzer": "",
                      "include_in_all": true,
                      "include_term_vectors": true,
                      "index": true,
                      "name": "messageId",
                      "store": false,
                      "type": "text"
                    }
                  ]
                },
                "recipient": {
                  "dynamic": false,
                  "enabled": true,
                  "properties": {
                    "aliases": {
                      "dynamic": false,
                      "enabled": true,
                      "fields": [
                        {
                          "analyzer": "",
                          "include_in_all": true,
                          "include_term_vectors": true,
                          "index": true,
                          "name": "aliases",
                          "store": false,
                          "type": "text"
                        }
                      ]
                    }
                  }
                },
                "sender": {
                  "dynamic": false,
                  "enabled": true,
                  "properties": {
                    "aliases": {
                      "dynamic": false,
                      "enabled": true,
                      "fields": [
                        {
                          "analyzer": "",
                          "include_in_all": true,
                          "include_term_vectors": true,
                          "index": true,
                          "name": "aliases",
                          "store": false,
                          "type": "text"
                        }
                      ]
                    }
                  }
                },
                "subject": {
                  "dynamic": false,
                  "enabled": true,
                  "fields": [
                    {
                      "analyzer": "",
                      "include_in_all": true,
                      "include_term_vectors": true,
                      "index": true,
                      "name": "subject",
                      "store": false,
                      "type": "text"
                    }
                  ]
                },
                "to": {
                  "dynamic": false,
                  "enabled": true,
                  "fields": [
                    {
                      "analyzer": "",
                      "include_in_all": true,
                      "include_term_vectors": true,
                      "index": true,
                      "name": "to",
                      "store": false,
                      "type": "text"
                    }
                  ]
                }
              }
            }
          }
        },
        "domain": {
          "dynamic": false,
          "enabled": true,
          "fields": [
            {
              "analyzer": "",
              "include_in_all": false,
              "include_term_vectors": true,
              "index": true,
              "name": "domain",
              "store": false,
              "type": "text"
            }
          ]
        }
      }
    }
  }
},
"store": {
  "kvStoreName": "mossStore"
}
  },
  "sourceParams": {}
}
5

And here is a query:

{
“from”: 0,
“ctl”: {
“timeout”: 75000
},
“sort”: ["-created"],
“size”: 20,
“query”: {
“conjuncts”: [{
“field”: “domain”,
“match”: “mxhero.com”
}, {
“inclusive_max”: true,
“min”: 0.0,
“field”: “data.email.date”,
“inclusive_min”: true,
“max”: 1.529504958169E12
}, {
“disjuncts”: [{
“field”: “data.email.recipient.aliases”,
“match_phrase”: “mmarmol”
}, {
“field”: “data.email.bcc”,
“match_phrase”: “mmarmol”
}, {
“field”: “data.email.cc”,
“match_phrase”: “mmarmol”
}, {
“field”: “data.email.to”,
“match_phrase”: “mmarmol”
}]
}]
}
}

6

We are using version Community Edition 5.0.1 build 5003 @tyler.mitchell

7

I’d do:

[{“mode”:“max”,“field”:“created”,“type”:“number”,“desc”:true,“by”:“field”}]

“descending” is not recognized.

8

I am using couchbase lib for that, is something that got fix in time?

com.couchbase.client
java-client
2.5.8

9

2.5.9 does not mention anything about it.

Fixed Issues
JVMCBC-534: Fix pooledService creating excessive endpoints on sending to downed node
JVMCBC-543: Clean up stale connections when query node goes offline, otherwise the connections may stick around until OS TCP timeout
JCBC-1207: Dont do reverse lookup on DNS SRV bootstrap
Enhancements & New Features
JCBC-1158: Add support for annotations in parent classes
JVMCBC-545: Improve debug logging on Endpoint
JCBC-1209: Audit and add explicit subscribers to non-kv requests to check for timeouts

10

@mmarmol thanks for raising the issue. I’ve filed https://issues.couchbase.com/browse/JCBC-1213 and will follow up with the teams to figure out the right solution. Thanks!

11

hi @mmarmol, does this mean that you were able to get the results SORTed working from direct curl commands and not while using the sdk? Quick local trials showed that the sorting worked correctly with “sort”: ["-created"] option as well for the small result set you shared.
Cheers!

12

Did not worked for me when using ["-created"] using the Java Couchbase SDK, I will try both based on this and let you know. Some how they are ± sorted… lets says that i get 20 records they are not sorted correctly but they are all between a range of minutes… doing a sort after i got them kinda solves the issue… anyway no the idea.

Keep you posted with findings .

13

@sreeks I found the issue, was nothing to do with couchbase. After I get all the IDs from the FTS I was running an async operation for each of them to get the actual values and was adding to a list. Since they are ASYNC returning order was of course not ordered. My bad.

So yes using the [-created] works well on the JAVA Couchbase SDK. Is there a way to actually return the values from the elements and not just the IDs when doing a FTS?

14

found this, got it.