FT.AGGREGATE SORTBY syntax help

Hello,
I would like to move from FT.SEARCH to FT.AGGREGATE, and I don’t seem to be able to get the SORTBY syntax correct, as the results always appear to be sorted by the first SORTABLE field.

Working version of FT.SEARCH:

FT.SEARCH clients_hash_index_2 “*” SORTBY @LastName ASC LIMIT 0 10

FT.AGGREGATE where SORTBY seems to be ignored:

FT.AGGREGATE clients_hash_index_2 “*” SORTBY 4 @LastName ASC @FirstName ASC LIMIT 0 10 APPLY @ClientID as ClientID

Here is the index:

FT.INFO clients_hash_index_2

  1. index_name
  2. clients_hash_index_2
  3. index_options
  4. (empty list or set)
  5. fields
      1. ClientID
    1. type
    2. NUMERIC
    3. SORTABLE
    1. FirstName
1. type
1. TEXT
1. WEIGHT
1. "1"
1. SORTABLE
    1. LastName
1. type
1. TEXT
1. WEIGHT
1. "1"
1. SORTABLE

Any ideas what I am missing?

Thanks!

Hi Paul,

I’m able to successfully sort on two sortable fields independently. Can you give a short cross section of data that shows your issue?

Thanks!

Kyle

Hi Kyle,
I have no doubt that I am missing something, maybe you could easily spot it from the syntax below:

FT.CREATE “users_hash_index_2” NOSCOREIDX SCHEMA “UserID” NUMERIC SORTABLE “UserName” TEXT SORTABLE “FirstName” TEXT SORTABLE “LastName” TEXT SORTABLE

FT.ADD users_hash_index_2 1 1 REPLACE FIELDS “UserID” 1 “UserName” “joe” “FirstName” “Joe” “LastName” “Smith”

FT.ADD users_hash_index_2 3 1 REPLACE FIELDS “UserID” 3 “UserName” “angela” “FirstName” “Angela” “LastName” “Doe”

FT.ADD users_hash_index_2 2 1 REPLACE FIELDS “UserID” 2 “UserName” “don” “FirstName” “Don” “LastName” “May”

Ascending sort by the LastName - so I would expect this to return 3,2,1

FT.AGGREGATE “users_hash_index_2” “*” “SORTBY” “2” “@LastName” “ASC” “APPLY” “@UserID” “AS” “UserID” “LIMIT” “0” “10”

  1. (integer) 3
    1. “UserID”
    2. “1”
    1. “UserID”
    2. “3”
    1. “UserID”
    2. “2”

Descending sort by the LastName - so I would expect this to return 1,2,3
FT.AGGREGATE “users_hash_index_2” “*” “SORTBY” “2” “@LastName” “DESC” “APPLY” “@UserID” “AS” “UserID” “LIMIT” “0” “10”

  1. (integer) 3
    1. “UserID”
    2. “2”
    1. “UserID”
    2. “3”
    1. “UserID”
    2. “1”

Based on what I am seeing it actually looks like it is returning the records in ascending or descending order of how they were entered, completely ignoring the fields I want to sort by.

Any help is appreciated!

Thank you!

It could be an issue of lower/upper case.
Could you try that with the field names in all lowercase?

Hey Paul-

  1. NOSCOREIDX was deprecated and now has been removed (https://github.com/RedisLabsModules/RediSearch/issues/149), although I don’t think it really matters here - more of an FYI

  2. I believe the general problem here is that the ‘LastName’ isn’t in brought into the aggregation pipeline before SORTBY. You need to do this via APPLY or LOAD and the sorting will be correct.

Works: FT.AGGREGATE users_hash_index_2 * APPLY @UserID as UserID APPLY @LastName as LastName SORTBY 2 @LastName asc

Works: FT.AGGREGATE users_hash_index_2 * APPLY @UserID as UserID APPLY @LastName as LastName SORTBY 2 @LastName desc

The side effect is that you’re also returning the LastName.

Also, tried Dvir’s suggestion and lowercasing doesn’t seem to help.

Thanks

-Kyle

If that is the case it’s likely a bug. You don’t need apply for sorting by design. Of course my design itself may have changed but it should not be necessary unless for return purposes.

Thank you Kyle, I appreciate your help!

Adding the APPLY for all of the fields I want to include in the sort did the trick. One other thing I was missing was that the SORTBY needs to be after the APPLY otherwise it will not sort.

Hi Dvir,
I even tried changing the schema to be all lower case, but unless I use APPLY for each field I want to sort on, the SORTBY does not appear to work.

Another interesting problem I am seeing is that SORTBY needs to come after the APPLY, otherwise the SORTBY parameters will be ignored, unless this is done by design.

Thank you,

Paul