[SERVER-24947] Need a type conversion mechanism for booleans, ISODates, ObjectID Created: 07/Jul/16  Updated: 06/Apr/23  Resolved: 06/Mar/18

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

Type: Improvement Priority: Major - P3
Reporter: Adinoyi Omuya Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 13
Labels: expression, usability
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File data.png    
Issue Links:
Duplicate
duplicates SERVER-33510 Add shorthand syntax for $convert Closed
Related
related to SERVER-9406 Allow treating ObjectId type as date ... Closed
related to SERVER-11400 Need a type conversion mechanism to c... Closed
related to SERVER-29512 Convert an ObjectId to a hex string i... Closed
is related to SERVER-22781 Allow $lookup between ObjectId (_id.s... Closed
Assigned Teams:
Query
Sprint: Query 12 (04/04/16), Query 13 (04/22/16), Query 15 (06/03/16)
Participants:

 Description   

Syntax

{$toISODate: <arbitrary expression>}
{$toObjectId: <arbitrary expression>}
{$toBoolean: <arbitrary expression>}

Examples

// Example 1
> db.coll.insert([
  {_id: 0, x: 1},
  {_id: 1, x: 0}
]);
> db.coll.aggregate([{
  $project: {
    boolean: {$toBoolean: "$x"}
  }])
{_id: 0, boolean: true}
{_id: 1, boolean: false}
 
// Example 2
> db.coll.insert([
  {_id: 0, x: "5769a41ba1db3f11dba6d4d3"}
]);
> db.coll.aggregate([{
  $project: {
    objectID: {$toObjectID: "$x"}
  }])
{_id: 0, objectID: ObjectId("5769a41ba1db3f11dba6d4d3")}
 
// Example 3
> db.coll.insert([
  {_id: 0, x: "2011-01-01"}
]);
> db.coll.aggregate([{
  $project: {
    isoDate: {$toISODate: ["$x", "YYYY-MM-DD"]}
  }])
{_id: 0, isoDate: ISODate("2011-11-11T00:00:00Z")}

Notes

  • Calling $toBoolean on a boolean, $toISODate on an ISODate, or $toObjectID on an ObjectID, is a no-op.

Errors

  • If the input is of a type or value that cannot be converted: e.g. $toBoolean should only convert 0/1, $toISODate and $toObjectID should only accept strings.

Each of these operators will have an equivalent $coerceTo operator that optionally accepts a default value in cases where the vanilla version would error. In cases where no default it supplied, it would return the default value for the target type. "false" for booleans, unix epoch for dates, and an empty string for object identifiers.

{$coerceToBoolean: "xxx"} // false
{$coerceToBoolean: ["xxx", true]} // true
{$coerceToBoolean: ["5.6abc"]} // false
 
{$coerceToObjectID: ["5769a41ba1db3f11dba6d4d3"]} // ObjectID("5769a41ba1db3f11dba6d4d3")
{$coerceToObjectID: ["x", 2]} // 2
 
{$coerceToISODate: ["2011-11-11", "YYYY-MM-DD", ISODate("2014-11-11T00:00:00Z")]} // ISODate("2011-11-11T00:00:00Z")



 Comments   
Comment by Justin Seyster [ 06/Mar/18 ]

We now have $toInt, $toString, etc. conversion operators in aggregation (SERVER-33510). These operators are shorthand for the $convert operator (SERVER-32784), which additionally allows for a "default" value to use when conversions fail. Note that supported conversions include converting an objectId to a string and converting the time portion of an objectId to an ISODate. Now that we have comprehensive type conversion support, I'm closing this ticket as a duplicate.

Comment by Emre [X] [ 11/Nov/17 ]

I agree with Pavel Litvinenko. It would be nice to convert ObjectId to string in aggregation pipeline.
Here is another need that convert ObjectId to string in aggregation pipeline. I have tree structure with materialized paths. Data similar like;

db.getCollection('product-categories').aggregate([
    {
        $project: {
            _id: 1,
            name: 1,
            parentID: 1,
            path: {
                $ifNull: ["$path", {
                    $concat: [",", "$_id", ","] // Need convert ObjectId to string
                }]
            },
            depth: {
                $size: {
                    $filter: {
                        input: {
                            $ifNull: [{
                                $split: ["$path", ","]
                            }, []]
                        },
                        as: "path",
                        cond: {
                            $gt: ["$$path", ""]
                        }
                    }
                }
            }
        }
    },
    {
        $sort: {
            path: 1,
            depth: 1,
            name: 1
        }
    }
])

It's simple to sort and ready to create tree structure without any modification.

Comment by Dissatisfied Former User [ 18/Jun/17 ]

As a cross-ticket referential integrity note, SERVER-29596 (projection of sub-components of an ObjectId) has an allowance for projecting an ObjectId to its hexadecimal string representation as well as projection of the date component as a date (amongst the other components); not a generic solution for general casting, but touches on this aspect of it.

Comment by Asya Kamsky [ 08/Jun/17 ]

Date to string is already handled via existing $dateToString and date from string is a duplicate of SERVER-29208.

Comment by Asya Kamsky [ 05/Jun/17 ]

Notes on original description:

$toBoolean should only convert 0/1, $toISODate and $toObjectID should only accept strings

I think there is strong reason to allow conversion to date from ObjectId, Bson timestamp and maybe longs (treated as bson timestamp/seconds since epoch). To boolean should also accept strings "true" and "false" and arguably any non-zero number should evaluate to true.

Note that our own internal implicit conversion to boolean already works with all types.

Comment by Asya Kamsky [ 31/May/17 ]

To and from string conversion is being tracked in the related/linked ticket SERVER-11400.

Comment by Pavel Litvinenko [ 31/May/17 ]

Any plans for $toString operator?

Comment by Pavel Bortnik [ 30/May/17 ]

Could you please throw some light on the progress of solving this issue?

Comment by Asya Kamsky [ 07/Apr/17 ]

There is currently no way to convert ObjectId to another type inside aggregation pipeline. This ability is what this ticket is tracking (along with other types).

Comment by Rafael da Silva Ferreira [X] [ 07/Apr/17 ]

#fact

Comment by Pavel Litvinenko [ 06/Apr/17 ]

Unfortunately this didn't work because ObjectId() gives me new _id.
I want to convert exist _id to string, and then concatenate it with another string. For example it gives me ability to generate urls to resources in db.

Comment by Rafael da Silva Ferreira [X] [ 04/Apr/17 ]

db.getCollection('collection').aggregate(
    [
        { $match :
            { '_id' :
                { $exists:true }
            }
        },
        { $project:
            { stringId:
                { $concat: [ ObjectId().str ] }
            }
        }
    ]
)

Comment by Pavel Litvinenko [ 03/Apr/17 ]

Yes, i know about this method, but i mean a conversion of ObjectID inside of an aggregation pipeline.

Comment by Rafael da Silva Ferreira [X] [ 03/Apr/17 ]

https://docs.mongodb.com/manual/reference/method/ObjectId.toString/

Comment by Pavel Litvinenko [ 01/Mar/17 ]

It would be nice to have ability to convert ObjectId to string.

Comment by Rafael da Silva Ferreira [X] [ 30/Jan/17 ]

Any release date?

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