Aggregate pipleline to generate new fields from the array

Working with Data
,
1

Hello Everyone,
I have this collection:

[primary] distapp> db.sellers.find({ code: 'MATS' }, { code: 1, name: 1, "settings.constraints": 1 })
[
  {
    _id: ObjectId("5e73e8ec6294db00038b4f1c"),
    settings: {
      constraints: {
        'Texture:Type': {
          regex: 'soft|hard|matty|tough|stainresist|adhesive'
        },
        '%or': [
          { 'thickness:ratio': { '%gte': 700 } },
          {
            'thickness:ratio': { '%gte': 600, '%lte': 699 },
            TRA: { '%max': 1.3 }
          },
          {
            'thickness:ratio': { '%gte': 580, '%lte': 599 },
            TRA: { '%max': 1.2 }
          },
          {
            'thickness:ratio': { '%gte': 540, '%lte': 579 },
            TRA: { '%max': 1.1 }
          },
          { TRA: { '%max': 1 }, 'thickness:ratio': { '%eq': null } }
        ]
      }
    },
    code: 'DELTA',
    name: 'DELTA pads'
  }
]

[primary] distapp> db.sellers.find({ code: 'MATS' }, { code: 1, name: 1, "settings.constraints": 1 })
[
  {
    _id: ObjectId("5e73e8ec6294db00038b4f1c"),
    settings: {
      constraints: {
        'Texture:Type': {
          regex: 'soft|hard|matty|tough|stainresist|adhesive'
        },
        '%or': [
          { 'thickness:ratio': { '%gte': 700 } },
          {
            'thickness:ratio': { '%gte': 600, '%lte': 699 },
            TRA: { '%max': 1.3 }
          },
          {
            'thickness:ratio': { '%gte': 580, '%lte': 599 },
            TRA: { '%max': 1.2 }
          },
          {
            'thickness:ratio': { '%gte': 540, '%lte': 579 },
            TRA: { '%max': 1.1 }
          },
          { TRA: { '%max': 1 }, 'thickness:ratio': { '%eq': null } }
        ]
      }
    },
    code: 'DELTA',
    name: 'DELTA pads'
  }
]

How can i generate the following fields from the settings.constraints array, this is required for csv export.

Code: "$code"
Name: "$name"
"$settings.constraints.Texture:Type.regex"
"$settings.constraints.%or.0.thickness:ratio.%gte"
"$settings.constraints.%or.1.thickness:ratio.%gte"
"$settings.constraints.%or.1.thickness:ratio.%lte"
"$settings.constraints.%or.1.TRA.%max"
"$settings.constraints.%or.2.thickness:ratio.%gte"
"$settings.constraints.%or.2.thickness:ratio.%lte"
"$settings.constraints.%or.2.TRA.%max"
"$settings.constraints.%or.3.thickness:ratio.%gte"
"$settings.constraints.%or.3.thickness:ratio.%lte"

Thanks

3

Hey @Ashok_Kumar10,

Welcome to the MongoDB Community Forums! :leaves:

Since your end goal is to export the required fields to CSV, you can try and use the mongoexport command. To test this, I created a document from the sample you shared, and used mongoexport command to get a CSV for the first seven fields you mentioned:

mongoexport --collection=sample --db=test --type=csv --fields='code', 'name', 'settings.constraints.Texture:Type.regex', 'settings.constraints.%or.0.thickness:ratio.%gte','settings.constraints.%or.1.thickness:ratio.%gte','settings.constraints.%or.1.thickness:ratio.%lte','settings.constraints.%or.1.TRA.%max' --out=events.csv

This is the CSV snippet:

code,name,settings.constraints.Texture:Type.regex,settings.constraints.%or.0.thickness:ratio.%gte,settings.constraints.%or.1.thickness:ratio.%gte,settings.constraints.%or.1.thickness:ratio.%lte,settings.constraints.%or.1.TRA.%max
DELTA,DELTA pads,soft|hard|matty|tough|stainresist|adhesive,700,600,699,1.3

You can mention all the fields that you want to display in your CSV and can get the required CSV file. You can also, create a view of all the fields that you want from the collection and then export it using mongoexport.

Please let us know if this helps or not. Feel free to reach out for anything else as well.

Regards,
Satyam

1 Like