* 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.