* 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:
…… |
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);
}
}
}
}
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL