MongoDB shell: How to convert array fields into string

Question

I have a collection which stores values as arrays, but these need to be strings.

Out of 4000 data sets 800 look like this

{'_id': '5c4f8408fc69ca02c92ac822',
 'enabled': true,
 'order_date': '2019-01-29T10:36:56.906Z',
 'order_id': 'PO-ERSNJ9',
 'productId': ['5dd5ca0170ebec13b8ab1134'],
 'selling_date': null,
 'selling_price': 99.99,
 'split_invoice': null}

for these 800 I need to convert
"productId" : [ "5dd5ca0170ebec13b8ab1134" ]
into
"productId" : "5dd5ca0170ebec13b8ab1134"
via mongo shell.

I already figured, that reduce (aggregation) would be the way to go, but I cannot get the syntax right.

Help would be great. Thanks a lot

Here is a similar question with some answers: convert string array to string. — prasad_
– prasad_, Commented Nov 25, 2019 at 2:16

prasad_ · Accepted Answer · 2019-11-25 13:53:45Z

This shell query updates the collection with the string value of the "orderId" array field.

db.orders.aggregate( [
  { 
     $match: { productId: { $type: "array" } } 
  },
  { 
     $addFields: { productId: { $arrayElemAt: [ "$productId", 0 ] } } 
  }
] 
).forEach( doc => db.orders.updateOne( { _id: doc._id }, { $set: { productId: doc.productId } } ) );

This update will work to convert the array value to a string - this works with MongoDB 4.2 or later versions only. Note the update uses an Aggregation pipeline within the update method.

db.orders.updateMany( 
  { 
      productId: { $type: "array" } 
  },
  [
      { 
          $set: { productId: { $arrayElemAt: [ "$productId", 0 ] } } 
      },
  ]
)

MongoDB v 4.4 introduces the $first array aggregate operator. In the above updateMany it can be used as productId: { $first: "$productId" } instead of productId: { $arrayElemAt: [ "$productId", 0 ] }.

Joe Drumgoole · Accepted Answer · 2019-11-24 23:20:26Z

the mongodb $unwind operator should do what you want. See an example in the MongoDB shell below.

> use test
switched to db test
> db.stack.insertOne({ "_id" : "5c4f8408fc69ca02c92ac822", "enabled" : true, "productId" : [ "5dd5ca0170ebec13b8ab1134" ], "selling_price" : 99.99, "order_date" : "2019-01-29T10:36:56.906Z", "order_id" : "PO-ERSNJ9", "selling_date" : null, "split_invoice" : null })
{ "acknowledged" : true, "insertedId" : "5c4f8408fc69ca02c92ac822" }
> db.stack.aggregate( [{"$unwind" : "$productId"}])
{ "_id" : "5c4f8408fc69ca02c92ac822", "enabled" : true, "productId" : "5dd5ca0170ebec13b8ab1134", "selling_price" : 99.99, "order_date" : "2019-01-29T10:36:56.906Z", "order_id" : "PO-ERSNJ9", "selling_date" : null, "split_invoice" : null }
>

Collectives™ on Stack Overflow

MongoDB shell: How to convert array fields into string

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related