Mongo - distinct of specific field of an object into an array

Question

My goal is having the "distinct" of the fourth field of the first array element

My sample data:

{
 'name': 'tizio'
,'address':[{  'roadType'   : "via",
             , 'roadname'   : "Roma"
             , 'number'     : "12 bis"
             , 'city'       : 'Milano'
            },
            {  'roadType'   : "via",
             , 'roadname'   : "Emilia"
             , 'number'     : "124"
             , 'city'       : "Modena"
            },
            {  'roadType'   : "via",
             , 'roadname'   : "Appia"
             , 'number'     : "89"
             , 'city'       : "Genova"
            }
            ]
}

,{
 'name': 'caio'
,'address':[{  'roadType'   : "vicolo",
             , 'roadname'   : "stretto"
             , 'number'     : "12"
             , 'town'       : 'Monza'
            },
            {  'roadType'   : "largo",
             , 'roadname'   : "Garibaldi"
             , 'number'     : "24"
             , 'city'       : "Modena"
            },
            {  'roadType'   : "piazza",
             , 'roadname'   : "Armi"
             , 'number'     : "26"
             , 'city'       : "Rovigo"
            }
            ]
},
{
 'name': 'sempronio'
,'address':[{  'roadType'   : "via",
             , 'roadname'   : "Roma"
             , 'number'     : "15"
             , 'city'       : 'Milano'
            },
            {  'roadType'   : "via",
             , 'roadname'   : "Po"
             , 'number'     : "4"
             , 'city'       : "Torino"
            },
            {  'roadType'   : "largo",
             , 'roadname'   : "Garibaldi"
             , 'number'     : "9"
             , 'community'  : "Genova"
            }
            ]
}

In my case the result should be:

{'city': 'Milano'}
{'town': 'Monza'}

Here is a sql-like Idea

select distinct(address[0][4]).key as community_type, 
       distinct(address[0][4]).value as community_name
from collection

doing

db.collection.distinct('address.0')

but it returns all the fields

            {  'roadType'   : "via",
             , 'roadname'   : "Roma"
             , 'number'     : "15"
             , 'city'       : 'Milano'
            },
            {  'roadType'   : "vicolo",
             , 'roadname'   : "stretto"
             , 'number'     : "12"
             , 'town'       : 'Monza'
            },{  'roadType' : "via",
             , 'roadname'   : "Roma"
             , 'number'     : "12 bis"
             , 'city'       : 'Milano'
            }

and

db.collection.distinct('address.0.4')

returns an empty array

NOTE: mongoplayground says query is incorrect but robomongo accepts it

Answer 1

Mongoplayground will not allow you to run distinct, only find and aggregate.

You usually can't query an object by index like that because the field order in an object is not always defined, and MongoDB's query language does not support that usage.

You could use aggregation to convert the object to an array, extract the nth field, and then group on that field. Also note that array indexes are 0-based, so none of those object would have an element with index 4.

db.collection.aggregate([
  {$project: {
    selected: {
      $arrayElemAt: [
        {
          $objectToArray: {
            $arrayElemAt: ["$address",0]
          }
        },
        3
      ]
    }
  }},
  {$group: {_id: "$selected"}}
])

Playground