How to get data from filtered data

Working with Data
,
1

Hi All,
I am new to MongoDB. I want to see all records from a collection where symbol is in the list of all distinct symbols which have AdjClose value less than 60.
My data looks like

{ 
    "_id" : ObjectId("604b67f16cebca011964cee6"), 
    "sym" : "COST", 
    "DateText" : "2008-01-02T12:00:00", 
    "AdjClose" : NumberDecimal("67.22"), 
    "PeriodChange" : NumberDecimal("0"), 
}
{ 
    "_id" : ObjectId("604b67f16cebca011964cee7"), 
    "sym" : "COV", 
    "DateText" : "2008-01-02T12:00:00", 
    "AdjClose" : NumberDecimal("42.4"), 
    "PeriodChange" : NumberDecimal("0"), 
}

I am trying this query.

{"sym" : 
    { "$in" : 
        [
            {
                "distinct" : "StocksTimeSeries",
                "key" : "sym",
                "query" : { "AdjClose" : { "$lt" : "50"}}
           }
       ]
   }
}

But it isn’t returning any record.

On the other hand, if I try to use shell command, it works.

db.getCollection("StocksTimeSeries").find({"sym" :{"$in" : db.getCollection('StocksTimeSeries').distinct("sym",{"AdjClose":{ "$lt" : 50 }}) }})

Considering these two records, I am expecting the result to be second record.

{ 
    "_id" : ObjectId("604b67f16cebca011964cee7"), 
    "sym" : "COV", 
    "DateText" : "2008-01-02T12:00:00", 
    "AdjClose" : NumberDecimal("42.4"), 
    "PeriodChange" : NumberDecimal("0"), 
}

Could you guys please help me.

Thanks

2

Please take a look at the following and edit your code accordingly.

It would also help if you could publish some sample documents from your collection and from the expected results.

It works in the shell because the expression db.getCollection(‘StocksTimeSeries’).distinct(“sym”,{“AdjClose”:{ “$lt” : 50 }}) get executed first and the the result is used as the value of the $in operator.

You might need to use the aggregation framework to accomplish what you want.

3

Hi Steevaj,
Thanks for the code formatting information. I have updated the post now.
Also, could you please give me some idea for for how to write a query with aggregation framework? Would the same query run if executed with collection.aggregate?
Sorry if I sound idiotic.

Thanks

5

First, I added a extra document from the list you supplied to illustrate an issue I found with the query:

The extra document is:

{
	"_id" : ObjectId("604f5846756789b5fd660e34"),
	"sym" : "COV",
	"DateText" : "2008-01-03T12:00:00",
	"AdjClose" : NumberDecimal("52.4"),
	"PeriodChange" : NumberDecimal("0")
}

DateText and AdjClose were changed. The latter so that {$lt:50} is false.

You query results in all documents with sym equals to COV, including the one with AdjClose equals to 52.4. Depending of what you want, only the one with AdjClose less than 50 or both records.

If the requirement is to only have the one with less than 50 then your query can be simplified to:

c = db.getCollection( "StocksTimeSeries" ) ;
c.find( {"AdjClose":{ "$lt" : 50 }} ) ;

If you really want what your original query will give with the additional document I added. With the aggregation framework you will need:

pipeline = [
	{
		"$match" : {
			"AdjClose" : {
				"$lt" : 50
			}
		}
	},
	{
		"$group" : {
			"_id" : "$sym"
		}
	},
	{
		"$lookup" : {
			"from" : "StocksTimeSeries",
			"localField" : "_id",
			"foreignField" : "sym",
			"as" : "list"
		}
	},
	{
		"$unwind" : "$list"
	},
	{
		"$replaceRoot" : {
			"newRoot" : "$list"
		}
	}
]
db.getCollection("StocksTimeSeries").aggregate( pipeline ) ;

If you are unfamiliar with the aggregation framework, I suggest that you take M121 from https://university.mongodb.com.