mongodb select all fields which is not null or empty string

Question

I have mongodb document which has data like this:

{
  "_id": "ObjectId(\"5e09db41d4ccee7f500f7326\")",
  "time": "ISODate(\"2019-12-30T09:10:53Z\")",
  "syslog_fac": 23,
  "syslog_sever": 6,
  "syslog_tag": "",
  "procid": "",
  "pid": "-",
  "status": "Allowed",
  "logtype": "Firewall Logs",
  "date": "2019-12-30",
  "Module": "01UM",
  "Desc": "Theuserloggedout.(UserName",
  "Severity_Level": "6",
  "Vsys": "public",
  "SourceIP": "10.10.0.57",
  "ParentGroup": "/netgrup.com.tr",
  "LogonTime": "2019/12/3014:07:20",
  "LogoutTime": "2019/12/3014:10:53",
  "ObversePackets": "0",
  "ObverseBytes": "0",
  "ReversePackets": "0",
  "ReverseBytes": "0",
  "LogoutReason": "AnIPaddressconflictoccurred",
  "VSys": "",
  "PolicyName": "",
  "Country": "",
  "DestinationIP": "",
  "SourcePort": "",
  "DestinationPort": "",
  "SourceZone": "",
  "DestinationZone": "",
  "User": "",
  "Protocol": "",
  "ApplicationName": "",
  "Profile": "",
  "Type": "",
  "Category": "",
  "SubCategory": "",
  "Page": "",
  "Host": "",
  "Referer": "",
  "Item": "",
  "Action": "",
  "level": "",
  "SourceNatIP": "",
  "SourceNatPort": "",
  "BeginTime": "",
  "EndTime": "",
  "SendPkts": 0,
  "SendBytes": 0,
  "RcvPkts": 0,
  "RcvBytes": 0,
  "SourceVpnID": "",
  "DestinationVpnID": "",
  "CloseReason": "",
  "Task": "",
  "Ip": "",
  "VpnName": "",
  "AuthenticationMethod": "",
  "Command": "",
  "user-name": "",
  "attack-type": "",
  "interface": "",
  "packet-count": "",
  "rate-number": "",
  "file-name": "",
  "file-type": "",
  "direction": "",
  "detect-type": "",
  "virus-name": "",
  "signature-id": "",
  "event-number": "",
  "target": "",
  "role": "",
  "user": ""
}

As you see there are a lot of fields which have an empty string or 0 , so what I want to do is to select only columns that have value except 0 or empty string.

this is my code that I am using to execute query from laravel but return nothings , when I apply that code in mongo directly I get correct data , and return exactly what I want , but return nothings from laravel ,

$data = Logss::raw(function($collection)use ($_id) {

return $collection->aggregate([
 [ '$match' => ['_id'=>'ObjectId(5e09dc63715d622be622c639)']],
     [
        '$replaceRoot'=> [
            'newRoot'=>[
                '$arrayToObject'=>[
                    '$filter'=> [
                        'input'=> [ '$objectToArray'=>'$$ROOT' ],
                        'cond'=> [
                            '$and'=> [
                                [ '$ne'=> [ '$$this.v', "" ] ],
                                [ '$ne'=>[ '$$this.v', 0 ] ]
                            ]
                        ]
                    ]
                ]
            ]
        ]
   ]


]);
});

I am not sure how you are finding this document (search) but you can do all this in your projection with conditionals — JBone
– JBone, Commented Dec 30, 2019 at 14:01

mickl · Accepted Answer · 2019-12-31 07:05:34Z

1

You can run below query:

db.collection.aggregate([
    { $match: { _id: ObjectId(...) } },
    {
        $replaceRoot: {
            newRoot: {
                $arrayToObject: {
                    $filter: {
                        input: { $objectToArray: "$$ROOT" },
                        cond: {
                            $and: [
                                { $ne: [ "$$this.v", "" ] },
                                { $ne: [ "$$this.v", 0 ] }
                            ]
                        }
                    }
                }
            }
        }
    }
])

The idea is pretty simple: you start with $objectToArray to get all the fields as an array. Then you can run $filter to remove all zeros and whitespaces and convert that array back to an object $arrayToObject promoting it to the root level ($replaceRoot).

Mongo Playground

edited Dec 31, 2019 at 7:05

answered Dec 30, 2019 at 14:01

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

stack over Over a year ago

thank you for your replay, I applied this code in my database and worked fine , but what if I want to get for only one record (where id=23423423432) it not working when adding match condition

mickl Over a year ago

@stackover modifed my answer, just use $match before $replaceRoot

Collectives™ on Stack Overflow

mongodb select all fields which is not null or empty string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related