[SERVER-73868] [Federated db] ISODate not working on a View with $project Created: 10/Feb/23  Updated: 10/Feb/23  Resolved: 10/Feb/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 5.0.14
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Anthony Viaene Assignee: Yuan Fang
Resolution: Done Votes: 0
Labels: Bug
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File Screenshot 2023-02-10 at 10.19.32.png    
Operating System: ALL
Steps To Reproduce:

add a db from a atalas cluster to federated database.

create a view on that db and in the pipeline you use any date command like ISODate or new Date.
 
error: Data Lake storage config invalid (errors: [[database 'db1': view 'test_dates': pipeline failed to parse: invalid JSON input. Position: 38. Character: I]]).
 
example pipeline:

 
[{
"$project":{
"aborted_at":{
"$cond":[

{ "$gt":[ "$aborted_at",ISODate("0001-01-01T00:00:00.000+0000") ] }

,

{ "$toDate":"$aborted_at" }

,
null
]
}
}}]

Participants:

 Description   

Unable to use ISOdate or new date in $project of a view on a federated databse. 

 

as a user i want to create a view in a federated database based on a collection of a atlas cluster. Inside this view I use the pipeline $project. Everything works fine unless you want to use dates like ISODate or new Date. Then you get following error.

 
Data Lake storage config invalid (errors: [[database 'db1': view 'test_dates': pipeline failed to parse: invalid JSON input. Position: 38. Character: I]]).
 



 Comments   
Comment by Yuan Fang [ 10/Feb/23 ]

Hi anthonyviaene@gmail.com,

Thank you for your report. If I understand correctly, you intended to create a view using an aggregation pipeline.

create a view on that db and in the pipeline, you use any date command like ISODate or new Date.

both ISODate() and new Date() are mongosh Methods for creating Date objects. However, I don't believe they are valid for use in the $project stage of an aggregation pipeline.

Furthermore, the error message:

pipeline failed to parse: invalid JSON input. Position: 38. Character: I" suggests that there is a syntax error in the JSON input at position 38 and the character causing the error is 'I'.

indicates that there is a syntax error in the JSON input at position 38 and the character causing the error is 'I'. As previously stated, "ISODate()" is not a valid function in MongoDB's aggregation pipeline which resulted in a syntax error. To resolve the issue, I would suggest you check the operator $dateFromString (aggregation).

For any further questions, we'd like to encourage you to start by asking our community for help by posting on the MongoDB Developer Community Forums. If the discussion there leads you to suspect a bug in the MongoDB server, then we'd want to investigate it as a possible bug here in the SERVER project.

Regards,
Yuan

Generated at Thu Feb 08 06:25:51 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.