Sort id stored as a string as a number in a full text search

[ryosek]

Hello.
I would like to sort user_id stored as a string as a number in a full text search.

My data is as follows.
I want to sort user_id as 1,2,11,101, but in the current query it is 1,101,11,2.
Is there any way to implement this without storing the user_id as a number?
I tried “sort”: [“+TO_NUMBER(user_id)”] but it doesn’t work.

Thanks,

[
{
“user_id”: “1”,
“address”: “new york”
“name”: “Foo”
},
{
“user_id”: “101”,
“address”: “new york”
“name”: “Baz”
},
{
“user_id”: “11”,
“address”: “new york”
“name”: “Bar”
},
{
“user_id”: “2”,
“address”: “paris”
“name”: “York”
}
]

And My n1ql is as follows.

SELECT search_score() as _score, *
FROM
test._default.record
USE INDEX (USING FTS)
WHERE
SEARCH(test,{
“fields”: [“*”],
“highlight”: {},
“size”: 100,
“from” : 0,
“query”: { “query”: “new york”},
“sort”: [“+user_id”]
},{
“index”:“Record”
})

[abhinav]

@ryosek unfortunately text ordering is lexicographic. So a search index will only order your examples as 1, 101, 11, 2.

N1QL’s text sorting is also lexicographic but it supports the to_number function when used within the ORDER BY clause. You cannot embed a N1QL function within the SEARCH function though.

So your query will simply need to change to this to get things to work your way -

SELECT search_score() AS _score, *
FROM test._default.record USE INDEX (USING FTS)
WHERE SEARCH(test,{ "fields": ["*"], "highlight": {}, "size": 100, "from" : 0, "query": { "query": "new york"}},{ "index":"Record" })
ORDER BY to_number(user_id)

[ryosek]

@abhinav
Thank you, I tried your query and the results are ordered as numbers!
But does this query work with limit clause?

For example, if user_id is “1”, “101”, “11”, “2” and I run the query with { “size” : 3, “from” : 0, } and ORDER BY to_number(user_id) in the query, which would be the correct result?

• 1, 2, 11
• 1, 11, 101

[vsr1]

You must remove size, from in search and the following should give right results.
As you are doing order by out side you don’t want apply size/from inside the SERACH(). Let all qualified results produced then order and limit.

SELECT search_score() AS _score, *
FROM test._default.record USE INDEX (USING FTS)
WHERE SEARCH(test,{ "fields": ["*"], "highlight": {}, "query": { "query": "new york"}},{ "index":"Record" })
ORDER BY to_number(user_id)
OFFSET 10
LIMIT 100;

[ryosek]

@vsr1
I tried your query and the result is what I wanted!

@abhinav @vsr1
Thank you so much!