Inserting an Array Value
When inserting an array value using connector versions 1.8.4 and earlier, you must specify the bottom-level index of the array in the INSERT statement. The connector then inserts the new array value at the index position that you specified.
In connector versions 2.0.0 to 2.2.7, specifying the bottom-level index in an INSERT statement for an array value causes the connector to return an error. These versions of the connector always insert new array values in the next available position, and do not accept INSERT statements that specify the bottom-level index value.
Beginning with connector versions 2.2.8, the connector is able to successfully execute INSERT statements that contain the bottom-level index value. The connector also supports statements that specify -1 as the index. In both cases, the connector appends the new value to the end of the array, as expected. However, it is still recommended that you do not specify the bottom-level index value, since it is no longer required.
Example
For example, a MongoDB database contains a table named Test, which has a single document with the following content:
{
_id : 2000,
"Array" : [ 0,
{
"BottomLevelArray" : [
{
"col" : "Federer"
}
]
}
]
}
The connector expands this array into the following virtual table named Test_Array_BottomLevelArray:
| _id | Test_Array_dim1_idx | Test_Array_BottomLevelArray_dim1_idx | col |
|---|---|---|---|
|
2000 |
1 |
0 |
Federer |
In this example, you want to append the value "Nadal" to the end of the array (after "Federer"), so that the resulting virtual table looks like this:
| _id | Test_Array_dim1_idx | Test_Array_BottomLevelArray_dim1_idx | col |
|---|---|---|---|
|
2000 |
1 |
0 |
Federer |
|
2000 |
1 |
1 |
Nadal |
In connector versions 1.8.4 and earlier, you must execute the following statement:
INSERT INTO Test_Array_BottomLevelArray_dim1_idx values (2000, 1, 1, 'Nadal')
In connector versions 2.0.0 to 2.2.7, you must execute the following statement:
INSERT INTO Test_Array_BottomLevelArray_dim1_idx values (2000, 1, 'Nadal')
In connector version 2.2.8 and later, you can execute any of the following statements:
INSERT INTO Test_Array_BottomLevelArray_dim1_idx values (2000, 1, 1, 'Nadal')
INSERT INTO Test_Array_BottomLevelArray_dim1_idx values (2000, 1, 'Nadal')
INSERT INTO Test_Array_BottomLevelArray_dim1_idx values (2000, 1, -1, 'Nadal')
Note:
- Even though statements that contain the bottom-level index value are supported in connector versions 2.2.8 or later, it is still recommended that you omit the value, as it is not required.
- In all cases, you must specify the upper-level index value. This indicates which array the connector should insert the value in.