How to query a dynamic key - mongodb schema design

Question

I have the following document:

{
    _id     : 1,

    key1    : {
                  samekeyA : "value1",
                  samekeyB : "value2"
              },

    key2 :    {
                  samekeyA : "value3",
                  samekeyB : "value4"
              },

    key3 :    {
                  samekeyA : "value5",
                  samekeyB : "value6"
              }  
}

Above; key1, key2 and key3 are given to demonstrate that I don't know the full key, except a prefix of it; but inner keys samekeyA and samekeyB those I know. I require queries such: db.coll.find({"key*.samekeyA":"value1"}).

I think there isn't a mongo way - regex key queries?- to accomplish that, so any ideas on that? Should I remodel my document -tree-?

score 8 · Answer 1 · answered Aug 23 '18 at 10:14

Using MongoDB 3.4.4 and newer versions:

db.coll.aggregate([
    { "$replaceRoot": {
        "newRoot": {
            "$arrayToObject": {
                "$filter": {
                    "input": { "$objectToArray": "$$ROOT" },
                    "as": "el",
                    "cond": {
                        "$eq": [
                            "$$el.v.samekeyA",
                            "value1"
                        ]
                    }
                }
            }
        }   
    } }
])

The above pipeline will yield the final output

{
    "key1" : {
        "samekeyA" : "value1",
        "samekeyB" : "value2"
    }
}

Explanations

The pipeline can be decomposed to show each individual operator's results.

$objectToArray

$objectToArray enables you to transform the root document with dynamic keys (denoted by the system variable $$ROOT) into an array that contains a element for each field/value pair in the original document. Each element in the return array is a document that contains two fields k and v. Running the pipeline with just the operator in a $project stage

db.coll.aggregate([
    { "$project": {
        "keys": { "$objectToArray": "$$ROOT" }
    } }
])

yields

{
    "_id" : 1,
    "keys" : [ 
        {
            "k" : "_id",
            "v" : 1
        }, 
        {
            "k" : "key1",
            "v" : {
                "samekeyA" : "value1",
                "samekeyB" : "value2"
            }
        }, 
        {
            "k" : "key2",
            "v" : {
                "samekeyA" : "value3",
                "samekeyB" : "value4"
            }
        }, 
        {
            "k" : "key3",
            "v" : {
                "samekeyA" : "value5",
                "samekeyB" : "value6"
            }
        }
    ]
}

$filter

The $filter operator acts as a filtering mechanism for the array produced by the $objectToArray operator, works by selecting a subset of the array to return based on the specified condition which becomes your query.

Consider the following pipeline which returns an array of the key/value pair that matches the condition { "samekeyA": "value1" }

db.coll.aggregate([
    { "$project": {
        "keys": { 
            "$filter": {
                "input": { "$objectToArray": "$$ROOT" },
                "as": "el",
                "cond": {
                    "$eq": [
                        "$$el.v.samekeyA",
                        "value1"
                    ]
                }
            }  
        }
    } }
])

which yields

{
    "_id" : 1,
    "keys" : [ 
        {
            "k" : "key1",
            "v" : {
                "samekeyA" : "value1",
                "samekeyB" : "value2"
            }
        }
    ]
}

$arrayToObject

This will transform the filtered array above from

[ 
    {
        "k" : "key1",
        "v" : {
            "samekeyA" : "value1",
            "samekeyB" : "value2"
        }
    }
]

to the original document with the dynamic key

{
    "key1" : {
        "samekeyA" : "value1",
        "samekeyB" : "value2"
    }
}

so running the pipeline

db.coll.aggregate([
    { "$project": {
        "key": {
            "$arrayToObject": {
                "$filter": {
                    "input": { "$objectToArray": "$$ROOT" },
                    "as": "el",
                    "cond": {
                        "$eq": [
                            "$$el.v.samekeyA",
                            "value1"
                        ]
                    }
                }
            }
        }   
    } }
])

will produce

{
    "_id" : 1,
    "key" : {
        "key1" : {
            "samekeyA" : "value1",
            "samekeyB" : "value2"
        }
    }
}

$replaceRoot

This will promote the filtered dynamic key document to the top level and replaces all other fields. The operation replaces all existing fields in the input document, including the _id field.

Essentially this transforms the above document

{
    "_id" : 1,
    "key" : {
        "key1" : {
            "samekeyA" : "value1",
            "samekeyB" : "value2"
        }
    }
}

to the desired final output

{
    "key1" : {
        "samekeyA" : "value1",
        "samekeyB" : "value2"
    }
}

hgoebl · Accepted Answer · 2014-01-25T08:54:18.520

I'd recommend restructuring the model.

{
    _id     : 1,

    data: [   {
                  key      : "key1",
                  samekeyA : "value1",
                  samekeyB : "value2"
              },

              {
                  key      : "key2",
                  samekeyA : "value3",
                  samekeyB : "value4"
              },

              {
                  key      : "key3",
                  samekeyA : "value5",
                  samekeyB : "value6"
              }  
    ]
}

And the query:

db.col.find({"data.samekeyA": "value1"})

At the moment (and probably in the future, too) it's not possible to query MongoDB collections with wildcards in fieldnames (thanks to @gWiz).

That is to say: no, MongoDB doesn't have any such query operator that allows you to match fields against a regex. — G-Wiz, Jan 25 '14 at 05:13

How to query a dynamic key - mongodb schema design

2 Answers2

Explanations

Linked