Identify records in a.d that do not have corresponding record in a.p in nested array

Question

I need to identify the list of array records a.d[] that do not have duplicate in a.p[]. In the nested object below with a.d.prid:"2" is such an record: it has same pid with a.p[pid] and a.d[pid], but has the prid different="2"

Example document:

{
 "f": 1,
 a: [
  { 
    aid:1,
    p: [
      {
        prid: "1",
        pid: {
          c: "A",
          s: "B",
          k: "C"
        },
        X: 20
      }
    ],
    d: [
      {
        prid: "1",
        pid: {
          c: "A",
          s: "B",
          k: "C"
        }
      },
      {
        prid: "2",
        pid: {
          c: "A",
          s: "B",
          k: "C"
        }
      }
    ]
  }
  ]
 }

I need a list with all such a.d records that have pid for same document in a.d[] and a.p[] , but have different prid

Expected output:

{ "f":1 , "aid": 1 , "prid": 2 }

playground

Later I need to delete those nested objects as they are considered "corrupted", but need to get the list first.

nimrod serok · Accepted Answer · 2023-05-22 12:54:56Z

1

One option is to use $mergeObjects and $in inside a $map to find the items on d without a matching item on p:

db.collection.aggregate([
  {$set: {
      a: {$map: {
          input: "$a",
          as: "item",
          in: {
            d: "$$item.d",
            aid: "$$item.aid",
            p: {$map: {
                input: "$$item.p",
                in: {$mergeObjects: [{prid: "$$this.prid"}, "$$this.pid"]}
            }}
      }}}
  }},
  {$project: {
      _id: 0,
      res: {$map: {
          input: "$a",
          as: "item",
          in: {$reduce: {
              input: "$$item.d",
              initialValue: [],
              in: {$concatArrays: [
                  "$$value",
                  {$cond: [
                      {$in: [
                          {$mergeObjects: [
                              {prid: "$$this.prid"},
                              "$$this.pid"
                          ]},
                          "$$item.p"
                      ]},
                      [],
                      [{
                          f: "$f",
                          aid: "$$item.aid",
                          prid: "$$this.prid"
                      }]
                  ]}
              ]}
          }}
      }}
  }},
  {$set: {
      res: {$reduce: {
          input: "$res",
          initialValue: [],
          in: {$concatArrays: ["$$value", "$$this"]}
      }}
  }},
  {$match: {"res.0": {$exists: 1}}}
])

Update: For some reason it is not consistent on the mongodb playground, but I don't understand why. It is tested and consistent on Robo 3T and studio 3T...

edited May 22, 2023 at 12:54

answered May 22, 2023 at 10:50

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

R2D2 Over a year ago

Thanks @nimrod , it seems something is not correct, in the playground the aid:2 d.prid:7 is matching the condition but not shown in the result ... , the result need to show only the ones from d not matching in p

nimrod serok Over a year ago

@R2D2, For some reason it is not consistent on the mongodb playground, but I don't understand why. It is tested and consistent on Robo 3T and studio 3T...

Collectives™ on Stack Overflow

Identify records in a.d that do not have corresponding record in a.p in nested array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related