Order by filed (list)

N1QL
#1

Hi @vsr1

is it possible to order by field?

something like:

select * from bucket where foo=bar order by field (xxx, “us”, “gb”, “es”) ?

I can’t find any documentation related to this, and if I try the above I get an error: “Number of arguments to function FIELD must be 2. - at end of input”

What I need is to be able to order the results in a specific order based on a list of values, not just ASC or DESC

#2

question is not clear. give example

#3

I have a list of documents

{ 
"app_name": "abc",
"country_code": "us"
"type": "app"
}

{ 
"app_name": "abcd",
"country_code": "gb"
"type": "app"
}

{ 
"app_name": "abce",
"country_code": "es"
"type": "app"
}

{ 
"app_name": "abcf",
"country_code": "cn"
"type": "app"
}

and I would like to query and order them by country_code in s apecific order

select * from `bucket where type = “app” order by field (country_code, “us”, “gb”, “cn”)

and the results to be

{ 
"app_name": "abc",
"country_code": "us"
"type": "app"
}

{ 
"app_name": "abcd",
"country_code": "gb"
"type": "app"
}

{ 
"app_name": "abcf",
"country_code": "cn"
"type": "app"
}

{ 
"app_name": "abce",
"country_code": "es"
"type": "app"
}
#4 
WITH orderobj AS ({"us":1, "gb":2, "cn":3, "es":4})
SELECT b.*
FROM `bucket` AS b
WHERE b.foo = "bar"
ORDER BY orderobj.[b.country_code];


WITH orderobj AS ({"us":10, "gb":2, "cn":3, "es":4})
SELECT b.*
FROM [{ "app_name": "abc", "country_code": "us","type": "app" }, { "app_name": "abcus", "country_code": "us","type": "app" }, { "app_name": "abcd", "country_code": "gb","type": "app" }, { "app_name": "abce", "country_code": "es","type": "app" }, { "app_name": "abcf", "country_code": "cn","type": "app" }] AS b
WHERE b.type = "app"
ORDER BY orderobj.[b.country_code];
#5

hmm, very smart! Thank you

Still, something is not working I am getting a totally different order.

The order is correct but the results are sorted at the end. Do I need to define all the potential options, or is there a way to move them at the begining for the country codes which are not defined in sort object?

here are the results:

[
{
“country_code”: “ro”
},
{
“country_code”: “us”
},
{
“country_code”: “gb”
},
{
“country_code”: “cn”
},
{
“country_code”: “es”
}
]

as you can see they are sorted but at the end

#6

There is typo in country_code of ORDER BY

#7

yes, I saw that, but see my additional comments regarding the order

#8

You can specify what you want and let other appears at the end with NULLS LAST

WITH orderobj AS ({"us":1, "gb":2, "cn":3, "es":4})
SELECT b.*
FROM `bucket` AS b
WHERE b.foo = "bar"
ORDER BY orderobj.[b.country_code] NULLS LAST;
1 Like
#9

yes, that’s it. Thanks so much, you are amazing