How to query the nested JSON structure in MongoDB/Mongoid

Question

The following is one of the document in the MongoDB database.

I want to select the year between 2007 ,2008.

and the key includes "Actual" or "Upper End of Range"

and the table_name equals to Unemployment rate

How to finish it in Mongoid or MongoDB query.

Or I only can do it in application layer like Ruby or Python ?

id 2012-04-25_unemployment_rate

{
  "_id": "2012-04-25_unemployment_rate",
  "table_name": "Unemployment rate",
  "unit": "Percent",
  "data": [
    {
      "2007": [
        {
          "Actual": "3.5"
        },
        {
          "Upper End of Range": "-"
        },
        {
          "Upper End of Central Tendency": "-"
        },
        {
          "Lower End of Central Tendency": "-"
        },
        {
          "Lower End of Range": "-"
        }
      ]
    },
    {
      "2008": [
        {
          "Actual": "1.7"
        },
        {
          "Upper End of Range": "-"
        },
        {
          "Upper End of Central Tendency": "-"
        },
        {
          "Lower End of Central Tendency": "-"
        },
        {
          "Lower End of Range": "-"
        }
      ]
    }
}

id 2014-04-25_unemployment_rate

{
  "_id": "2014-04-25_unemployment_rate",
  "table_name": "Unemployment rate",
  "unit": "Percent",
  "data": [
    {
      "2008": [
        {
          "Actual": "3.5"
        },
        {
          "Upper End of Range": "-"
        },
        {
          "Upper End of Central Tendency": "-"
        },
        {
          "Lower End of Central Tendency": "-"
        },
        {
          "Lower End of Range": "-"
        }
      ]
    },
    {
      "2009": [
        {
          "Actual": "1.7"
        },
        {
          "Upper End of Range": "-"
        },
        {
          "Upper End of Central Tendency": "-"
        },
        {
          "Lower End of Central Tendency": "-"
        },
        {
          "Lower End of Range": "-"
        }
      ]
    }
}

wdberkeley · Accepted Answer · 2015-03-18 17:00:03Z

1

You don't select documents by keys; you select documents by values. You should restructure your documents to have fields like "year" : 2007. For example,

{
    "_id": "2012-04-25_unemployment_rate",
    "table_name": "Unemployment rate",
    "unit": "Percent",
    "data": [
        {
            "year" : 2007,
            "Actual": "3.5",
            "Upper End of Range": "-",
            "Upper End of Central Tendency": "-",
            "Lower End of Central Tendency": "-",
            "Lower End of Range": "-"
        }
    ]
}

I'm not sure what you mean by the condition that "key includes 'Actual' or 'Upper End of Range'", but if you want documents with a data element with year 2007 or 2008 and table_name equal to "Unemployment rate", use the query spec

{ "table_name" : "Unemployment rate", "data.year" : { "$in" : [2007, 2008] } }

answered Mar 18, 2015 at 17:00

wdberkeley

11.7k1 gold badge30 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

How to query the nested JSON structure in MongoDB/Mongoid

id 2012-04-25_unemployment_rate

id 2014-04-25_unemployment_rate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

id 2012-04-25_unemployment_rate

id 2014-04-25_unemployment_rate

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related