* How to Query Subscripts of MongoDB Array Members
MongoDB can use $slice to get members of a nested array by subscripts, but it cannot get subscripts through member values. A MongoDB array, for instance, has members that are color names. We can find a color according to the corresponding subscript but cannot get a subscript through the color.
Here’s an example. MongoDB collection colors consists of name field and color field, which is of array type, as shown below:
colors |
_id |
name |
color |
1 |
page |
[red,green,blue,white,mauve,black] |
|
2 |
card |
[gray,ivory,mauve,lavender,white,black,yellow] |
To locate a record containing the white color of the specified position, for instance, MongoDB has the following code:
db.colors.find({}, {color: {$slice: [3,4]}} ),
But MongoDB doesn’t have a method to get the subscript of white in the array.
It would be convenient if you could use esProc SPL to do this. SPL script offers pos() function to directly get the subscript of an array’s members.
Download esProc installation package HERE.
Directions for accomplishing the task with esProc:
1. Write SPL script colors.dfx in esProc:
A |
B |
|
1 |
=mongo_open("mongodb://127.0.0.1:27017/raqdb") |
/ Connect to MongoDB database |
2 |
=mongo_shell(A1,"colors.find()").fetch() |
/ Query data of collection colors |
3 |
=A2.new(name:NAME, color.pos("white"):POS) |
/ Get subscript of white in colors |
4 |
>A1.close() |
/ Close database connection |
2. Execute the script and return the following result:
A3 |
NAME |
POS |
paper |
4 |
|
card |
5 |
esProc provides JDBC interface, so you can easily integrate the script into a Java program:
public static void doColor() {
Connection con = null;
java.sql.Statement st;
try{
Class.forName("com.esproc.jdbc.InternalDriver");
con = DriverManager.getConnection("jdbc:esproc:local://");
// Call script colors.dfx
st=con.createStatement();
ResultSet rst = st.executeQuery("call colors");
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);
}
}
}
}
To get all subscripts for the current specified color, you can use @a option with pos()function. More uses of pos() function can be found HERE. Read How to Call an SPL Script in Java to learn more about integration of esProc SPL script into a Java program.
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