Select key values from array of objects without repeating


#1

HI I have document like:

  "name": "Test",
  "sellers": [{
    "id": "1427",
    "items": 12,
    "country_name": "United Kingdom",
    "country_code": "GB",
  }, {
    "id": "132",
    "items": 12,
     "country_name": "United Kingdom",
    "country_code": "GB",
  }, {
    "id": "1711",
    "items": 12,
     "country_name": "France",
    "country_code": "FR,
  }],

now, how to select all documents from namespace with each additional array containing objects with countries names and codes without repeating

[
   {name: 'United Kingdom', code: 'UK'}, 
   {name: 'FRANCE', code: 'FR'}
]

#2
SELECT d.*, 
         ARRAY_DISTINCT(ARRAY {"name": v.country_name, "code":v.country_code} FOR v IN d.sellers END) AS countires 
FROM default AS d 
WHERE .....

#3

Thanks but this is not without repeating, I’m getting the same country multiple times


#4
SELECT d.*, 
         ARRAY_DISTINCT(ARRAY {"name": v.country_name, "code":v.country_code} FOR v IN d.sellers END) AS countires 
FROM default AS d 
WHERE .....

OR

SELECT d.*, 
         (SELECT DISTINCT seller.country_name AS name, seller.country_code AS code
           FROM d.sellers AS seller
             ) AS countries
FROM default AS d 
WHERE .....

#5

Is it possible to return distinct countries in array with name and counter which points how many of them exist ?
so:

"name": "Test",
"sellers": [{
"id": "1427",
"items": 12,
"country_name": "United Kingdom",
"country_code": "GB",
  }, {
"id": "132",
"items": 12,
 "country_name": "United Kingdom",
"country_code": "GB",
  }, {
"id": "1711",
"items": 12,
 "country_name": "France",
"country_code": "FR,
  }],

and the result wanted:

[
   {name: 'United Kingdom', code: 'UK', counter: 2}, 
   {name: 'FRANCE', code: 'FR', counter: 1}
]

#6

With in the each document

SELECT d.*,
 (SELECT seller.country_name AS name, seller.country_code AS code, COUNT(1) AS counter 
   FROM d.sellers AS seller
    GROUP BY seller.country_name ,    seller.country_code) AS countries
 FROM default AS d 
WHERE .....