Help converting query to not use Javascript

Working with Data
1

per Cannot run server-side javascript without the javascript engine enabled

I’m looking for help modifying a query to not use javascript. this one uses a function and not a db.eval.
This portion of a query in metabase seems to prevent it from working with digital ocean’s mongodb, metabase/modules/drivers/mongo/src/metabase/driver/mongo.clj at b6e05875da6694a3958631970f573beddd73d2de · metabase/metabase · GitHub

anyone got any idead how to convert it?

1 Like
2

Hey Darrel,

From my understanding of the problem, the issue is likely due to Metabase using a $function operator in aggregation queries, which requires server-side JavaScript execution, something DigitalOcean’s MongoDB doesn’t allow.

Since $function is the problem, we need to rewrite the query to remove JavaScript execution. You have a few options:

  1. Replace $function with a native MongoDB aggregation operator

  2. Use a client-side transformation

Could you share the specific MongoDB query that fails? That would help in converting it to a JavaScript-free alternative

Thanks,
Tim

3

We’re trying to determine the subtype of BinData objects. It can be done with the .type field when you pass the BinData object into a JS function, but from what I can tell it isn’t possible to get the subtype from the BinData BSON object, with the .type field or any other method. For example:

db.coll.aggregate([
    {
        "$project": {
            "uuid_type": {  "$type": UUID() },
            "js_uuid_subtype": {
                "$function": {
                    "body": "function(uuid) { return uuid.type; }",
                    "args": [  UUID() ],
                    "lang": "js"
                }
            },
            "bson_uuid_subtype": UUID().type
        }
    }
])

[
  {
    _id: ObjectId('67dc5784677dddbefba00aa2'),
    uuid_type: 'binData',
    js_uuid_subtype: 4,
    bson_uuid_subtype: null
  }
]

If you could provide a way to determine the subtype of a BinData BSON object without passing it into a JS function, or confirm that it isn’t possible, that would be great.

4

The query is in the link, but I can paste it here for posterity. I’ve preserved the surrounding clojure which defines multiple queries for context. The query with the JS is assigned to the initial-items variable. The type-alias inside the $map which uses the ternary to return ‘uuid’ or type is the JS that would be nice to replace. I’m neither a clojure developer nor a mongodb expert, so bear with any misinterpretations I may have made.

  "To understand how this works, see the comment block below for a rough translation of this query into Clojure."
  [& {:keys [collection-name sample-size max-depth]}]
  (let [start-n       (quot sample-size 2)
        end-n         (- sample-size start-n)
        sample        [{"$sort" {"_id" 1}}
                       {"$limit" start-n}
                       {"$unionWith"
                        {"coll" collection-name
                         "pipeline" [{"$sort" {"_id" -1}}
                                     {"$limit" end-n}]}}]
        initial-items [{"$project" {"path" "$ROOT"
                                    "kvs" {"$map" {"input" {"$objectToArray" "$$ROOT"}
                                                   "as"    "item"
                                                   "in"    {"k"          "$$item.k"
                                                            "object"     {"$cond" {"if"   {"$eq" [{"$type" "$$item.v"} "object"]}
                                                                                   "then" "$$item.v"
                                                                                   "else" nil}}
                                                            "type"       {"$type" "$$item.v"}
                                                            "type-alias" {"$function" {"body" "function(val, type) { return (type == 'binData' && val.type == 4) ? 'uuid' : type; }"
                                                                                       "args" ["$$item.v" {"$type" "$$item.v"}]
                                                                                       "lang" "js"}}}}}}}
                       {"$unwind" {"path" "$kvs", "includeArrayIndex" "index"}}
                       {"$project" {"path"       "$kvs.k"
                                    "result"     {"$literal" false}
                                    "type"       "$kvs.type"
                                    "type-alias" "$kvs.type-alias"
                                    "index"      1
                                    "object"     "$kvs.object"}}]]
1 Like
5

@Tim_Kelly Wondering if you/someone else is able to confirm if

  1. Replace $function with a native MongoDB aggregation operator

is possible for determining subtypes of binData? :slightly_smiling_face: