.explain() result in ParseSwift

Hi,

I would like to investigate query performance and was able to set a simple function that returns array of objects.

Parse.Cloud.define("newProfiles", async (request) => {
    const basicQuery = new Parse.Query("PrsProfile");
    basicQuery.lessThanOrEqualTo("ag", 26);
    basicQuery.greaterThanOrEqualTo("ag", 23);
    const results = await basicQuery.find({ sessionToken: request.user.getSessionToken() });
    return results;
  });

This works as intended and I have PrsProfile object already as a struct, so decoding is not an issue. But when I chain basicQuery.explain() in to the function, it returns result that I can’t read well:

error: The data couldn’t be read because it isn’t in the correct format. Format: Optional("{\“result\”:{\“queryPlanner\”:{\“mongosPlannerVersion\”:1,\“winningPlan\”:{\“stage\”:\“SINGLE_SHARD\”,\“shards\”:[{\“shardName\”:\“rs6\”,\“connectionString\”:\“rs6/MongoRS601A.back4app.com:27018,MongoRS602A.back4app.com:27018\”,\“serverInfo\”:{\“host\”:\“MongoRS601A.back4app.com\”,\“port\”:27018,\“version\”:\“3.6.8-2.0\”,\“gitVersion\”:\“1fb3fbd45602ef799b4053edbe1f2748ab97eebe\”},\“plannerVersion\”:1,\“namespace\”:\“a1b5c903c7814f7a836a269dc63ff84f.PrsProfile\”,\“indexFilterSet\”:false,\“parsedQuery\”:{\"$and\":[{\“ag\”:{\"$lte\":26}},{\“ag\”:{\"$gte\":23}},{\"_rperm\":{\"$in\":[null,\"\",\“zyLHKLV5Ll\”]}}]},\“winningPlan\”:{\“stage\”:\“LIMIT\”,\“limitAmount\”:100,\“inputStage\”:{\“stage\”:\“COLLSCAN\”,\“filter\”:{\"$and\":[{\“ag\”:{\"$lte\":26}},{\“ag\”:{\"$gte\":23}},{\"_rperm\":{\"$in\":[null,\"\",\“zyLHKLV5Ll\”]}}]},\“direction\”:\“forward\”}},\“rejectedPlans\”:}]}},\“executionStats\”:{\“nReturned\”:1,\“executionTimeMillis\”:3,\“totalKeysExamined\”:0,\“totalDocsExamined\”:1,\“executionStages\”:{\“stage\”:\“SINGLE_SHARD\”,\“nReturned\”:1,\“executionTimeMillis\”:3,\“totalKeysExamined\”:0,\“totalDocsExamined\”:1,\“totalChildMillis\”:0,\“shards\”:[{\“shardName\”:\“rs6\”,\“executionSuccess\”:true,\“executionStages\”:{\“stage\”:\“LIMIT\”,\“nReturned\”:1,\“executionTimeMillisEstimate\”:0,\“works\”:3,\“advanced\”:1,\“needTime\”:1,\“needYield\”:0,\“saveState\”:0,\“restoreState\”:0,\“isEOF\”:1,\“invalidates\”:0,\“limitAmount\”:100,\“inputStage\”:{\“stage\”:\“COLLSCAN\”,\“filter\”:{\"$and\":[{\“ag\”:{\"$lte\":26}},{\“ag\”:{\"$gte\":23}},{\"_rperm\":{\"$in\":[null,\"*\",\“zyLHKLV5Ll\”]}}]},\“nReturned\”:1,\“executionTimeMillisEstimate\”:0,\“works\”:3,\“advanced\”:1,\“needTime\”:1,\“needYield\”:0,\“saveState\”:0,\“restoreState\”:0,\“isEOF\”:1,\“invalidates\”:0,\“direction\”:\“forward\”,\“docsExamined\”:1}}}]},\“allPlansExecution\”:[{\“shardName\”:\“rs6\”,\“allPlans\”:}]},\“ok\”:1}}")")

I am using following definition with ParseSwift SDK:

let findQuery = Cloud(functionJobName: "newProfiles")
        findQuery.runFunction { result in
            switch result {
            case .success(let results):
                print("Response from cloud function: \(results))")
            case .failure(let error):
                print("Error calling cloud function: \(error)")
            }
        }

The typealias in the Cloud struct is for me the unknown…

import** ParseSwift

//: Create your own value typed `ParseCloud` type.
struct** Cloud: ParseCloud {

//: Return type of your Cloud Function
typealias ReturnType = ????

//: These are required for Object
var functionJobName: String

}

Does anyone has a wrapper or useful trick, how to print explain() result in Xcode in a better way?

Thank you!

Swift is strongly typed so you need to know the type you are returning. Since you’re calling explain from cloud code, you should know what it’s returning. Note that queries in ParseSwift are able to be explained as well:

The simple solution is to use AnyDecodable (like the playground example above), in the your particular case:

struct ExplainType: Decodable {
  let result: AnyDecodable
}

struct Cloud: ParseCloud {

//: Return type of your Cloud Function
typealias ReturnType = ExplainType

//: These are required for Object
var functionJobName: String

}

This is replicating the AnyResultResponse type:

You can see all of the generic return types ParseSwift uses in the file above.

Once you have AnyDecodable , you can cast the value to what you expect. I don’t use mongo, so I don’t know the types its explain returns, but you can look at the results you posted. You can try below to get through the first layer:

findQuery.runFunction { result in
            switch result {
            case .success(let results):
                print("Response from cloud function: \(results.result))")
                //If you know the type, use that instead of Any. If Any doesn't work use AnyDecodable again
                guard let explainDictionary = results.result.value as? [String: Any] else {
                        return
                 }
                print("Casted explain: \(explainDictionary)"
            case .failure(let error):
                print("Error calling cloud function: \(error)")
            }
        }
1 Like

Hi Corey,

thank you for your response! I was able to decode the response from cloud function as you mentioned by defining the return type as AnyDecodable in the Cloud struct:

func fetchUserViaCloudCode() {
        let findQuery = Cloud(functionJobName: "newProfiles")
        findQuery.runFunction { result in
            switch result {
            case .success(let results):
                guard let explainDictionary = results.value as? [String: Any] else {
                    return
                }
                let executionStats = explainDictionary["executionStats"] as? [String: Any]
                for key in executionStats!.keys {
                    switch key {
                    case "totalKeysExamined", "totalDocsExamined", "nReturned", "executionTimeMillis": print("-> \(key) \(executionStats![key]!)")
                    default: ()
                    }
                    
                }
            case .failure(let error):
                print("Error calling cloud function: \(error)")
            }
        }
    }

unfortunately, when I follow your playgrounds example and try to cast the result of a “find” function as AnyDecodable it does not work and it catches error decoding due to format:

finalQuery.find(explain: true) { (result: Result<[AnyDecodable], ParseError>) in
            switch result {
            case .success(let results):
                guard let explainDictionary = results.first?.value as? [String: Any] else {
                    return
                }
                let executionStats = explainDictionary["executionStats"] as? [String: Any]
                for key in executionStats!.keys {
                    switch key {
                    case "totalKeysExamined", "totalDocsExamined", "nReturned", "executionTimeMillis": print("-> \(key) \(executionStats![key]!)")
                    default: ()
                    }

                }
            case .failure(let error):
                print("Error calling cloud function: \(error)")
            }
        }

If I try to set it as no array result I get an compiler error as he expects array

finalQuery.find(explain: **true**) { (result: Result<AnyDecodable, ParseError>) **in**

I apologise if I missed any basic swift declaration login, I am still an beginner in a programming world.

Can you post the full error?

My guess is this is a bug on parse-server and not Parse-Swift and someone on the server team should probably pick up the Q&A here…

I say this because it doesn’t appear when using explain in Postgres. When I added explain to the Postgres adapter on the parse-server (PR 6506), I made sure it’s always returned in JSON format so it can always be decoded:

Looking at the explain results you’ve shown along with some of my own testing, the Mongo version of explain isn’t always returning a JSON version of explain. CloudCode and the JS SDK most likely show results fine in because JS isn’t a strongly typed language and isn’t trying to decode JSON to specific types. Conversely, Swift is, and expects everything from the server to be in a “correct” JSON format or else it throws errors. The mongo version of explain doesn’t appear to be in a correct JSON format.

Update: the problem is a mongo explain isn’t being returned within a JSON array.

If what I said above is true (the server team will need to confirm), there will most likely need to be a fix in this file parse-server/MongoStorageAdapter.js at master · parse-community/parse-server · GitHub. I found some info about explain in mongo (but I don’t use it, so I won’t be able to provide any additional insight beyond this): explain — MongoDB Manual

A workaround could be to use your Cloud Code explain function and only return the fields your care about to Parse-Swift.

Thank you for a great support, Corey! As I will move this query function in the cloud code at the end anyway, I am continuing with the cloud code explain.

1 Like

Posting additional results (using a simple geo query) for the server team. An additional question would be is explain executing or the command here (it looks like it might be, but I’m unsure). I would think the default would be not to execute the query/command:

error: The data couldn’t be read because it isn’t in the correct format. Format: Optional(\"{\\\"results\\\":{\\\"queryPlanner\\\":{\\\"plannerVersion\\\":1,\\\"namespace\\\":\\\"parse_hipaa.GameScore\\\",\\\"indexFilterSet\\\":false,\\\"parsedQuery\\\":{\\\"$and\\\":[{\\\"score\\\":{\\\"$gt\\\":9}},{\\\"_rperm\\\":{\\\"$in\\\":[null,\\\"*\\\"]}},{\\\"location\\\":{\\\"$nearSphere\\\":[-30,40]}}]},\\\"winningPlan\\\":{\\\"stage\\\":\\\"LIMIT\\\",\\\"limitAmount\\\":1,\\\"inputStage\\\":{\\\"stage\\\":\\\"FETCH\\\",\\\"filter\\\":{\\\"$and\\\":[{\\\"_rperm\\\":{\\\"$in\\\":[null,\\\"*\\\"]}},{\\\"score\\\":{\\\"$gt\\\":9}}]},\\\"inputStage\\\":{\\\"stage\\\":\\\"GEO_NEAR_2D\\\",\\\"keyPattern\\\":{\\\"location\\\":\\\"2d\\\"},\\\"indexName\\\":\\\"location_2d\\\",\\\"indexVersion\\\":2,\\\"inputStage\\\":{\\\"stage\\\":\\\"FETCH\\\",\\\"inputStage\\\":{\\\"stage\\\":\\\"IXSCAN\\\",\\\"keyPattern\\\":{\\\"location\\\":\\\"2d\\\"},\\\"indexName\\\":\\\"location_2d\\\",\\\"isMultiKey\\\":false,\\\"isUnique\\\":false,\\\"isSparse\\\":false,\\\"isPartial\\\":false,\\\"indexVersion\\\":2,\\\"direction\\\":\\\"forward\\\",\\\"indexBounds\\\":{\\\"location\\\":[\\\"[BinData(128, 69D89D89D8380000), BinData(128, 69D89D89D83BFFFF)]\\\",\\\"[BinData(128, 69D89D89D83C0000), BinData(128, 69D89D89D83FFFFF)]\\\",\\\"[BinData(128, 69D89D89D86A0000), BinData(128, 69D89D89D86AFFFF)]\\\",\\\"[BinData(128, 69D89D89D86B8000), BinData(128, 69D89D89D86BBFFF)]\\\",\\\"[BinData(128, 69D89D89D8870000), BinData(128, 69D89D89D887FFFF)]\\\",\\\"[BinData(128, 69D89D89D88C0000), BinData(128, 69D89D89D88FFFFF)]\\\",\\\"[BinData(128, 69D89D89D8900000), BinData(128, 69D89D89D89FFFFF)]\\\",\\\"[BinData(128, 69D89D89D8A55000), BinData(128, 69D89D89D8A55FFF)]\\\",\\\"[BinData(128, 69D89D89D8B00000), BinData(128, 69D89D89D8B3FFFF)]\\\",\\\"[BinData(128, 69D89D89D8B40000), BinData(128, 69D89D89D8B7FFFF)]\\\",\\\"[BinData(128, 69D89D89D8BC0000), BinData(128, 69D89D89D8BCFFFF)]\\\",\\\"[BinData(128, 69D89D89D8BD0000), BinData(128, 69D89D89D8BDFFFF)]\\\",\\\"[BinData(128, 69D89D89D8C00000), BinData(128, 69D89D89D8CFFFFF)]\\\",\\\"[BinData(128, 69D89D89D8E00000), BinData(128, 69D89D89D8E3FFFF)]\\\",\\\"[BinData(128, 69D89D89D8E40000), BinData(128, 69D89D89D8E4FFFF)]\\\",\\\"[BinData(128, 69D89D89D8E80000), BinData(128, 69D89D89D8E80FFF)]\\\"]}}}}}},\\\"rejectedPlans\\\":[]},\\\"executionStats\\\":{\\\"executionSuccess\\\":true,\\\"nReturned\\\":1,\\\"executionTimeMillis\\\":0,\\\"totalKeysExamined\\\":1,\\\"totalDocsExamined\\\":2,\\\"executionStages\\\":{\\\"stage\\\":\\\"LIMIT\\\",\\\"nReturned\\\":1,\\\"executionTimeMillisEstimate\\\":0,\\\"works\\\":5,\\\"advanced\\\":1,\\\"needTime\\\":3,\\\"needYield\\\":0,\\\"saveState\\\":0,\\\"restoreState\\\":0,\\\"isEOF\\\":1,\\\"limitAmount\\\":1,\\\"inputStage\\\":{\\\"stage\\\":\\\"FETCH\\\",\\\"filter\\\":{\\\"$and\\\":[{\\\"_rperm\\\":{\\\"$in\\\":[null,\\\"*\\\"]}},{\\\"score\\\":{\\\"$gt\\\":9}}]},\\\"nReturned\\\":1,\\\"executionTimeMillisEstimate\\\":0,\\\"works\\\":4,\\\"advanced\\\":1,\\\"needTime\\\":3,\\\"needYield\\\":0,\\\"saveState\\\":0,\\\"restoreState\\\":0,\\\"isEOF\\\":0,\\\"docsExamined\\\":1,\\\"alreadyHasObj\\\":1,\\\"inputStage\\\":{\\\"stage\\\":\\\"GEO_NEAR_2D\\\",\\\"nReturned\\\":1,\\\"executionTimeMillisEstimate\\\":0,\\\"works\\\":4,\\\"advanced\\\":1,\\\"needTime\\\":3,\\\"needYield\\\":0,\\\"saveState\\\":0,\\\"restoreState\\\":0,\\\"isEOF\\\":0,\\\"keyPattern\\\":{\\\"location\\\":\\\"2d\\\"},\\\"indexName\\\":\\\"location_2d\\\",\\\"indexVersion\\\":2,\\\"searchIntervals\\\":[{\\\"minDistance\\\":0,\\\"maxDistance\\\":3.5829649157275663,\\\"maxInclusive\\\":false,\\\"nBuffered\\\":1,\\\"nReturned\\\":1}],\\\"inputStage\\\":{\\\"stage\\\":\\\"FETCH\\\",\\\"nReturned\\\":1,\\\"executionTimeMillisEstimate\\\":0,\\\"works\\\":2,\\\"advanced\\\":1,\\\"needTime\\\":0,\\\"needYield\\\":0,\\\"saveState\\\":0,\\\"restoreState\\\":0,\\\"isEOF\\\":1,\\\"docsExamined\\\":1,\\\"alreadyHasObj\\\":0,\\\"inputStage\\\":{\\\"stage\\\":\\\"IXSCAN\\\",\\\"nReturned\\\":1,\\\"executionTimeMillisEstimate\\\":0,\\\"works\\\":2,\\\"advanced\\\":1,\\\"needTime\\\":0,\\\"needYield\\\":0,\\\"saveState\\\":0,\\\"restoreState\\\":0,\\\"isEOF\\\":1,\\\"keyPattern\\\":{\\\"location\\\":\\\"2d\\\"},\\\"indexName\\\":\\\"location_2d\\\",\\\"isMultiKey\\\":false,\\\"isUnique\\\":false,\\\"isSparse\\\":false,\\\"isPartial\\\":false,\\\"indexVersion\\\":2,\\\"direction\\\":\\\"forward\\\",\\\"indexBounds\\\":{\\\"location\\\":[\\\"[BinData(128, 69D89D89D8380000), BinData(128, 69D89D89D83BFFFF)]\\\",\\\"[BinData(128, 69D89D89D83C0000), BinData(128, 69D89D89D83FFFFF)]\\\",\\\"[BinData(128, 69D89D89D86A0000), BinData(128, 69D89D89D86AFFFF)]\\\",\\\"[BinData(128, 69D89D89D86B8000), BinData(128, 69D89D89D86BBFFF)]\\\",\\\"[BinData(128, 69D89D89D8870000), BinData(128, 69D89D89D887FFFF)]\\\",\\\"[BinData(128, 69D89D89D88C0000), BinData(128, 69D89D89D88FFFFF)]\\\",\\\"[BinData(128, 69D89D89D8900000), BinData(128, 69D89D89D89FFFFF)]\\\",\\\"[BinData(128, 69D89D89D8A55000), BinData(128, 69D89D89D8A55FFF)]\\\",\\\"[BinData(128, 69D89D89D8B00000), BinData(128, 69D89D89D8B3FFFF)]\\\",\\\"[BinData(128, 69D89D89D8B40000), BinData(128, 69D89D89D8B7FFFF)]\\\",\\\"[BinData(128, 69D89D89D8BC0000), BinData(128, 69D89D89D8BCFFFF)]\\\",\\\"[BinData(128, 69D89D89D8BD0000), BinData(128, 69D89D89D8BDFFFF)]\\\",\\\"[BinData(128, 69D89D89D8C00000), BinData(128, 69D89D89D8CFFFFF)]\\\",\\\"[BinData(128, 69D89D89D8E00000), BinData(128, 69D89D89D8E3FFFF)]\\\",\\\"[BinData(128, 69D89D89D8E40000), BinData(128, 69D89D89D8E4FFFF)]\\\",\\\"[BinData(128, 69D89D89D8E80000), BinData(128, 69D89D89D8E80FFF)]\\\"]},\\\"keysExamined\\\":1,\\\"seeks\\\":1,\\\"dupsTested\\\":0,\\\"dupsDropped\\\":0}}}}},\\\"allPlansExecution\\\":[]},\\\"serverInfo\\\":{\\\"host\\\":\\\"33fdd198c5e7\\\",\\\"port\\\":27017,\\\"version\\\":\\\"4.4.4-6\\\",\\\"gitVersion\\\":\\\"f3dd4bc7c7500705a537de40bb4d6127ba498bd3\\\"},\\\"ok\\\":1}}\")

I don’t believe some of the fields above can be decoded properly in JSON, but I could be wrong.

@dplewis @davimacedo @dblythy @Manuel it looks like there’s a bug on the server with the mongo version of explain (you can ignore my JSON comments above as the fix below is the appropriate one). The problem is the results of a mongo explain are:

internal struct AnyResultsResponse<U: Decodable>: Decodable {
    let results: U //Technically, when using results (plural), it should be an array
    //let results: [U] This is what it should be
}

The solution would be to wrap the result of a mongo explain within an array. The return types of first, find, count, etc. in a regular query always returns results: [U], but the Mongo explain isn’t following this pattern. If you look at the postgres PR I linked in this comment, it does it correctly.

@lsmilek1 if you want, you can take my comments here and open an issue on the parse-server GitHub repo. You can ignore my JSON comments.

So the JSON parsing server side of the explain response from MongoDB is faulty?

Not the JSON, I was wrong about that.

The part that’s faulty is the result of a mongo explain needs to be added into an array to fit the pattern of a query return type. It’s currently being sent as a single element.

The fix is probably a one-liner. Me skimming the mongo adapter, the problem is probably the line below:

My guess is objects from a mongo explain isn’t array, but was assumed to be, so it needs to be added to an array. I believe I ran into this problem when I added the postgres explain but caught it when I was writing the testcases.

Got it, that seems like an easy fix, although a breaking change for developers who parse the current output, right?

Yup, will definitely be a breaking change for those who use it.

It will probably also break some of the mongo explain testcases, but the fix in those should be to take the first element of the array before parsing

It is actually possible that this only affects certain MongoDB versions.

I made a PR to adapt the hint tests to work with MongoDB 4.4 because I think the DB response changed, see spec/ParseQuery.hint.spec.js.

Maybe we only need to adapt the response for MongoDB >= 4.4, but haven’t looked into it.

It was giving me an error on 4.2 also, but I didn’t verify if it was the same problem. I will check soon. I only checked 4.4 and 4.2

Update: Verified it’s the same problem on 4.2

Also, the original error reported here: .explain() result in ParseSwift shows mongo version 3.6.8-2.0

1 Like

Thanks, well investigated!