Need to get All sId:"A","B","C","E", tmp:$gte:10 and sId:"D",tmp:$gte:50 from the same collection

Working with Data
1

Hi ,
I am using the following query to get field sId:“A”,“B”,“C”,“E” which contains tmp:$gte:10.0, and the field sId:“D”, which contains the tmp:$gte:50.0, from the same collection. i want to get the sId:a,b,c,e and there tmp values are greater than 10.0 and sid:d and the tmp value are greater than 50.0 in the same object. Following queryey i am executing to get the results, but not getting accurate results, Kindly do needfull in this matter.

db.temp_log.aggregate([{$match: {
 $and: [
  {
   $or: [
    {
     sId: {
      $in: [
       'A',
       'B',
       'C',
       'E'
      ]
     }
    },
    {
     tmp: {
      $gte: 10
     }
    },
    {
     temperatureLogTime: {
      $gte: ISODate('2022-07-01T00:00:00.000Z')
     }
    },
    {
     temperatureLogTime: {
      $lte: ISODate('2022-07-31T00:00:00.000Z')
     }
    },
    {
     sId: {
      $in: [
       'D'
      ]
     }
    },
    {
     tmp: {
      $gte: 50
     }
    },
    {
     temperatureLogTime: {
      $gte: ISODate('2022-07-01T00:00:00.000Z')
     }
    },
    {
     temperatureLogTime: {
      $lte: ISODate('2022-07-31T00:00:00.000Z')
     }
    }
   ]
  }
 ]
}}, {$group: {
 _id: {
  sId: '$sId'
 },
 count: {
  $push: '$tmp'
 }
}}])

Result Set:

**1)**
1. id

:

Object

  1. sId

:

"D"
2. count

:

Array

  1. 0

:

-124.255

  2. 1

:

-126.255

  3. 2

:

-124.255

  4. 3

:

-126.255

  5. 4

:

-126.255

  6. 5

:

-126.255

  7. 6

:

-126.255

  8. 7

:

-126.255

  9. 8

:

-124.255

  10. 9

:

-95.119

  11. 10

:

-126.255

**2)**

1. _id

:

Object

  1. sId

:

"B"
2. count

:

Array

  1. 0

:

-126.255

  2. 1

:

45

  3. 2

:

45

  4. 3

:

45

  5. 4

:

45

  6. 5

:

45

  7. 6

:

45

  8. 7

:

45

  9. 8

:

45

  10. 9

:

45

  11. 10

:

45
2

Hi @MERUGUPALA_RAMES can you share some documents that we can work with while testing? Also it would be nice to see the desired output you are expecting.

3

Hi Doug_Duncan,
i am having a data in this format in my collection

/* 1 */
{
    "_id" : ObjectId("61ea5d6e2bf8817538c81cfc"),    
    "sId" : "B",
    "time" : NumberLong(1642748640),
    "typ" : 0,
    "tmp" : 28.2,
    "temperatureLogTime" : ISODate("2022-01-21T07:04:00.000Z"),
    "temperatureLogid" : "61ea5d6e2bf8817538c81cfb",
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 2 */
{
    "_id" : ObjectId("61ea5d6e2bf8817538c81cfd"),
    "sId" : "C",
    "time" : NumberLong(1642748640),
    "typ" : 0,
    "tmp" : 28.4,
    "temperatureLogTime" : ISODate("2022-01-21T07:04:00.000Z"),
    "temperatureLogid" : "61ea5d6e2bf8817538c81cfb",
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 3 */
{
    "_id" : ObjectId("61ea5d6e2bf8817538c81cfe"),    
    "sId" : "D",
    "time" : NumberLong(1642748640),
    "typ" : 0,
    "tmp" : 28.4,
    "temperatureLogTime" : ISODate("2022-01-21T07:04:00.000Z"),
    "temperatureLogid" : "61ea5d6e2bf8817538c81cfb",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 4 */
{
    "_id" : ObjectId("61ea5d6e2bf8817538c81cff"),    
    "sId" : "E",
    "time" : NumberLong(1642748640),
    "typ" : 0,
    "tmp" : 28.5,
    "temperatureLogTime" : ISODate("2022-01-21T07:04:00.000Z"),
    "temperatureLogid" : "61ea5d6e2bf8817538c81cfb",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 5 */
{
    "_id" : ObjectId("61ea5d6e2bf8817538c81d00"),    
    "sId" : "B",
    "time" : NumberLong(1642748700),
    "typ" : 0,
    "tmp" : 28.2,
    "temperatureLogTime" : ISODate("2022-01-21T07:05:00.000Z"),
    "temperatureLogid" : "61ea5d6e2bf8817538c81cfb",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 6 */
{
    "_id" : ObjectId("61ea5d6e2bf8817538c81d01"),    
    "sId" : "C",
    "time" : NumberLong(1642748700),
    "typ" : 0,
    "tmp" : 28.4,
    "temperatureLogTime" : ISODate("2022-01-21T07:05:00.000Z"),
    "temperatureLogid" : "61ea5d6e2bf8817538c81cfb",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 7 */
{
    "_id" : ObjectId("61ea5d6e2bf8817538c81d02"),    
    "sId" : "D",
    "time" : NumberLong(1642748700),
    "typ" : 0,
    "tmp" : 28.4,
    "temperatureLogTime" : ISODate("2022-01-21T07:05:00.000Z"),
    "temperatureLogid" : "61ea5d6e2bf8817538c81cfb",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 8 */
{
    "_id" : ObjectId("61ea5d6e2bf8817538c81d03"),    
    "sId" : "E",
    "time" : NumberLong(1642748700),
    "typ" : 0,
    "tmp" : 28.5,
    "temperatureLogTime" : ISODate("2022-01-21T07:05:00.000Z"),
    "temperatureLogid" : "61ea5d6e2bf8817538c81cfb",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 9 */
{
    "_id" : ObjectId("61ea5dc72bf8817538c81d05"),    
    "sId" : "B",
    "time" : NumberLong(1642748760),
    "typ" : 0,
    "tmp" : 28.2,
    "temperatureLogTime" : ISODate("2022-01-21T07:06:00.000Z"),
    "temperatureLogid" : "61ea5dc72bf8817538c81d04",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 10 */
{
    "_id" : ObjectId("61ea5dc72bf8817538c81d06"),    
    "sId" : "C",
    "time" : NumberLong(1642748760),
    "typ" : 0,
    "tmp" : 28.4,
    "temperatureLogTime" : ISODate("2022-01-21T07:06:00.000Z"),
    "temperatureLogid" : "61ea5dc72bf8817538c81d04",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 11 */
{
    "_id" : ObjectId("61ea5dc72bf8817538c81d07"),    
    "sId" : "D",
    "time" : NumberLong(1642748760),
    "typ" : 0,
    "tmp" : 28.4,
    "temperatureLogTime" : ISODate("2022-01-21T07:06:00.000Z"),
    "temperatureLogid" : "61ea5dc72bf8817538c81d04",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 12 */
{
    "_id" : ObjectId("61ea5dc72bf8817538c81d08"),    
    "sId" : "E",
    "time" : NumberLong(1642748760),
    "typ" : 0,
    "tmp" : 28.5,
    "temperatureLogTime" : ISODate("2022-01-21T07:06:00.000Z"),
    "temperatureLogid" : "61ea5dc72bf8817538c81d04",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 13 */
{
    "_id" : ObjectId("61ea5dc72bf8817538c81d09"),    
    "sId" : "B",
    "time" : NumberLong(1642748820),
    "typ" : 0,
    "tmp" : 28.2,
    "temperatureLogTime" : ISODate("2022-01-21T07:07:00.000Z"),
    "temperatureLogid" : "61ea5dc72bf8817538c81d04",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 14 */
{
    "_id" : ObjectId("61ea5dc72bf8817538c81d0a"),    
    "sId" : "C",
    "time" : NumberLong(1642748820),
    "typ" : 0,
    "tmp" : 28.4,
    "temperatureLogTime" : ISODate("2022-01-21T07:07:00.000Z"),
    "temperatureLogid" : "61ea5dc72bf8817538c81d04",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 15 */
{
    "_id" : ObjectId("61ea5dc72bf8817538c81d0b"),    
    "sId" : "D",
    "time" : NumberLong(1642748820),
    "typ" : 0,
    "tmp" : 28.4,
    "temperatureLogTime" : ISODate("2022-01-21T07:07:00.000Z"),
    "temperatureLogid" : "61ea5dc72bf8817538c81d04",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 16 */
{
    "_id" : ObjectId("61ea5dc72bf8817538c81d0c"),    
    "sId" : "E",
    "time" : NumberLong(1642748820),
    "typ" : 0,
    "tmp" : 28.5,
    "temperatureLogTime" : ISODate("2022-01-21T07:07:00.000Z"),
    "temperatureLogid" : "61ea5dc72bf8817538c81d04",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 17 */
{
    "_id" : ObjectId("61e67d390fcdd25fa73aa080"),    
    "sId" : "A",
    "time" : NumberLong(1642486290),
    "typ" : 0,
    "tmp" : 7.0,
    "temperatureLogTime" : ISODate("2022-01-18T06:11:30.000Z"),
    "temperatureLogid" : "61e67d390fcdd25fa73aa07f",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 18 */
{
    "_id" : ObjectId("61e67d390fcdd25fa73aa081"),    
    "sId" : "C",
    "time" : NumberLong(1642486290),
    "typ" : 0,
    "tmp" : 25.9,
    "temperatureLogTime" : ISODate("2022-01-18T06:11:30.000Z"),
    "temperatureLogid" : "61e67d390fcdd25fa73aa07f",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 19 */
{
    "_id" : ObjectId("61e67d390fcdd25fa73aa082"),    
    "sId" : "D",
    "time" : NumberLong(1642486290),
    "typ" : 0,
    "tmp" : 26.3,
    "temperatureLogTime" : ISODate("2022-01-18T06:11:30.000Z"),
    "temperatureLogid" : "61e67d390fcdd25fa73aa07f",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 20 */
{
    "_id" : ObjectId("61e67d390fcdd25fa73aa083"),    
    "sId" : "E",
    "time" : NumberLong(1642486290),
    "typ" : 0,
    "tmp" : 26.0,
    "temperatureLogTime" : ISODate("2022-01-18T06:11:30.000Z"),
    "temperatureLogid" : "61e67d390fcdd25fa73aa07f",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 21 */
{
    "_id" : ObjectId("61e67d470fcdd25fa73aa085"),    
    "sId" : "A",
    "time" : NumberLong(1642486451),
    "typ" : 0,
    "tmp" : 7.0,
    "temperatureLogTime" : ISODate("2022-01-18T06:14:11.000Z"),
    "temperatureLogid" : "61e67d470fcdd25fa73aa084",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 22 */
{
    "_id" : ObjectId("61e67d470fcdd25fa73aa086"),    
    "sId" : "C",
    "time" : NumberLong(1642486451),
    "typ" : 0,
    "tmp" : 25.9,
    "temperatureLogTime" : ISODate("2022-01-18T06:14:11.000Z"),
    "temperatureLogid" : "61e67d470fcdd25fa73aa084",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 23 */
{
    "_id" : ObjectId("61e67d470fcdd25fa73aa087"),    
    "sId" : "D",
    "time" : NumberLong(1642486451),
    "typ" : 0,
    "tmp" : 26.3,
    "temperatureLogTime" : ISODate("2022-01-18T06:14:11.000Z"),
    "temperatureLogid" : "61e67d470fcdd25fa73aa084",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 24 */
{
    "_id" : ObjectId("61e67d470fcdd25fa73aa088"),    
    "sId" : "E",
    "time" : NumberLong(1642486451),
    "typ" : 0,
    "tmp" : 26.0,
    "temperatureLogTime" : ISODate("2022-01-18T06:14:11.000Z"),
    "temperatureLogid" : "61e67d470fcdd25fa73aa084",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 25 */
{
    "_id" : ObjectId("61e67d530fcdd25fa73aa08a"),    
    "sId" : "A",
    "time" : NumberLong(1642486610),
    "typ" : 0,
    "tmp" : 26.0,
    "temperatureLogTime" : ISODate("2022-01-18T06:16:50.000Z"),
    "temperatureLogid" : "61e67d530fcdd25fa73aa089",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 26 */
{
    "_id" : ObjectId("61e67d530fcdd25fa73aa08b"),    
    "sId" : "C",
    "time" : NumberLong(1642486610),
    "typ" : 0,
    "tmp" : 25.9,
    "temperatureLogTime" : ISODate("2022-01-18T06:16:50.000Z"),
    "temperatureLogid" : "61e67d530fcdd25fa73aa089",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 27 */
{
    "_id" : ObjectId("61e67d530fcdd25fa73aa08c"),    
    "sId" : "D",
    "time" : NumberLong(1642486610),
    "typ" : 0,
    "tmp" : 26.3,
    "temperatureLogTime" : ISODate("2022-01-18T06:16:50.000Z"),
    "temperatureLogid" : "61e67d530fcdd25fa73aa089",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 28 */
{
    "_id" : ObjectId("61e67d530fcdd25fa73aa08d"),    
    "sId" : "E",
    "time" : NumberLong(1642486610),
    "typ" : 0,
    "tmp" : 26.0,
    "temperatureLogTime" : ISODate("2022-01-18T06:16:50.000Z"),
    "temperatureLogid" : "61e67d530fcdd25fa73aa089",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 29 */
{
    "_id" : ObjectId("61e67d600fcdd25fa73aa08f"),    
    "sId" : "A",
    "time" : NumberLong(1642486774),
    "typ" : 0,
    "tmp" : 26.0,
    "temperatureLogTime" : ISODate("2022-01-18T06:19:34.000Z"),
    "temperatureLogid" : "61e67d600fcdd25fa73aa08e",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

/* 30 */
{
    "_id" : ObjectId("61e67d600fcdd25fa73aa090"),    
    "sId" : "C",
    "time" : NumberLong(1642486774),
    "typ" : 0,
    "tmp" : 25.9,
    "temperatureLogTime" : ISODate("2022-01-18T06:19:34.000Z"),
    "temperatureLogid" : "61e67d600fcdd25fa73aa08e",    
    "_class" : "com.dipl.assets.entity.TemperatureLogDetails"
}

Where my requirement is to get the records which contains the fields for “sId”:[“A”,“B”,“C”,“E”] and there field “tmp” values should be greater than 10.0 and for “sId”:“D” the “tmp” values which contains greater than 50.0. In the above aggregation query i am able to get the results but it is not accurage, For “sId”:“A”,“B”,“C”,“E” the tmp values i am able to get minus values and for “sId”:“D” also getting the minus values in the Result set. I hope this will helpfull to understand my requirement.

4

Give this a try:

db.temp_log.aggregate(
    [
        {
            $match: {
                $and: [{
                        temperatureLogTime: {
                            $gte: ISODate('2022-01-19T00:00:00.000Z'),
                            $lte: ISODate('2022-01-29T00:00:00.000Z')
                        }
                    },
                    {
                        $or: [
                            {
                                sId: {
                                    $in: ['A', 'B', 'C', 'E']
                                },
                                tmp: {
                                    $gte: 10
                                },
                            },
                            {
                                sId: 'D',
                                tmp: {
                                    $gte: 28
                                },
                            }
                        ]
                    }
                ]
            }
        },
        {
            $group: {
                _id: {
                    sId: '$sId'
                },
                count: {
                    $push: '$tmp'
                }
            }
        }
    ]
)

The above returns the following results:

[
  { _id: { sId: 'B' }, count: [ 28.2, 28.2, 28.2, 28.2 ] },
  { _id: { sId: 'C' }, count: [ 28.4, 28.4, 28.4, 28.4 ] },
  { _id: { sId: 'E' }, count: [ 28.5, 28.5, 28.5, 28.5 ] },
  { _id: { sId: 'D' }, count: [ 28.4, 28.4, 28.4, 28.4 ] }
]

Note I modified the dates and tmp values to fit the sample data set you provided (thanks for giving a lot of data to test with).

Let us know if you have any questions.

5

Thanks for the Update Doug_Duncan,
i had executed the above query which you have provided, but unfortunatelly i am getting only “sId”:“D” and their tmp values in the result set. Kindly provide me another approach if possible, so that it will helpfull for me to get the accurate rusults.

6

I am not sure why the code does not work for you. Have you done any work to troubleshoot it? The code I provided worked on the test set you gave me as evidenced by the results I provided.

I am just a community member like you with a job, family and friends that take up most of my time. I try to spend some of the free time I have left over trying to help fellow MongoDB users out with their issues, but I don’t have unlimited time to solve all their problems.

To give me a bigger test set, I ran the following Javascript code that put in 10,000 documents with random values:

let letters = 'ABCDEFG';
let startDate = new Date('2022-01-01T00:00:00.000');
let maxTemp = 0;
let msinday = 24 * 60 * 60 * 1000

for (let i = 0; i < 10000; i++) {
	let sid = letters.charAt(Math.floor(Math.random() * letters.length));
	if (sid == 'D') {
		maxTemp = 75;
	}
	else {
		maxTemp = 15;
	}
	
	db.temp_log.insert({
		"sId": sid,
		"tmp": Math.floor(Math.random() * maxTemp),
		"temperatureLogTime": new Date(+startDate + Math.floor(Math.random() * 365) * msinday)
	})
}

I then ran the code I sent yesterday against this larger set and I got the following results:

[
  {
    _id: { sId: 'A' },
    count: [
      14, 12, 11, 13, 13, 10, 13, 13, 11,
      10, 14, 14, 12, 10, 10, 10, 14, 13,
      11, 14, 12, 12, 14, 12, 11, 11, 14,
      14, 14, 11, 14, 14, 11, 11, 14, 14
    ]
  },
  {
    _id: { sId: 'C' },
    count: [
      14, 10, 12, 12, 12, 12, 14, 11, 10,
      12, 11, 12, 12, 13, 13, 14, 14, 14,
      11, 14, 14, 11, 12, 10, 13, 14, 10,
      11, 11, 11, 11, 14, 11
    ]
  },
  {
    _id: { sId: 'B' },
    count: [
      13, 14, 13, 10, 10, 11, 10, 13, 11, 13,
      14, 14, 13, 11, 13, 11, 12, 10, 11, 11,
      12, 11, 10, 13, 13, 14, 11, 11, 13, 14,
      12, 13, 14, 10, 10, 11, 11
    ]
  },
  {
    _id: { sId: 'D' },
    count: [
      55, 66, 56, 58, 66, 71, 64, 64, 58, 54,
      72, 69, 63, 67, 53, 51, 65, 56, 62, 71,
      63, 67, 63, 60, 62, 73, 59, 71, 51, 61,
      72, 73, 50, 50, 74, 55, 73, 61, 73, 65
    ]
  },
  {
    _id: { sId: 'E' },
    count: [
      10, 12, 13, 14, 11, 14, 12, 14, 12,
      10, 10, 14, 12, 14, 13, 10, 10, 11,
      12, 10, 10, 11, 14, 12, 12, 12, 14,
      10, 14, 11, 13, 11, 10, 10, 12, 11
    ]
  }
]

This tells me that based on the parameters you provided that things are working from what I can see.

1 Like
7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.