CODEWITHSUNDEEP
mongodbaggregatepymongo
YEZIHANG
YEZIHANG

Reputation: 25

how to convert objectid of mongodb child collection to string inplace during aggregation

I have two collections like this:

# col1
[
  {
    _id: '5ec878f79c87a300127ec503',
    name: 'dim'
  },
  {...},
]

# col2
[
  {
    _id: ObjectId('5ec8da619c87a30012f41d0b'),
    record_id: '5ec878f79c87a300127ec503',
    tags: 'authenticated'
  },
  {
    _id: ObjectId('5ec8da619c87a30012ffdsk1'),
    record_id: '5ec878f79c87a300127ec503',
    tags: 'pre'
  },
]

I want to join two collections into one and convert col2._id from objectid to string.

heres my aggregate query:

col1.aggregate(
  [
    {'$lookup': {
       'from': 'col2',
       'localField': '_id',
       'foreignField': 'record_id',
       'as': 'records'
    }},
    {'$project': {'records._id': {'$toString': '$records._id'}}}
  ]
)

The result I except should be like this:

# result
[
  {
    '_id': '5ec878f79c87a300127ec503',
    'name': 'dim',
    'records': [
        {
          '_id': '5ec8da619c87a30012f41d0b',
          'record_id': '5ec878f79c87a300127ec503',
          'tags': 'authenticated'
        },
        {
          '_id': '5ec8da619c87a30012ffdsk1',
          'record_id': '5ec878f79c87a300127ec503',
          'tags': 'pre'
        },
      ]
  }
]

but I got an error.

Unsupported conversion from array to string in $convert with no onError value

I also tried $addFields and $map. It either override records and return an array, or create a new field. that not what I want.

{'$addFields': {
  'records': {
    '$map': {
        'input': '$records',
        'as': 'r',
        'in': {'$toString': '$$r._id'}
      }
    }
}}

So my question is: how to convert col2._id from objectid to string inplace during aggregate?

Upvotes: 0

Views: 1001

Answers (1)

Programmer Analyst
Programmer Analyst

Reputation: 919

you can try below option where we first convert id to String and store as a convertedIdStr and then using that we do a lookup.

[
 {
  "$addFields": {
     "convertedIdStr": {
        "$toString": "$_id"
     }
  }
    },
 {
  "$lookup": {
     "from": "test",
     "localField": "convertedIdStr",
     "foreignField": "record_id",
     "as": "records"
  }
 }
]

Hope this helps! Note We run this aggregation on col1 (collection1)

Upvotes: 0

Related Questions