MongoDB object property check if $exists with certain value in nested array

Question

I have a DB collection like this.

  {
      "name" : "test",
      "gender" : "male",
      "attributes" : [ 
          {
              "field_id" : "123",
              "field_value" : "['Public']"
          }, 
          {
              "field_id" : "124",
              "field_value" : "true"
          },
          {
              "field_id" : "125",
              "field_value" : "['Single']"
          },
      ]
  },
  {
      "name" : "test2",
      "gender" : "male",
      "attributes" : [ 
          
          {
              "field_id" : "125",
              "field_value" : "['Married']"
          },
      ]
  },
  {
      "name" : "test3",
      "gender" : "male",
      "attributes" : [ 
          {
              "field_id" : "123",
              "field_value" : "['Public']"
          },
          {
              "field_id" : "125",
              "field_value" : "['Married']"
          },
      ]
  },
  {
      "name" : "test4",
      "gender" : "male",
      "attributes" : [ 
          {
              "field_id" : "123",
              "field_value" : "['Private']"
          },
      ]
  },
  {
      "name" : "test4",
      "gender" : "male",
      "attributes" : [ 
         
      ]
  }

]

I want to fetch all records which have field_id as 123 and field_value as "Public"; + records in which field_id 123 doesn't exist under attributes.

I tried

var condition = { "attributes" :  {$elemMatch:{field_id:"123",field_value:{ $in:["Public"] }}}};

queryObj.push(condition);

this works fine to fetch records having field_id as 123 and field_value as Public

But I also want to get records in which field_id as 123 doesn't exists, I tried this

var condition1 = 
{ "attributes" :  {$elemMatch:{field_id:"123",$exists:false}}}

queryObj.push(condition1);

but it returns an error as it's not right syntax. Please guide how do I fetch as desired.

This is the expected output, As these records have only Public Value for 123 field_id or field_id as 123 DOES NOT EXIST in these.

[
  {
      "name" : "test",
      "gender" : "male",
      "attributes" : [ 
          {
              "field_id" : "123",
              "field_value" : "['Public']"
          }, 
          {
              "field_id" : "124",
              "field_value" : "true"
          },
          {
              "field_id" : "125",
              "field_value" : "['Single']"
          },
      ]
  },
  {
      "name" : "test3",
      "gender" : "male",
      "attributes" : [ 
          {
              "field_id" : "123",
              "field_value" : "['Public']"
          },
          {
              "field_id" : "125",
              "field_value" : "['Married']"
          },
      ]
  },
  {
      "name" : "test2",
      "gender" : "male",
      "attributes" : [ 
          
          {
              "field_id" : "125",
              "field_value" : "['Married']"
          },
      ]
  },
  {
      "name" : "test4",
      "gender" : "male",
      "attributes" : [ 
         
      ]
  }

]

@CuongLeNgoc I have updated the question and added expected output in there. — Sahil Gulati
– Sahil Gulati, Commented Oct 9, 2020 at 7:01

Kh.Nomani · Accepted Answer · 2020-10-09 12:11:10Z

1

you should use two $elemMatch with $or operator.

your query should be something like this.

db.collection.find({
  $or: [
    {
      "attributes": {
        "$elemMatch": {
          field_id: "123",
          field_value: "['Public']"
        }
      }
    },
    {
      "attributes": {
        "$not": {
          "$elemMatch": {
            field_id: "123"
          }
        }
      }
    }
  ]
})

working example: https://mongoplayground.net/p/CP3HSKmNpKM

answered Oct 9, 2020 at 12:11

Kh.Nomani

1852 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

MongoDB object property check if $exists with certain value in nested array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related