* Query Nested Subdocuments in MongoDB


As a NoSQL database, MongoDB can increase horizontal scalability and performances and achieve a one-to-many relationship through nested subdocuments. Yet users who are not very familiar with the database are inclined to get confused by its unique solutions. Take the following MongoDB data as an example:

……
       {
          "_id": "1001",
        "name": "Storage Beta",
        "items": [
           {"category":   "food", "name":   "pear"},
           {"category":   "food", "name":   "peach"},
         {"category":   "food", "name":   "grape"},
 
           {"category":   "tool", "name":   "knife"},
         {"category":   "furniture",   "name": "chair"  },
         {"category":   "furniture",   "name": "bench"}
       ]
  }
    ……

We want to get subdocuments meeting the condition category='food'. I’m sure many users will first think of using find() statement to do this:

db.storage.find({'items.category':{ $eq: 'food'}}).pretty();
Yet the statement returns all original documents without performing filtering. MongoDB’s way of doing this is the combination of aggregate+$project+$filter+input+cond. I won’t go into details here.

It would be easy to get things done if you used esProc SPL. SPL script supports using select() to query the target directly.
Download esProc installation package
HERE.

Directions:
1. Write SPL script storage.dfx in esProc:

A

B

1

=mongo_open("mongodb://127.0.0.1:27017/raqdb")

/ Connect to MongoDB database

2

=mongo_shell(A1,"storage.find()").fetch()

/ Query data of collection storage

3

=A2.new(_id, name,  items.select(category=="food"):items   )

/ Perform filtering to query   the target data and store result as a table sequence

4

>A1.close()

/ Close database connection

2. Execute the script and return the following result:

A3

_id

name

items

1000

Storage  Alpha

[[food,apple],[food,banana]]

1001

Storage  Beta

[[food,pear],[food,peach],[food,grape]]

esProc offers JDBC interface, so you can easily integrate the script into a Java program:

public static void doWork() {
  Connection con = null;
  java.sql.Statement st;
 
  try{
    Class.forName("com.esproc.jdbc.InternalDriver");
    con = DriverManager.getConnection("jdbc:esproc:local://");
     // Call script storage.dfx
     st=con.createStatement();
     ResultSet rst = st.executeQuery("call storage");
     System.out.println(rst);
   }catch(Exception e){
     System.out.println(e);
   }finally{
    // Close database connection
    if (con!=null) {
       try {
        con.close();
      }catch(Exception e) {
        System.out.println(e);
      }
    }
  }
}

Read How to Call an SPL Script in Java to learn more about integration of esProc SPL script into a Java program.