Querying a map (<String, Object>) in JSON through MongoDB

Question

How to query a map of type Map<String, List> in JSON form, in MongoDB?

Sample JSON:

{
    "WIDTH": 810,
    "HEIGHT": 465,
    "MODULES": {
        "23": {
            "XNAME": "COMP1",
            "PARAMS": {
                "_Klockers": {
                    "TYPE": "text",
                    "VALUE": "Klocker#3"
                },
                "SUBSYS": {
                    "TYPE": "text",
                    "VALUE": "2"
                },
                "EP": {
                    "TYPE": "integer",
                    "VALUE": "2"
                }
            }
        },
        "24": {
            "XNAME": "COMP2",
            "PARAMS": {
                "_Rockers": {
                    "TYPE": "text",
                    "VALUE": "Rocker#3"
                },
                "Driver": {
                    "TYPE": "binary",
                    "VALUE": 1
                },
                "EP": {
                    "TYPE": "long",
                    "VALUE": "233"
                }
            }
        },
        "25": {
            "XNAME": "COMP3",
            "PARAMS": {
                "_Mockers": {
                    "TYPE": "text",
                    "VALUE": "Mocker#3"
                },
                "SYSMain": {
                    "TYPE": "text",
                    "VALUE": "2342"
                },
                "TLP": {
                    "TYPE": "double",
                    "VALUE": "2.3"
                }
            }
        }
    }
}

Basically I want to :

List all the "XNAME" field values of all keys in "MODULES". Expected output : {"COMP1", "COMP2", "COMP3"}
List all the "TYPE" in "PARAMS" object within each key of "MODULES". Expected output : {"text", "text", "integer", "text", "binary", "long", "text", "text", "double"}

I am new to MongoDB and any help or redirection is appreciated.

Gibbs · Accepted Answer · 2020-12-15 09:21:22Z

1

You can use this

db.collection.aggregate([
  {
    $project: {//You require this as your data is dynamic
      "modules": {
        "$objectToArray": "$MODULES"
      }
    }
  },
  {//Destruct the array
    "$unwind": "$modules"
  },
  {
    "$project": {//Again, requires the same as keys are dynamic
      "types": {
        "$objectToArray": "$modules.v.PARAMS"
      },
      xname: "$modules.v.XNAME"
    }
  },
  {//Destruct the types
    $unwind: "$types"
  },
  {//Get the distinct values
    $group: {
      "_id": null,
      "xname": {
        "$addToSet": "$xname"
      },
      "types": {
        "$addToSet": "$types.v.TYPE"
      },
      
    }
  }
])

answered Dec 15, 2020 at 9:21

Gibbs

23.1k14 gold badges84 silver badges147 bronze badges

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

2 Comments

H S Raju Over a year ago

Thanks Gibbs. It answers the query. However, I want to check, whether it is good decision to perform these complex collections related operations at DB level or at programmatic level. I believe the later would be better, as the performance will be a question. Your thoughts please

Gibbs Over a year ago

It depends on the number of documents, nodes, sharding and many more. I would suggest you to try with your data.

Collectives™ on Stack Overflow

Querying a map (<String, Object>) in JSON through MongoDB

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