Need help in Flattening array of json in Mongo

Hi All,

We have received requirement in MongoDB to flatten array of json.

Could you please help us by providing the sample code to flatten the below array of json(refer to column p).

Thanks,
Ramana

Hi @Ramana_nomula,

The $unwind aggregation pipeline stage can be used to accomplish this.

Can you share an example of what you expect the output to be as this would help us better inform you.

2 Likes

Hi @alexbevi,

Thanks for quick response.

We need the output in below format,.

flattening json field and comparing with source columns.

Hi All,

Any inputs on this?

@Ramana_nomula the following pipeline should produce the desired result.

db.foo.drop();
db.foo.insertMany([
{ _id: 1, price: 12, stock_item: "almonds", ordered: 2, p: [ { _id: 1, stock_item: "almonds", price: 12, ordered: 2 }, { _id: 3, stock_item: "almonds", price: 12, ordered: 2 } ] },
{ _id: 2, price: 20, stock_item: "pecans", ordered: 2, p: [ { _id: 2, stock_item: "pecans", price: 12, ordered: 2 } ] },
{ _id: 3, price: 10, stock_item: "cookies", ordered: 20, p: [ ] }
])
db.foo.aggregate([
{ $unwind: "$p" },
{ $addFields: {
  fields: { $objectToArray: "$p" }
}},
{ $unwind: "$fields" },
{ $match: { "fields.k": { $ne: "_id" } } },
{ $project: {
  field_name: "$fields.k",
  matched: {
    $switch: {
      branches: [
        { case: {  $eq: [ "$fields.k", "stock_item" ] }, then: { $eq: [ "$fields.v", "$stock_item" ] } },
        { case: {  $eq: [ "$fields.k", "price" ] }, then: { $eq: [ "$fields.v", "$price" ] } },
        { case: {  $eq: [ "$fields.k", "ordered" ] }, then: { $eq: [ "$fields.v", "$ordered" ] } }
      ],
      default: false
    }
  },
  source_value: "$fields.v",
  target_value: {
    $switch: {
      branches: [
        { case: {  $eq: [ "$fields.k", "stock_item" ] }, then: "$stock_item" },
        { case: {  $eq: [ "$fields.k", "price" ] }, then: "$price" },
        { case: {  $eq: [ "$fields.k", "ordered" ] }, then: "$ordered" }
      ],
      default: false
    }
  }  
}}
]);

I’ve seeded the test collection with the 3 sample documents you shared so you can validate the results.

Hi @alexbevi,

Thanks for quick response, please find the below updated expected output as we have some changes in the expected output(highlighted in yellow colour).

In some scenarios, there is possibility of different column names, for example, stock_item is compared to item as shown in above screenshot.

Could you please modify your query logic according to the updated output.

Thanks,
Ramana