0

Lets say we have following documents:

{
 reportName: 'ABC_REPORT'
 reports: [
 {
  "startDate" : ISODate("2017-07-02T00:00:00Z"),
  "endDate" : ISODate("2017-07-08T00:00:00Z"),
  "data" : [ { DATA_ID: '012345678004' }, { DATA_ID: 'abcd1984500' } ] 
},
{
 "startDate" : ISODate("2017-07-09T00:00:00Z"),
 "endDate" : ISODate("2017-07-15T00:00:00Z"),
 "data" : [ ] },
{
 "startDate" : ISODate("2017-07-16T00:00:00Z"),
 "endDate" : ISODate("2017-07-22T00:00:00Z"),
 "data" : [{ DATA_ID: '012345678009' }, { DATA_ID: '11111111012' }]
 },
{
 "startDate" : ISODate("2017-07-16T00:00:00Z"),
 "endDate" : ISODate("2017-07-22T00:00:00Z"),
 "data" : [{ DATA_ID: 'abcd9809876' }]
 }
]}

Now I want to write a query that should search for the DATA_IDs that starts with ('01234', 'abcd') and expect to get an array like :

{
data: [{ DATA_ID: '012345678004' },
       { DATA_ID: 'abcd1984500' },
       { DATA_ID: '012345678009' },
       { DATA_ID: 'abcd9809876' }
      ]
 }

Iam trying to solve this with aggregation-framework but really struggling with it.

user22197
  • 129
  • 1
  • 2
  • 10

1 Answers1

1
db.stackOverflow.aggregate([ 
  {$unwind:"$reports”},
  {$unwind:"$reports.data"},
  {$match:{"reports.data.DATA_ID":{$in:[/^abcd/,/^01234/]}}},
  {$group:{_id:null, data:{$push:{"DATA_ID":"$reports.data.DATA_ID"}}}}
])

If you want to run above query for case insensitive put i after /

Ayush Mittal
  • 539
  • 2
  • 5
  • This query is going to return all the documents that have those DATA_IDs. but i need to project an array of those subdocuments that matches with the condition. So the requirement is it should return only one object with an array and that array should contain only the matching objects with DATA_IDs. – user22197 Oct 19 '17 at 19:26
  • This aggregation does not return anything :(.. and i believe we will need to do the projection after match – user22197 Oct 19 '17 at 20:30