How to view the query that is being executed

[sevenpixels]

Is there a way to view the JSON that is being executed on the server? I'd like to output the query to help in debugging but I don't see a way to be able to view the JSON that was generated.

[jmikola]

The mongodb extension has an Application Performance Monitoring (APM) feature that you can use to register an event listener for each database command issued by the driver. There are currently events for commands starting, succeeding, and failing. The CommandStartedEvent provides access to the command document, which may be what you're looking for.

A small nit: the document is BSON, not JSON. The event object will expose the document as a PHP object, so if you do want to convert it to JSON for logging purposes you can use the BSON functions to first convert it to BSON bytes and then convert that to JSON (which can produce Extended JSON). Alternatively, you may prefer to just pass the PHP object to json_encode().

[sevenpixels]

Ah ya, sorry BSON, not JSON.

I'm gonna be honest here, I got a query working in the PHP library, but I can't seem to write the same query and use it in the command line or Navicat. I was hoping I could spit out the query so I could see how it was formatted so I could utilize it outside of PHP. I attempted to but get an error back Error: collection.updateOne requires update operator

This is the PHP call. It gets all documents in project 12, sets played to 1, then sets low score to the value of score if it's lower, then sets the high score to the value of score if it's higher.

        $collection->updateMany(
            ['project' => 12],
            [
                ['$set' => [
                    'played' => 1
                ]],
                ['$set' => [
                    'low_score' => ['$min' => ['$score', '$low_score']],
                    'high_score' => ['$max' => ['$score', '$max_score']],
                ]]
            ]
        );

[jmikola]

The following example demonstrates how to dump the update command:

<?php

require_once 'vendor/autoload.php';

class CommandLogger implements MongoDB\Driver\Monitoring\CommandSubscriber
{
    public function commandStarted(MongoDB\Driver\Monitoring\CommandStartedEvent $event)
    {
        if ('update' === $event->getCommandName()) {
            echo MongoDB\BSON\toRelaxedExtendedJSON(MongoDB\BSON\fromPHP($event->getCommand()));
        }
    }

    public function commandSucceeded(MongoDB\Driver\Monitoring\CommandSucceededEvent $event)
    {
    }

    public function commandFailed(MongoDB\Driver\Monitoring\CommandFailedEvent $event)
    {
    }
}

MongoDB\Driver\Monitoring\addSubscriber(new CommandLogger);

$client = new MongoDB\Client();
$collection = $client->test->foo;

$collection->updateMany(
    ['project' => 12],
    [
        ['$set' => ['played' => 1]],
        ['$set' => [
            'low_score' => ['$min' => ['$score', '$low_score']],
            'high_score' => ['$max' => ['$score', '$max_score']],
        ]],
    ]
);

Running this locally produced the following output for me:

$ php gh743.php 
{ "update" : "foo", "ordered" : true, "$db" : "test", "lsid" : { "id" : { "$binary" : { "base64": "R2hoaaXfTYKxAQzPKnc9Kg==", "subType" : "04" } } }, "$clusterTime" : { "clusterTime" : { "$timestamp" : { "t" : 1588691277, "i" : 5 } }, "signature" : { "hash" : { "$binary" : { "base64": "AAAAAAAAAAAAAAAAAAAAAAAAAAA=", "subType" : "00" } }, "keyId" : 0 } }, "updates" : [ { "q" : { "project" : 12 }, "u" : [ { "$set" : { "played" : 1 } }, { "$set" : { "low_score" : { "$min" : [ "$score", "$low_score" ] }, "high_score" : { "$max" : [ "$score", "$max_score" ] } } } ], "upsert" : false, "multi" : true } ] }

I can't speak for Navicat, but if you were attempting to run the same query in the MongoDB shell you should have no problem doing so via the db.collection.updateMany method. The manual includes an example under Update with an Aggregation Pipeline.

[sevenpixels]

Ok thanks! Since I was able to get the query I could run it via the command line no problem, but Navicat does not like it. I think it might be an issue on their end with how they handle aggregation piplines in the updateMany/update methods. I'm gonna reach out to them.