Compound data array subdocuments with other array of subdocuments

Hello

I´m a collection of document like this:

{

    "_id" : ObjectId("62d59f2bd6f3809e6588fda4"),
    "ClientNavision" : "51",
    "Version" : 18,
    "Fields" : [
        {
            "I18List" : [
                {
                    "I18Code" : "es-ES",
                    "I18Name" : "Cargo"
                },
                    "I18Code" : "en-US",
                    "I18Name" : "Occupation"
                }
            ],
            "Id" : "1",
            "Description" : "Descripcion de Cargo",
            "ParentId" : "4",
            "Type" : "text",
            "Size" : NumberLong("100"),
            "Default" : true,
            "Order" : 3,
            "Members" : null
        },
                    "I18Name" : "Extension"
            "Id" : "2",
            "Description" : "Descripcion de Extension",
            "Order" : 1,
        }
    ],
    "Clients" : [
            "Active" : true,
            "ClientName" : "Giuseppe Pepponi",
            "ClientI18" : "es-ES",
            "Fields" : [
                    "Id" : "1",
                    "Value" : [ "Supervisor" ]
                    "Id" : "2",
                    "Value" : [ "234" ]
            "Modified" : ISODate("2022-03-24T16:29:28.656+01:00"),
            "Created" : ISODate("2021-09-03T12:50:11.000+02:00"),
            "ClientFolder" : "",
            "ClientPhone" : ""
            "Cif" : "f98765422",
            "ClientName" : "Puigdémont Indultos S.L.",
                    "Value" : [ "Jefe" ]
                    "Value" : [ "123" ]
            "Created" : ISODate("2021-09-09T19:01:31.758+02:00"),
            "Modified" : ISODate("2021-09-09T19:01:31.758+02:00"),
    ]
}

I need recover the collection of Fields into the Clients.Fields collection by the Field.Id and recover the especific value selected by the clients (clients.Fields.Value)

I tried with graphlookup, but i´m not find the correct solution

This is and example of the desired result:

// collection: Customers

{

    "_id" : ObjectId("62d59f2bd6f3809e6588fda4"),
    "ClientNavision" : "51",
    "Version" : 18,
    "Clients" : [
        {
            "Id" : "1",
            "Active" : true,
            "ClientName" : "Giuseppe Pepponi",
            "ClientI18" : "es-ES",
            "Fields" : [
                {
                    "I18List" : [
                        {
                            "I18Code" : "es-ES",
                            "I18Name" : "Cargo"
                        },
                            "I18Code" : "en-US",
                            "I18Name" : "Occupation"
                        }
                    ],
                    "Id" : "1",
                    "Description" : "Descripcion de Cargo",
                    "ParentId" : "4",
                    "Type" : "text",
                    "Size" : NumberLong("100"),
                    "Default" : true,
                    "Order" : 3,
                    "Members" : null,
                    "Value" : [ "Supervisor" ]
                },
                    "Id" : "2",
                    "Value" : [ "234" ],
                            "I18Name" : "Extension"
                    "Description" : "Descripcion de Extension",
                    "Order" : 1,
                    "Members" : null
                }
            ],
            "Modified" : ISODate("2022-03-24T16:29:28.656+01:00"),
            "Created" : ISODate("2021-09-03T12:50:11.000+02:00"),
            "ClientFolder" : "",
            "ClientPhone" : ""
        },
            "Id" : "2",
            "Cif" : "f98765422",
            "ClientName" : "Puigdémont Indultos S.L.",
                    "Value" : [ "Jefe" ],
                    "Value" : [ "123" ],
            "Created" : ISODate("2021-09-09T19:01:31.758+02:00"),
            "Modified" : ISODate("2021-09-09T19:01:31.758+02:00"),
        }
    ]
}

thanks in advance

Please share what you tried so that we know not to pursue a solution in the same direction. Also share the results and indicate where it differs from the expected result. Sometimes a slight adjustment to what you tried might solve the issue.

At first look, I think a $lookup is more appropriate than $graphLookup.

send my trie with lookup:

{

      '$lookup': {
        'from': 'Customers',
        'localField': 'Clients.Fields.Id', 
        'foreignField': 'Fields.Id',
        'let': {
          'domains': '$Domains',
          'client_fields_id': '$Clients.Fields.Id',
          'client_fields_value': '$Clients.Fields.Value'
        },
        'pipeline': [
          {
            '$match': {
              '$expr': {
                '$eq': [
                  '$Fields.Id', '$$client_fields_id'
                ]
              }
            }
          }, {
            '$project': {
              'Fields': 1,
              'Domains': 1,
              '_id': 1
            }
          }
        ],
        'as': 'fields_complete'
      }
    }

i tried:

  • with and without localField and ForeingField
  • with project parent arrays and only subproperties

but the result is empty

I tried with

{
    '$graphLookup': {
      'from': 'Customers', 
      'startWith': '$Customers.Fields.Id"', 
      'connectFromField': 'Customers.Clients.Fields.Id', 
      'connectToField': 'Customers.Fields.Id', 
      'as': 'FieldsConnection', 
      'depthField': 'prof', 
      'restrictSearchWithMatch': {}
    }
  },

with identical wrong result

I have tried to cut-n-paste your sample input and result documents from your first post and they both gives me syntax error.

Make sure we can cut-n-paste your documents because it makes it impossible to experiment with erroneous documents.

Make sure you identify your collection names correctly. In your original post you identify the desired result as the collection Customers but in your lookup code it looks like it is your input collection.

sorry, send you tall thes test:

[
  {
    '$match': {
      'Version': 18, 
      'ClientNavision': '51', 
      'Clients.Id': '1', 
      'Clients.Active': true
    }
  }, {
    '$lookup': {
      'from': 'Customers', 
      'localField': 'Clients.Fields.Id', 
      'foreignField': 'Fields.Id', 
      'let': {
        'domains': '$Domains', 
        'client_fields_id': '$Clients.Fields.Id', 
        'client_fields_value': '$Clients.Fields.Value'
      }, 
      'pipeline': [
        {
          '$match': {
            '$expr': {
              '$eq': [
                '$Fields.Id', '$$client_fields_id'
              ]
            }
          }
        }, {
          '$project': {
            'Fields': 1, 
            'Domains': 1, 
            '_id': 1
          }
        }
      ], 
      'as': 'fields_complete'
    }
  }, {
    '$project': {
      '_id': 0, 
      'ClientNavision': 1, 
      'Site': 1, 
      'Relations': 1, 
      'Fields': 1, 
      'Clients.Fields': 1, 
      'Clients.Id': 1, 
      'Clients.Active': 1, 
      'Clients.Local': 1, 
      'Clients.Cp': 1, 
      'Clients.ClientI18': 1, 
      'Clients.ContactPhone': 1, 
      'Clients.ClientName': 1, 
      'Clients.Direction': 1, 
      'fields_complete': 1
    }
  }, {
    '$unwind': {
      'path': '$Clients', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$match': {
      'Clients.Id': '1', 
      'Clients.Active': true
    }
  }, {
    '$unwind': {
      'path': '$Clients.Fields', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$project': {
      'ClientNavisionId': '$ClientNavision', 
      'Site': '$Site', 
      'Relations': '$Relations', 
      'Value': '$Clients.Fields.Value', 
      'Id': '$Clients.Fields.Id', 
      'Description': '$Clients.Fields.Description', 
      'ParentId': '$Clients.Fields.ParentId', 
      'Type': '$Clients.Fields.Type', 
      'Size': '$Clients.Fields.Size', 
      'Default': '$Clients.Fields.Default', 
      'Order': '$Clients.Fields.Order', 
      'Members': '$Clients.Fields.Members', 
      'I18List': '$Clients.Fields.I18List', 
      'ClientId': '$Clients.Id', 
      'ClientI18': '$Clients.ClientI18', 
      'FieldsMatch': '$FieldsConnection', 
      'Stockdata': '$stockdata'
    }
  }, {
    '$sort': {
      'Created': 1
    }
  }, {
    '$limit': 100
  }
]

the reason is, the two subdocuments collection are in the same document collection (Customers), i need join Customers.Fields (array of subdocuments of Customer) with Customer.Clients.Fields (array of subdocuments of Customer)

Please read Formatting code and log snippets in posts and update your post. We cannot use that the way it is formatted.

If they are in the same document it will be easier to do on the client side with your language of choice.

ok, sorry, its my first post, later format and resend the data

If you really want to do it on the server and the you are working within the same document, you will need a $set stage that uses $map on Clients.Fields that $reduce Fields into the appropriate value.

[
  {
    '$match': {
      'Version': 18, 
      'ClientNavision': '51', 
      'Clients.Id': '1', 
      'Clients.Active': true
    }
  }, {
    '$lookup': {
      'from': 'Customers', 
      'localField': 'Clients.Fields.Id', 
      'foreignField': 'Fields.Id', 
      'let': {
        'domains': '$Domains', 
        'client_fields_id': '$Clients.Fields.Id', 
        'client_fields_value': '$Clients.Fields.Value'
      }, 
      'pipeline': [
        {
          '$match': {
            '$expr': {
              '$eq': [
                '$Fields.Id', '$$client_fields_id'
              ]
            }
          }
        }, {
          '$project': {
            'Fields': 1, 
            'Domains': 1, 
            '_id': 1
          }
        }
      ], 
      'as': 'fields_complete'
    }
  }, {
    '$project': {
      '_id': 0, 
      'ClientNavision': 1, 
      'Site': 1, 
      'Relations': 1, 
      'Fields': 1, 
      'Clients.Fields': 1, 
      'Clients.Id': 1, 
      'Clients.Active': 1, 
      'Clients.Local': 1, 
      'Clients.Cp': 1, 
      'Clients.ClientI18': 1, 
      'Clients.ContactPhone': 1, 
      'Clients.ClientName': 1, 
      'Clients.Direction': 1, 
      'fields_complete': 1
    }
  }, {
    '$unwind': {
      'path': '$Clients', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$match': {
      'Clients.Id': '1', 
      'Clients.Active': true
    }
  }, {
    '$unwind': {
      'path': '$Clients.Fields', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$project': {
      'ClientNavisionId': '$ClientNavision', 
      'Site': '$Site', 
      'Relations': '$Relations', 
      'Value': '$Clients.Fields.Value', 
      'Id': '$Clients.Fields.Id', 
      'Description': '$Clients.Fields.Description', 
      'ParentId': '$Clients.Fields.ParentId', 
      'Type': '$Clients.Fields.Type', 
      'Size': '$Clients.Fields.Size', 
      'Default': '$Clients.Fields.Default', 
      'Order': '$Clients.Fields.Order', 
      'Members': '$Clients.Fields.Members', 
      'I18List': '$Clients.Fields.I18List', 
      'ClientId': '$Clients.Id', 
      'ClientI18': '$Clients.ClientI18', 
      'FieldsMatch': '$FieldsConnection', 
      'Stockdata': '$stockdata'
    }
  }, {
    '$sort': {
      'Created': 1
    }
  }, {
    '$limit': 100
  }
]

OK thank you very much

In short, I understand that my approach was wrong, the ways to do it when we work on the same document would be:

  1. do it in the calling programming language
  2. modify the data with combinations of $set, $map, $match, $reduce,

I’ll try the second option and if I don’t get it, I’ll do it in code

1 Like

I encourage you to follow the thread Complex aggregation pipelines vs complex programming logic because it is related to yours.

It is about deciding whether doing data transformation on the data server or in the application code.