filter object from array and select field

Question

I've got a list of objects with name value pairs in them and I can't figure out how to retrieve a value from an array where name is a certain value.

For instance:

    {
            "ordernumber" : "192915",
            "orderparameters" : {
                    "list" : [
                            {
                                    "composite" : null,
                                    "name" : "CURRENCY_CODE",
                                    "value" : "NOK",
                                    "type" : "String"
                            },
                            {
                                    "composite" : null,
                                    "name" : "NEED_CUSTOMS_DOCUMENT",
                                    "value" : "True",
                                    "type" : "Boolean"
                            }
              ]}
    }

Now I need to find the value of the item with

"name": "NEED_CUSTOMS_DOCUMENT"

so in this case

"value": "True"

I can list all the values with

 db.orders.aggregate({ $match: { ordernumber:'1234' } }, { $project: { 'orderparameters.list.name': 1 } }).pretty()

But then how to retrieve the value field from the index with that name?

The index could be different on every document so I cant simply query "orderparameters.list.1.value"

chridam · Accepted Answer · 2021-11-19 14:08:58Z

3

Refine your aggregate pipeline to include a another stage with a $filter operator and that allows you to filter the list on a given condition, something like the following:

db.orders.aggregate([
    { $match: { 
        ordernumber: "1234", 
        "orderparameters.list.name": "NEED_CUSTOMS_DOCUMENT" 
    } },
    { $project: {
       list: {
          $filter: {
              input: "$orderparameters.list",
              cond: { $eq: ["$$this.name", "NEED_CUSTOMS_DOCUMENT"] }
          }
       } 
    } },
    { $project: { values: "$list.value" } }
])

answered Nov 19, 2021 at 14:08

chridam

104k26 gold badges246 silver badges243 bronze badges

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

Comments

Serve Laurijssen · Accepted Answer · 2022-02-10 13:56:39Z

1

There is an operator especially made for this

$elemMatch

The $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria.

db.orders.aggregate({ $match: { 'orderparameters.list': { $elemMatch: { name: "NEED_CUSTOMS_DOCUMENT", value: "True" } } } })

db.orders.find({ 'orderparameters.list': { $elemMatch: { name: "NEED_CUSTOMS_DOCUMENT", value: "True" } } })

answered Feb 10, 2022 at 13:56

Serve Laurijssen

9,9606 gold badges55 silver badges109 bronze badges

Collectives™ on Stack Overflow

filter object from array and select field

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related