Return only matching array via Data API

[spencerm]

Hi, I can’t find any documentation on how to return only the matching arrays using the data-api endpoint /action/findOne.

This is the query I’m using

{
   "dataSource":"Cluster0",
   "database":"v3",
   "collection":"allPersons",
   "filter":{
      "$and":[
         {
            "host":"host-sandbox"
         },
         {
            "company.name":{
               "$regex":"name2",
               "$options":"i"
            }
         },
         {
            "company.pipedrive.orgId":{
               "$ne":null
            }
         },
         {
            "company.pipedrive.pipedrive.orgId":{
               "$ne":""
            }
         }
      ]
   }
}

This is what my document scheme looks like:

{
  "_id": {
    "$oid": "630ec5d1840fae32479fbc72"
  },
  "host": "host-sandbox",
  "person": {
    "fullName": "Placeholder",
  },
  "company": [
    {
      "name": "name",
      "size": "9",
      "email": "email",
      "website": "website",
      "gpStatus": "valid",
      "industry": "Law Practice",
      "position": "Senior Counsel ",
      "companyOrder": "1",
      "verifyStatus": "success",
      "pipedrive": {
        "sync": "ready",
        "orgId": "1"
      },
      "verifyProcessed": true,
      "verifyResults": "valid"
    },
    {
      "name": "name2",
      "size": "9",
      "email": "email2",
      "website": "website2",
      "gpStatus": "valid",
      "industry": "Law Practice",
      "position": "Senior Counsel ",
      "companyOrder": "1",
      "verifyStatus": "success",
      "pipedrive": {
        "sync": "ready",
        "orgId": "2"
      },
      "verifyProcessed": true,
      "verifyResults": "valid"
    }
  ],
  "importName": "testing_import"
}

this is what I would like the query to return:

   {
      "name": "name2",
      "size": "9",
      "email": "email2",
      "website": "website2",
      "gpStatus": "valid",
      "industry": "Law Practice",
      "position": "Senior Counsel ",
      "companyOrder": "1",
      "verifyStatus": "success",
      "pipedrive": {
        "sync": "ready",
        "orgId": "2"
      }

Thank you so much for your help!

[Jason_Tran]

Hi @spencerm,

I believe you can use the aggregate action for the API request to achieve a similar output with the $filter operator. I’ve made a very simple example based off the sample document you have provided which only filters based off the objects within the "company" array and if those object’s have a "name" field value of "name2". You can alter the pipeline and condition accordingly based off your use case and requirements.

Request:

curl --location --request POST 'https://data.mongodb-api.com/app/data-abcde/endpoint/data/v1/action/aggregate' \
--header 'Content-Type: application/json' \
--header 'api-key: <MY_API_KEY>' \
--data-raw '{
    "collection":"data",
    "database":"myFirstDatabase",
    "dataSource":"Cluster0",
    "pipeline": [
        {
          "$addFields": {
            "filteredArray":{
              "$filter":{
                "input":"$company",
                "cond":{"$eq":["$$this.name","name2"]}
              }
            }
          }
        },
        {
          "$project": {"_id":0,"filteredArray":1}
        }
      ]
}'

Response (formatted):

{
  "documents":[
    {
      "filteredArray":[
        {
        "name":"name2",
        "size":"9",
        "email":"email2",
        "website":"website2",
        "gpStatus":"valid",
        "industry":"Law Practice",
        "position":"Senior Counsel ",
        "companyOrder":"1",
        "verifyStatus":"success",
        "pipedrive":{
           "sync":"ready",
           "orgId":"2"
        },
        "verifyProcessed":true,
        "verifyResults":"valid"
        }
      ]
    }
  ]
}

Hope this helps.

Regards,
Jason