Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Getting Error when trying to go to next 50 results for Aggregate #1529

Open
SandraArnold opened this issue Apr 11, 2018 · 22 comments
Open

Getting Error when trying to go to next 50 results for Aggregate #1529

SandraArnold opened this issue Apr 11, 2018 · 22 comments
Assignees
Projects

Comments

@SandraArnold
Copy link

@SandraArnold SandraArnold commented Apr 11, 2018

I am running the following Aggregate that was provided to me by a developer. I get the first 50 documents but when I try to go to the next 50 I get the following error:

Error: Line 1: Unexpected token .

I am running this in Robo 3T 1.2.1 against a Replica Set connection on MongoDB 3.6.3.

The aggregate is as follows:

db.Results.aggregate([ {$unwind: "$assets"}, {$match : {"assets.programType" : "Series Episode"}}, { $lookup: { from: "VodAsset", localField: "assets.pidPaid", foreignField: "moviePidPaid", as : "timsList" } }, {$unwind: "$timsList"}, { $lookup: { from: "BasePopularityXref", localField: "timsList.seriesGroupingID", foreignField: "roviVideoID", as : "BaseList" } }, { $lookup: { from: "TemporalPopularityXref", localField: "timsList.seriesGroupingID", foreignField: "roviVideoID", as : "TemporalList" } }, {$unwind : "$TemporalList"}, {$unwind : "$BaseList"}, { $project : {categoryName : 1, categoryID:1,pidPaid :"$assets.pidPaid", AssetsRating:"$assets.ranking", Modified_Temp_Ranking :{"$multiply" :["$TemporalList.ranking", 1.5454]},Modified_Base_Ranking :{"$multiply": ["$BaseList.ranking", 1.5454]}, Temporal_Ranking:"$TemporalList.ranking",Base_Ranking : "$BaseList.ranking", VODProgrammingGroupingID: "$timsList.seriesGroupingID", Temp_roviVideoID :"$TemporalList.roviVideoID", Base_roviVideoID :"$BaseList.roviVideoID", VodAssetTitle : "$timsList.longTitle"} } ])

Your help would be greatly appreciated

Sandra Arnold
Sr DBA
TiVo

@vincebowdren
Copy link

@vincebowdren vincebowdren commented Apr 17, 2018

I have run into the same problem.

I am running Robo 3T 1.2.1, against a MongoDB server running v3.4.14

The problem seems to happen if the aggregation includes a $match on a dotted field, for example:

db.default__people.aggregate([
  {$match: {"customAttributes.favourite colour" : "blue"}}
])

db.default__boundarychanges.aggregate([
  {$unwind: "$people"},
  {$match: {"people.positiveCount" : 1}}
])

db.walfordtech__people.aggregate([
  {$unwind: "$groupInstances"},
  {$match: {"groupInstances.group" : ObjectId("564b164a9408a50b00ec1c49")}}
])

In every case, the same error message is returned when fetching the next 50 rows from the result set:

Error: Line 1: Unexpected token .

By contrast, if the $match is on a non-dotted field, there is no error.

@StephanGeorg
Copy link

@StephanGeorg StephanGeorg commented May 3, 2018

Yes, can confirm this. When trying to match subfields { $match: { 'my.sub.key': true } } it returns

Error: Line 1: Unexpected token .

when requesting next n documents via pagination.

@Zeychiel
Copy link

@Zeychiel Zeychiel commented May 10, 2018

Confirmed

@kwokhou
Copy link

@kwokhou kwokhou commented Sep 4, 2018

Confirmed + 1

@tdezso
Copy link

@tdezso tdezso commented Sep 11, 2018

+1

@aershov24
Copy link

@aershov24 aershov24 commented Sep 25, 2018

Any updates on this?

@edoardotentarelli
Copy link

@edoardotentarelli edoardotentarelli commented Oct 16, 2018

+1

@ozzyknox
Copy link

@ozzyknox ozzyknox commented Oct 23, 2018

I am getting this as well, any feedback on a fix?

This is my aggregation query:

db.invoices.aggregate([
    { $match: 
        { invoiceType: "partial" 
        , createdAt: {
            $gte: ISODate('2018-05-01'),
            $lt: ISODate('2018-07-01')
        }}
    }
    
    ,{ $unwind : "$items" }
    ,{ $addFields: { 
        "items._id": "$_id",
        "items.createdAt": "$createdAt"
    } }
    ,{ $replaceRoot: { newRoot: "$items" } }
    ,{ $match: { 
        $or: [
            { description: { $in: ["Capital", "Interest", "Invoice fee"] } },
            { description: /PAYMENT REMINDER APPLIED ON/g }
        ]
       } 
     }
     ,{ $project: {
         createdAt: 1,
         description: 1,
         priceInCentsIncVat: 1
     } }
])

To resolve this, I found this issue that gives me a solution: #1058

So, I wrap the above query in a cursor and loop through it as follows:

var cursor = db.invoices.aggregate([
   ...
]);

while (cursor.hasNext()) {
   print(cursor.next());
}

At least using this approach I can get my data in a single page (I think this is limited to 10000 records or something, though, which is fine in my case).

@suhassd
Copy link

@suhassd suhassd commented Nov 13, 2018

I was getting the error even when i did not have any "dot" in match. Just guessed that it may also be because of $sort having a "dot". just did a workaround by having an extra field using $addFields

My query (Getting error)
db.order_dump.aggregate([
{
$match: { ... }
},
{
$group: { _id:{"month": {"$month":"$createdTs"},
"year": {"$year":"$createdTs"}
},
count: { $sum: 1 }
},
{
$sort:{"_id.year":-1,"_id.month":-1}
}
])
Modified query
db.order_dump.aggregate([
{
$match: { ... }
},
{
$group: { _id: {
"month": {"$month":"$createdTs"},
" year": {"$year":"$createdTs"}
},
count: { $sum: 1 }
},
{
$addFields: {
Year:"$_id.year" , Month:"$_id.month"
}
},
{
$sort:{"_id.year":-1,"_id.month":-1}
}
])

Not sure if the best way to do it but counters the error for say.

@iliatankelevich
Copy link

@iliatankelevich iliatankelevich commented Nov 26, 2018

+1

4 similar comments
@eastwook80
Copy link

@eastwook80 eastwook80 commented Dec 5, 2018

+1

@avanaur
Copy link

@avanaur avanaur commented Jan 9, 2019

+1

@MrNeocore
Copy link

@MrNeocore MrNeocore commented Jan 9, 2019

+1

@triay0
Copy link

@triay0 triay0 commented Feb 7, 2019

+1

@simsekgokhan simsekgokhan self-assigned this Feb 12, 2019
simsekgokhan added a commit that referenced this issue Feb 13, 2019
@malolaki
Copy link

@malolaki malolaki commented Mar 14, 2019

Any fixes yet for this? I am facing the same issue as well

@simsekgokhan
Copy link
Collaborator

@simsekgokhan simsekgokhan commented Mar 14, 2019

Hi @malolaki , all, we have a bug fix for aggregation queries with dotted fields in Robo 3T 1.3.

Can you guys try Robo 3T 1.3 Beta to see if it fixes for your problem? Fingers crossed.
See: Robo 3T 1.3 Beta

@dholbrook86
Copy link

@dholbrook86 dholbrook86 commented Apr 2, 2019

Hi @malolaki , all, we have a bug fix for aggregation queries with dotted fields in Robo 3T 1.3.

Can you guys try Robo 3T 1.3 Beta to see if it fixes for your problem? Fingers crossed.
See: Robo 3T 1.3 Beta

Kinda fixed. The error is suppressed. You are able to paginate. But you are not able to query more than 50 documents. If you define 0 - 100 for example, only the first 50 are returned.
1.2 - Error: Line 1: Unexpected token .
1.3 - Pagination worked, but with the 50 bug described above.
Let me know if you need more info.

@simsekgokhan
Copy link
Collaborator

@simsekgokhan simsekgokhan commented Apr 3, 2019

Hi @dholbrook86, thanks a lot for your input. It seems that fix for aggregation queries with dotted fields, broke the existing working functionality, now the problem is "batch size is not working properly with aggregation queries.". However, batch size does work when it is 50.

I have already created a ticket to be fixed in 1.4.
#1626: Robo 1.3: Batch size not working properly with aggregation queries

To prevent this kind of fixed-one-broke-other problems in the future, we will add more tests related to aggregation.

@S4lem
Copy link

@S4lem S4lem commented Jun 11, 2019

Got the same problem. Any fix ?

@rodmandi
Copy link

@rodmandi rodmandi commented Jun 18, 2019

+1

@deeTEEcee
Copy link

@deeTEEcee deeTEEcee commented Apr 3, 2020

I reverted my batchSize setting from 1000 to 50 in ~/.config/robomongo and restarted. My aggregation which does a $match + $project borks on the next pagination so I don't think that's working.

@joelabair
Copy link

@joelabair joelabair commented Apr 16, 2020

Same scenario for me cannot view more than the 1st page of results.

db.getCollection('actLog').aggregate([ 
    { '$match': { 
        accountName: { '$regex': new RegExp("xxxxxxxxx" ,"i") }
    } }
])

Clicking the next page (next 50) button produces the Error dialog

Failed to execute script.

Error: command failed: {
	"ok" : 0,
	"errmsg" : "$regex has to be a string",
	"code" : 2,
	"codeName" : "BadValue"
} : aggregate failed 
Details:
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:534:17
assert.commandWorked@src/mongo/shell/assert.js:618:16
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1062:12
DBCollection.prototype.aggregate@:1:355
@(shell):1:1

and the results panel is cleared...

Tried converting to std regex delimiters

db.getCollection('actLog').aggregate([ 
    { '$match': { 
        accountName: { '$regex': /xxxxxxxxx/i }
    } }
])

and the same error dialog pops up.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
Robo 3T 1.3
Awaiting triage
Linked pull requests

Successfully merging a pull request may close this issue.

None yet