How do I merge array to become 1 array with unique array of elements using LET

Hi Guys,
Just want to ask, how can merge array to become 1 array with unique array of elements ?
Here is my query:

   SELECT DISTINCT RAW tags AS tags
   FROM `company`
  WHERE `type` = 'product' AND categories IS NOT MISSING
  ORDER BY(tags) ASC LIMIT 2

Result for this query is:

[
  [
    "Antioxidants",
    "Cardiovascular",
    "Gluten Free",
    "Heart health",
    "Kosher",
    "Non-GMO",
    "Softgel",
    "Vegetarian"
  ],
  [
    "Antioxidants",
    "Gluten Free",
    "Heart health",
    "Non-GMO",
  ]
]

The expected result would be like this.

[
  "Antioxidants",
  "Cardiovascular",
  "Gluten Free",
  "Heart health",
  "Kosher",
  "Non-GMO",
  "Softgel",
  "Vegetarian"
]

Sincerely,

SELECT  RAW ARRAY_DISTINCT(  ARRAY_FLATTEN((SELECT DISTINCT RAW tags AS tags
   FROM `company`
  WHERE `type` = 'product' AND categories IS NOT MISSING
  ORDER BY(tags) ASC LIMIT 2),1));

OR

SELECT  RAW ARRAY_DISTINCT(  ARRAY_FLATTEN(av,1))
LET   av = (SELECT DISTINCT RAW tags AS tags
       FROM `company`
      WHERE `type` = 'product' AND categories IS NOT MISSING
      ORDER BY(tags) ASC LIMIT 2);

OR

SELECT  DISTINCT RAW  tag 
FROM (SELECT DISTINCT  tags 
                FROM `company`
               WHERE `type` = 'product' AND categories IS NOT MISSING
              ORDER BY(tags) ASC LIMIT 2) AS d
UNNEST d.tags AS tag;

Hi @vsr1,

 Cool! it works for me :)

the LET statement works for me:

SELECT  RAW ARRAY_DISTINCT(  ARRAY_FLATTEN(av,1))
LET   av = (SELECT DISTINCT RAW tags AS tags
       FROM `company`
      WHERE `type` = 'product' AND categories IS NOT MISSING
      ORDER BY(tags) ASC LIMIT 2);

Here’s the result:

[
  [
    "50+",
    "Age",
    "Caplet",
    "Gluten Free",
    "Iron",
    "Kosher",
    "Minerals",
    "Multinutrient",
    "Multivitamin",
    "Women's",
    "Men's"
  ]
]

But, how can I sort, those array items just inside the LET ? it seems the ORDER BY doesn’t work

SELECT  RAW ARRAY_SORT(ARRAY_DISTINCT(  ARRAY_FLATTEN(av,1)))
LET   av = (SELECT DISTINCT RAW tags AS tags
       FROM `company`
      WHERE `type` = 'product' AND categories IS NOT MISSING
      ORDER BY(tags) ASC LIMIT 2);

OR

SELECT  DISTINCT RAW  tag 
FROM (SELECT DISTINCT  tags 
                FROM `company`
               WHERE `type` = 'product' AND categories IS NOT MISSING
              ORDER BY(tags) ASC LIMIT 2) AS d
UNNEST d.tags AS tag
ORDER BY tag;
1 Like

Hi @vsr1,
Wow cool thanks man! :slight_smile: it works appreciated,