Get-MdbcData Filter and Project

[kipster521]

Below is an example collection data. I would like to filter on Element array Name="Fruit" and only return the Id, Element Array Name and Element Array Item. The Element is an array where the value "Fruit" in the Name field can be in any of the array index.

I started with this: Get-MdbcData -As ps -Filter @{Element.Name : "Fruit"} -Project {Id, Element.Name, Element.Item} and received an Invalid JSON input '' error. What am I missing? Is there a wildcard for the array index?

[nightroman]

The provided command fails due to invalid PowerShell syntax, not "Invalid JSON input"... Parameter values for Filter and Projection are both invalid.

Not sure what you want to do but based on your command this is the valid syntax:

Connect-Mdbc -NewCollection

$(
    @{
        _id=1; Id=1; Partition=1
        Element=@(
            @{Name='Fruit'; Item='Apple'; IsActive=$true}
            @{Name='Vegetable'; Item='Lettice'; IsActive=$true}
        )
    }
    @{
        _id=2; Id=2; Partition=2
        Element=@(
            @{Name='Drink'; Item='Water'; IsActive=$true}
            @{Name='Fruit'; Item='Orange'; IsActive=$true}
        )
    }
) | Add-MdbcData

Get-MdbcData -As ps -Filter @{'Element.Name' = 'Fruit'} -Project @{Id=1; 'Element.Name'=1; 'Element.Item'=1}

output

_id Id Element
--- -- -------
  1  1 {@{Name=Fruit; Item=Apple}, @{Name=Vegetable; Item=Lettice}}
  2  2 {@{Name=Drink; Item=Water}, @{Name=Fruit; Item=Orange}}

[kipster521]

Thank you for the response. However, I would like my output to only include Name=Fruit as seen below. The way I am using the filter parameter does not seem to work. id Id Element

---

1 1 {@{Name=Fruit; Item=Apple}} 2 2 {@{Name=Fruit; Item=Orange}}

[nightroman]

However, I would like my output to only include Name=Fruit as seen below. The way I am using the filter parameter does not seem to work.

Your filter works as designed in MongoDB, this may be confusing perhaps.

What you want (almost) may be achieved with a different projection, e.g.

Get-MdbcData -As ps -Filter @{'Element.Name' = 'Fruit'} -Project @{Id=1; Element=@{'$elemMatch'=@{Name='Fruit'}}}

"Almost" because the Element is already specified and you cannot use conflicting 'Element.Name'=1; 'Element.Item'=1.

Perhaps aggregation may give what you want exactly, e.g. see this SO answer -- https://stackoverflow.com/a/28877559/323582 and try using the command Invoke-MdbcAggregate.