How can I sort array element, regardless if it is upper or lower case

SQL++
,
1

Hi guys,

 I just want to know if how can I sort array of elements, regardless if its upper or lower case strings.

// this is my array:

tags [
   "Acidolphilus",
   "B-Vitamins"
    "Antioxidants"
    "Herbs"
    "algal oil"
     "concentrate"
     "concentrate juice"
]

// expected result:

tags [
    "Acidolphilus",
     "algal oil",
      "Antioxidants",
      "B-Vitamins",
       "concentrate",
       "concentrate juice",
      "Herbs"
]

FYI, currently, I used this clause to sort.

 ARRAY_SORT( ARRAY_DISTINCT( ARRAY_FLATTEN( tag, 1 ) ) ) AS tags

It actually, sorts but, the lower case strings are not considered. :frowning:

tags [
    "Acidolphilus",
     "Antioxidants",
     "B-Vitamins",
    "Herbs"
     "algal oil",
     "concentrate",
     "concentrate juice"
]
2 
SELECT   RAW t
FROM  <your array expression> AS t
ORDER BY TOLOWER(t) ASC;
3

Hi @vsr1,

Thank you for your response ;)

the TOLOWER() doesn’t work.

the query I’ve made works but doesn’t sort well

SELECT ARRAY_SORT( ARRAY_DISTINCT( ARRAY_FLATTEN( tag, 1 ) ) ) AS tags
LET tag = (
  SELECT DISTINCT RAW tags
  FROM `bucket`
  WHERE `type` = 'product' AND tags IS NOT MISSING
  AND active = true
  ORDER BY LOWER(tags) ASC
)

This is my expected result:

tags [
    "Acidolphilus",
    "algal oil",
    "Antioxidants",
    "B-Vitamins",
    "concentrate",
    "concentrate juice",
    "Herbs"
]

regardless of uppercase or lowercase, it will sort.

4 
SELECT DISTINCT RAW tag
FROM `bucket` AS b 
UNNEST b.tags AS tag
 WHERE  b.`type` = 'product' AND  b.tags IS NOT NULL
  AND b.active = true
ORDER BY LOWER(tag)
5

Hi @vsr1,
Sorry for the last response, I’ve tried the query but I got an error of:

 `Duplicate UNNEST alias tag`
6

There is no duplicate alias. Post complete query

7

Hi @vsr1,

Here’s my query:

SELECT
  ARRAY_SORT( ARRAY_DISTINCT( ARRAY_FLATTEN( tag, 1 ) ) ) AS tags
LET
  tag = (
    SELECT DISTINCT RAW tag
    FROM `bucket`AS b
    UNNEST b.tags AS tag
    WHERE b.`type` = 'product' AND b.tags IS NOT MISSING
    AND b.active = true
    ORDER BY LOWER(tag)
  )
8

by the way @vsr1, I already resolved the duplicate, but it seems that sort doesn’t still works :frowning:

the result of the query is this:
it seems that lower case value of for example “algal oil” it’s not sorting.

tags [
    "Acidolphilus",
    "Antioxidants",
    "B-Vitamins",
    "Herbs"
    "algal oil",
    "concentrate",
    "concentrate juice",
]

My expected result would be like this:

tags [
    "Acidolphilus",
    "algal oil",
    "Antioxidants",
    "B-Vitamins",
    "concentrate",
    "concentrate juice",
    "Herbs"
]
9 
SELECT DISTINCT RAW tag
    FROM `bucket`AS b
    UNNEST b.tags AS tag
    WHERE b.`type` = 'product' AND b.tags IS NOT MISSING
    AND b.active = true
    ORDER BY LOWER(tag)

This query should give results . Not sure why do u need ARRAY_FLATTEN,SORT,DISTINCT. Unlees I missing something

10

Hi @vsr1,
Yes, your correct, I did remove the array flatten and it works. thanks :slight_smile: