Need to move object in Array UP or Down using N1QL, couchbase 4.5.0

Hi,

Need some assistance on moving object up and down in array using N1QL query.

I have document like below:

{
“level1_Arr”: [
{
“level2_Arr”: [
{
“level3_Arr”: [
{
“level4_Attr”: “L4_ATTR”,
“level4_Attr1”: “L4_ATTR0”,
“level4_Id”: “L4_ID_0”
},
{
“level4_Attr”: “test1”,
“level4_Attr1”: “L4_ATTR1”,
“level4_Id”: “L4_ID_1”
},
{
“level4_Attr”: “test2”,
“level4_Attr1”: “L4_ATTR2”,
“level4_Id”: “L4_ID_2”
}
],
“level3_Id”: “L3_ID_0”
}
],
“level2_Id”: “L2_ID_0”
}
],
“level1_Id”: “L1_ID_0”
}

Need simple query to move objects.

Ex :

  1. “level4_Id”: “L4_ID_1” Object needs to move above “level4_Id”: “L4_ID_0” by selecting level4_id as KEY.
  2. “level4_Id”: “L4_ID_0” object cannot move above as it is first object in that array.
  3. “level4_Id”: “L4_ID_2” object has to be moved above “level4_Id”: “L4_ID_1”

Kindly help ASAP.

Thanks,

can you give a sample result data you want?

if “level4_Id”: “L4_ID_1” is selected then , result has to be like below,

{
“level1_Arr”: [
{
“level2_Arr”: [
{
“level3_Arr”: [
{
“level4_Attr”: “test1”,
“level4_Attr1”: “L4_ATTR1”,
“level4_Id”: “L4_ID_1”
},
{
“level4_Attr”: “L4_ATTR”,
“level4_Attr1”: “L4_ATTR0”,
“level4_Id”: “L4_ID_0”
},
{
“level4_Attr”: “test2”,
“level4_Attr1”: “L4_ATTR2”,
“level4_Id”: “L4_ID_2”
}
],
“level3_Id”: “L3_ID_0”
}
],
“level2_Id”: “L2_ID_0”
}
],
“level1_Id”: “L1_ID_0”
}

the core part as following:

SELECT ARRAY_CONCAT(TO_ARRAY(obj), ARRAY_REMOVE(t.level1_Arr[0].level2_Arr[0].level3_Arr ,obj)) AS level3_Arr_part
FROM default t USE KEYS ["TEST:1002"]
LET obj = FIRST level4_obj  FOR level4_obj IN t.level1_Arr[0].level2_Arr[0].level3_Arr WHEN level4_obj.level4_Id == "L4_ID_1" END

I am trying to finish the query.

another version of core part as following:

SELECT ARRAY_INSERT(ARRAY_REMOVE(t.level1_Arr[0].level2_Arr[0].level3_Arr ,obj),0,obj)
FROM default t USE KEYS ["TEST:1002"]
LET obj = FIRST level4_obj  FOR level4_obj IN t.level1_Arr[0].level2_Arr[0].level3_Arr WHEN level4_obj.level4_Id == "L4_ID_1" END

I have no idea how to replace this part for the sample document.
may be @vsr1 can help.

INSERT INTO default VALUES("k01", { "level1_Arr": [ { "level2_Arr": [ { "level3_Arr": [ { "level4_Attr": "L4_ATTR", "level4_Attr1": "L4_ATTR0", "level4_Id": "L4_ID_0" }, { "level4_Attr": "test1", "level4_Attr1": "L4_ATTR1", "level4_Id": "L4_ID_1" }, { "level4_Attr": "test2", "level4_Attr1": "L4_ATTR2", "level4_Id": "L4_ID_2" } ], "level3_Id": "L3_ID_0" } ], "level2_Id": "L2_ID_0" } ], "level1_Id": "L1_ID_0" });

 UPDATE default USE KEYS ["k01"] SET level1_Arr[0].level2_Arr[0].level3_Arr = 
 ARRAY_INSERT(ARRAY_REMOVE(level1_Arr[0].level2_Arr[0].level3_Arr ,FIRST level4_obj  FOR level4_obj IN level1_Arr[0].level2_Arr[0].level3_Arr WHEN level4_obj.level4_Id == "L4_ID_1" END),0,FIRST level4_obj  FOR level4_obj IN level1_Arr[0].level2_Arr[0].level3_Arr WHEN level4_obj.level4_Id == "L4_ID_1" END);

OR

UPDATE default USE KEYS ["k01"] SET level1_Arr[0].level2_Arr[0].level3_Arr = 
ARRAY_PREPEND(FIRST level4_obj  FOR level4_obj IN level1_Arr[0].level2_Arr[0].level3_Arr WHEN level4_obj.level4_Id == "L4_ID_1" END, ARRAY_REMOVE(level1_Arr[0].level2_Arr[0].level3_Arr ,FIRST level4_obj  FOR level4_obj IN level1_Arr[0].level2_Arr[0].level3_Arr WHEN level4_obj.level4_Id == "L4_ID_1" END));

1st query is not working, got this error - “Invalid function ARRAY_INSERT. - at )”, 2nd query is working fine.

Couchbase version is 4.0.0.

But if i want to move the 3rd element to 2nd position and 2nd element as 3rd element, that means need to move the selected element just 1 position up, but not for the first element as there is no further moment for that.

ARRAY_INSERT() function is not available in 4.0.0

Opened improvement for MOVE objects in array. https://issues.couchbase.com/browse/MB-24264