How the Filter works?

[saptaji]

I am planning to build a filter with various conditions (and, or, etc). How I can learn more about mdbc filters.

This one NOT working:

$filter = "@{'Agent.DeviceName' = [regex]'^Microsoft.*'}"

Get-MdbcData -Filter $filter -As PS

This one works:

Get-MdbcData -As PS -Filter @{'Agent.DeviceName' = [regex]'^Microsoft.*'}

I also tested some filters with 'Compass'. It works with Compass but not mdbc.

{"Agent.DeviceName": {$regex: /^Microsoft.*/}}

[nightroman]

See MongoDB documentation -- https://docs.mongodb.com/manual/reference/method/db.collection.find/ Mdbc filters are the same. You may literally use the same JSON expressions or their equivalent PS hashtables.

[nightroman]

Example with regex:

https://github.com/nightroman/Mdbc/blob/117e27ca0e528f5d8be0c291d83492d49c43736e/Tests/HowTo.test.ps1#L44-L67

[nightroman]

Here is your example, working:

Import-Module Mdbc

Connect-Mdbc . test test -NewCollection

@(
    @{ Agent = @{DeviceName = 'Microsoft foo bar' } }
    @{ Agent = @{DeviceName = 'Google foo bar' } }
) | Add-MdbcData

"*** all ***"
Get-MdbcData -As PS

"*** filter ***"
Get-MdbcData -As PS -Filter @{'Agent.DeviceName' = [regex]'^Microsoft.*' }

output

*** all ***
_id                      Agent
---                      -----
6065fb21a26f5905331b0efe @{DeviceName=Microsoft foo bar}
6065fb21a26f5905331b0eff @{DeviceName=Google foo bar}

*** filter ***
6065fb21a26f5905331b0efe @{DeviceName=Microsoft foo bar}

[nightroman]

Your example works. What is the issue/question? How to write or/and filters? Have you tried some JSON filters (like in MongoDB, see its docs)? They should work. Or you do not know how to translate JSON filters to PS hashtable filters? Then please ask about these particular filters.

This one NOT working: ...

Correct. Because you are passing a string as filter, i.e. it should be a JSON filter but it is not.

[saptaji]

@nightroman Thank you for your quick response. I appreciate it.

I read that document but still have problems in applying MDBC for a "complex" filter.

As in the example, this one is working:

Get-MdbcData -As PS -Filter @{ "Agent.DeviceName" = @{ '$in' = @([regex]'^Microsoft*') } }

Would you provide examples?

1. Filter with '$or' and '$and'
2. Build a filter string then apply it to the '-Filter' parameter as I will have a complex filter and want to build the filter string before applying it to the '-Filter' parameter.

For point 2. I want to filter based on the DeviceName and UniqueID.

'Agent.DeviceName' = [regex]'^Microsoft.*' 'Agent.UniqueId' = 'f4b7c482-1c50-4091-8ee2-25711f2a4ba6'

Thanks in advance for your help.

[nightroman]

I also tested some filters with 'Compass'. It works with Compass but not mdbc.

Correct. Because you are using invalid JSON and the error tells that. This is the valid version which works:

Get-MdbcData -As PS -Filter '{"Agent.DeviceName": {$regex: "^Microsoft.*"}}'

[nightroman]

I can help you to correct your not working filter. Please post it or at least explain in pseudo code what your filter should do.

[saptaji]

@nightroman

Ahh, it works now:

Get-MdbcData -As PS -Filter '{$and: [{"Agent.DeviceName": { $regex: "^Microsoft.*"}}, {"Agent.UniqueId": "f4b7c482-1c50-4091-8ee2-25711f2a4ba6"}]}'

And this one works also:

$filter = '{ $and: [{"Agent.DeviceName": { $regex: "^Microsoft.*"}}, {"Agent.UniqueId": "f4b7c482-1c50-4091-8ee2-25711f2a4ba6"}] }'

Get-MdbcData -As PS -Filter $filter

Thank you so much for your help. I appreciate it.

You can close this ticket.

Btw, Do you have a PayPal donation account for this effort?

[nightroman]

I am happy that it works for you and you are happy. That's the reward, really. Thank you.

[saptaji]

It's very kind of you. Thank you so much for your contribution to the community.