BIG DATA MONGODB QUERY ON JSON + PARSERS + NESTED QUERY

Source: https://stackoverflow.com/questions/24294273/mongodb-query-a-json-object-nested-in-an-array 

mongodb: Query a json-object nested in an array

5down votefavorite 3

I'm quite new to mongodb and there is one thing I can't solve right now: Let's pretend, you have the following document (simplified): { 'someKey': 'someValue', 'array' : [ {'name' : 'test1', 'value': 'value1' }, {'name' : 'test2', 'value': 'value2' } ] } Which query would return the json-object, in which the value equals 'value2'? That means, i need this json-object: { 'name' : 'test2', 'value': 'value2' } Of course I already tried a lot of possible queries, but none of them returned the right, e.g. db.test.find({'array.value':'value2'}) db.test.find({'array.value':'value2'}, {'array.value':1}) db.test.find({'array.value':'value2'}, {'array.value':'value2'}) Can someone help and show me, what I'm doing wrong? Thanks! arrays json mongodb find shareimprove this question edited Nov 20 '14 at 11:28 Daniel.V 588625 asked Jun 18 '14 at 20:13 Jonas M. 30127

add a comment

3 Answers

activeoldestvotes

up vote10down voteaccepted

Using Positional operator db.test.find( { "array.value": "value2" }, { "array.$": 1, _id : 0 } ) Output { "array" : [ { "name" : "test2", "value" : "value2" } ] } Using aggregation db.test.aggregate([ { $unwind : "$array"}, { $match : {"array.value" : "value2"}}, { $project : { _id : 0, array : 1}} ]) output { "array" : { "name" : "test2", "value" : "value2" } } Using Java Driver MongoClient mongoClient = new MongoClient(new ServerAddress("localhost", 27017)); DB db = mongoClient.getDB("mydb"); DBCollection collection = db.getCollection("test"); DBObject unwind = new BasicDBObject("$unwind", "$array"); DBObject match = new BasicDBObject("$match", new BasicDBObject( "array.value", "value2")); DBObject project = new BasicDBObject("$project", new BasicDBObject( "_id", 0).append("array", 1)); List<DBObject> pipeline = Arrays.asList(unwind, match, project); AggregationOutput output = collection.aggregate(pipeline); Iterable<DBObject> results = output.results(); for (DBObject result : results) { System.out.println(result.get("array")); } output { "name" : "test2" , "value" : "value2"}

1down vote	Try the $in operator like this: db.test.find({"array.value" : { $in : ["value2"]}}) shareimprove this answer answered Jun 18 '14 at 20:50 V_Singh 404414
	Thanks for your help, but unfortunately it doesn't work either. If I run this in the terminal, it returns the whole document – Jonas M. Jun 19 '14 at 0:51  add a comment

up vote0down vote

use $elemMatch and dot(.) to get your required output db.getCollection('mobiledashboards').find({"_id": ObjectId("58c7da2adaa8d031ea699fff") },{ viewData: { $elemMatch : { "widgetData.widget.title" : "England" }}}) shareimprove this answer

Source: https://docs.mongodb.com/manual/reference/mongodb-extended-json/

• Introduction to MongoDB > 
• MongoDB Extended JSON

MongoDB Extended JSON

On this page

• Parsers and Supported Format
• BSON Data Types and Associated Representations

JSON can only represent a subset of the types supported by BSON. To preserve type information, MongoDB adds the following extensions to the JSON format:

• Strict mode. Strict mode representations of BSON types conform to the JSON RFC. Any JSON parser can parse these strict mode representations as key/value pairs; however, only the MongoDB internal JSON parser recognizes the type information conveyed by the format.
• mongo Shell mode. The MongoDB internal JSON parser and the mongo shell can parse this mode.

The representation used for the various data types depends on the context in which the JSON is parsed.

Parsers and Supported Format

Input in Strict Mode

The following can parse representations in strict mode with recognition of the type information.

• REST Interfaces
• mongoimport
• --query option of various MongoDB tools
• MongoDB Compass

Other JSON parsers, including mongo shell and db.eval(), can parse strict mode representations as key/value pairs, but without recognition of the type information.

Input in mongo Shell Mode

The following can parse representations in mongo shell mode with recognition of the type information.

• REST Interfaces
• mongoimport
• --query option of various MongoDB tools
• mongo shell

Output in Strict mode

mongoexport and REST and HTTP Interfaces output data in Strict mode.

Output in mongo Shell Mode

bsondump outputs in mongo Shell mode.

BSON Data Types and Associated Representations

The following presents the BSON data types and the associated representations in Strict mode and mongoShell mode.

Binary

data_binary

Strict Mode		mongo Shell Mode
{ "$binary": "<bindata>", "$type": "<t>" }		BinData ( <t>, <bindata> )

• <bindata> is the base64 representation of a binary string.
• <t> is a representation of a single byte indicating the data type. In Strict mode it is a hexadecimal string, and in Shell mode it is an integer. See the extended bson documentation. http://bsonspec.org/spec.html

Date

data_date

Strict Mode		mongo Shell Mode
{ "$date": "<date>" }		new Date ( <date> )

In Strict mode, <date> is an ISO-8601 date format with a mandatory time zone field following the template YYYY-MM-DDTHH:mm:ss.mmm<+/-Offset>.

The MongoDB JSON parser currently does not support loading ISO-8601 strings representing dates prior to the Unix epoch. When formatting pre-epoch dates and dates past what your system’s time_ttype can hold, the following format is used:

{ "$date" : { "$numberLong" : "<dateAsMilliseconds>" } }

In Shell mode, <date> is the JSON representation of a 64-bit signed integer giving the number of milliseconds since epoch UTC.

Timestamp

data_timestamp

Strict Mode		mongo Shell Mode
{ "$timestamp": { "t": <t>, "i": <i> } }		Timestamp( <t>, <i> )

• <t> is the JSON representation of a 32-bit unsigned integer for seconds since epoch.
• <i> is a 32-bit unsigned integer for the increment.

Regular Expression

data_regex

Strict Mode		mongo Shell Mode
{ "$regex": "<sRegex>", "$options": "<sOptions>" }		/<jRegex>/<jOptions>

• <sRegex> is a string of valid JSON characters.
• <jRegex> is a string that may contain valid JSON characters and unescaped double quote (") characters, but may not contain unescaped forward slash (/) characters.
• <sOptions> is a string containing the regex options represented by the letters of the alphabet.
• <jOptions> is a string that may contain only the characters ‘g’, ‘i’, ‘m’ and ‘s’ (added in v1.9). Because the JavaScript and mongo Shell representations support a limited range of options, any nonconforming options will be dropped when converting to this representation.

OID

data_oid

Strict Mode		mongo Shell Mode
{ "$oid": "<id>" }		ObjectId( "<id>" )

<id> is a 24-character hexadecimal string.

DB Reference

data_ref

Strict Mode		mongo Shell Mode
{ "$ref": "<name>", "$id": "<id>" }		DBRef("<name>", "<id>")

• <name> is a string of valid JSON characters.
• <id> is any valid extended JSON type.

Undefined Type

data_undefined

Strict Mode		mongo Shell Mode
{ "$undefined": true }		undefined

The representation for the JavaScript/BSON undefined type.

You cannot use undefined in query documents. Consider the following document inserted into the people collection:

db.people.insert( { name : "Sally", age : undefined } )

The following queries return an error:

db.people.find( { age : undefined } )
db.people.find( { age : { $gte : undefined } } )

However, you can query for undefined values using $type, as in:

db.people.find( { age : { $type : 6 } } )

This query returns all documents for which the age field has value undefined.

MinKey

data_minkey

Strict Mode		mongo Shell Mode
{ "$minKey": 1 }		MinKey

The representation of the MinKey BSON data type that compares lower than all other types. SeeComparison/Sort Order for more information on comparison order for BSON types.

MaxKey

data_maxkey

Strict Mode		mongo Shell Mode
{ "$maxKey": 1 }		MaxKey

The representation of the MaxKey BSON data type that compares higher than all other types. SeeComparison/Sort Order for more information on comparison order for BSON types.

NumberLong

New in version 2.6.

data_numberlong

Strict Mode		mongo Shell Mode
{ "$numberLong": "<number>" }		NumberLong( "<number>" )

NumberLong is a 64 bit signed integer. You must include quotation marks or it will be interpreted as a floating point number, resulting in a loss of accuracy.

For example, the following commands insert 9223372036854775807 as a NumberLong with and without quotation marks around the integer value:

db.json.insert( { longQuoted : NumberLong("9223372036854775807") } )
db.json.insert( { longUnQuoted : NumberLong(9223372036854775807) } )

When you retrieve the documents, the value of longUnQuoted has changed, while longQuoted retains its accuracy:

db.json.find()
{ "_id" : ObjectId("54ee1f2d33335326d70987df"), "longQuoted" : NumberLong("9223372036854775807") }
{ "_id" : ObjectId("54ee1f7433335326d70987e0"), "longUnQuoted" : NumberLong("-9223372036854775808") }

NumberDecimal

New in version 3.4.

data_numberdecimal

Strict Mode		mongo Shell Mode
{ "$numberDecimal": "<number>" }		NumberDecimal( "<number>" )

NumberDecimal is a high-precision decimal. You must include quotation marks, or the input number will be treated as a double, resulting in data loss.

For example, the following commands insert 123.40 as a NumberDecimal with and without quotation marks around the value:

db.json.insert( { decimalQuoted : NumberDecimal("123.40") } )
db.json.insert( { decimalUnQuoted : NumberDecimal(123.40) } )

When you retrieve the documents, the value of decimalUnQuoted has changed, while decimalQuoted retains its specified precision:

db.json.find()
{ "_id" : ObjectId("596f88b7b613bb04f80a1ea9"), "decimalQuoted" : NumberDecimal("123.40") }
{ "_id" : ObjectId("596f88c9b613bb04f80a1eaa"), "decimalUnQuoted" : NumberDecimal("123.400000000000") }

←  Comparison/Sort OrderInstall MongoDB →

© MongoDB, Inc 2008-2017. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.

**** 

Source: https://stackoverflow.com/questions/10320633/mongo-how-to-query-a-nested-json 

Ask Question

up vote9down votefavorite 5	I am a complete mongo newbie. I am using mongo hub for mac. I need to query the for following json - { "_id" : ObjectId( "abcd" ), "className" : "com.myUser", "reg" : 12345, "test" : [ { "className" : "com.abc", "testid" : "pqrs" } ] } and find records where testid is pqrs. How would I go about doing that? mongodb shareimprove this question asked Apr 25 '12 at 17:20 user747858
	add a comment

1 Answer

activeoldestvotes

up vote27down voteaccepted	You can type {'test.testid': 'pqrs'} in the query field of Mongo Hub. shareimprove this answer answered Apr 25 '12 at 17:26 Blacksad 6,678104467
	1   How to query based on _id too ? – Balaji Boggaram Ramanarayan Aug 31 '16 at 6:15

   

 Was this page helpful?