Combining two sequencing sub documents

SQL++
1

How to combine two sequencing sub docs into one like “did”: “XYZ” and “did”: “ABC” into one doc and next two into another . Following is the structure
[
{
“data”: [
{

    "name": "CDF",
    "timeStamp": 1759670710999,
  },
  {
    "name": "EfF",
    "timeStamp": 1559670710999,
  }
],
"did": "XYZ",
"id": "1_2"

},
{
“data”: [
{

    "name": "WER",
    "timeStamp": 1859670710999,
  },
  {
    "name": "TYU",
    "timeStamp": 1959670710999,
  }
],
"did": "ABC",
"id": "1_2",

},
{
“data”: [
],
“did”: “XYZ”,
“id”: “1_2”,
},
{
“data”: [
],
“did”: “ABC”,
“id”: “1_2”,
}
]

2

Could you please categorize the input and expected output. (Like docA, docB, Expected output).

You can construct array as [docA, docB] or use ARRAY_AGG()
Or Add use OBJECT_ADD() to make sub object.

3

Input
[
{
“data”: [
{

    "name": "CDF",
    "timeStamp": 1759670710999,
  },
  {
    "name": "EfF",
    "timeStamp": 1559670710999,
  }
],
"did": "XYZ",
"id": "1_2"

},
{
“data”: [
{

    "name": "WER",
    "timeStamp": 1859670710999,
  },
  {
    "name": "TYU",
    "timeStamp": 1959670710999,
  }
],
"did": "ABC",
"id": "1_2",

},
{
“data”: [
],
“did”: “XYZ”,
“id”: “1_2”,
},
{
“data”: [
],
“did”: “ABC”,
“id”: “1_2”,
}
]

Output
[
{
“id”: “1_2”
{
“data”: [
{

    "name": "CDF",
    "timeStamp": 1759670710999,
  },
  {
    "name": "EfF",
    "timeStamp": 1559670710999,
  }
],
"did": "XYZ",
},

{
“data”: [
{

    "name": "WER",
    "timeStamp": 1859670710999,
  },
  {
    "name": "TYU",
    "timeStamp": 1959670710999,
  }
],
"did": "ABC",

}
},
{
“id”: “1_2”
{
“data”: [
“name”: “WER”,
“timeStamp”: 1959670710999,
],
“did”: “XYZ”,
},
{
“data”: [
],
“did”: “ABC”,
}
}
]

4

Use array construct

ARRAY (CASE WHEN (pos == 0 AND ARRAY_LENGTH(inputArray) > 1) THEN {v.id,"newdata":inputArray[0:2]}
ELSE v END) FOR pos:v IN inputArray WHEN pos != 1 END


SELECT ARRAY (CASE WHEN (pos == 0 AND ARRAY_LENGTH(inputArray) > 1) THEN {v.id,"newdata":inputArray[0:2]} ELSE v END) FOR pos:v IN inputArray WHEN pos != 1 END
LET inputArray = [ { "data": [ { "name": "CDF", "timeStamp": 1759670710999 }, { "name": "EfF", "timeStamp": 1559670710999 } ], "did": "XYZ", "id": "1_2" }, { "data": [ { "name": "WER", "timeStamp": 1859670710999 }, { "name": "TYU", "timeStamp": 1959670710999 } ], "did": "ABC", "id": "1_2" }, { "data": [ ], "did": "XYZ", "id": "1_2" }, { "data": [ ], "did": "ABC", "id": "1_2" } ];
5

internal data not in an array are like sub docs example [{“id”:“1”,“did”:“ABC”,data[]},{“id”:“1”,“did”:“XYZ”,data[]},{“id”:“1”,"did:“ABC1”,data[]} ,{“id”:“1”,“did”:“XYZ1”,data[]},{“id”:“1”,"did:“ABC2”,data[]} ,{“id”:“1”,“did”:“XYZ2”,data[]}]

out put i am looking for
{
{
“id”:“1”,
{“did”:“ABC”,data[]},{“did”:“XYZ”,data[]}
},
{
“id”:“1”,
{“did”:“ABC1”,data[]},{“did”:“XYZ1”,data[]}
}
}

6

Is not valid JOSN format. Object needs a name or it needs to be an array.
What is criteria grouping. You can change ARRAY construct to generate pairs. Also inputArray subquery.

SELECT ARRAY (CASE WHEN (IMOD(pos,2) = 0 AND pos+1 < ARRAY_LENGTH(inputArray) ) THEN {v.id,"newdata":inputArray[pos:2]} ELSE v END) FOR pos:v IN inputArray WHEN IMOD(pos,2) = 1 END
LET inputArray = (SUB QUERY);

If u need each one as OBJECT do UNNEST on top

Also you can try following replace SUB QUERY with actual query

SELECT av[0].id, av
FROM "1" AS c
UNNEST (SUB QUERY) AS p
GROUP BY IDIV(UNNEST_POSITION(p),2)
LETTING av = ARRAY_AGG(p);

SELECT (OBJECT TOSTRING(pos):v FOR pos:v IN av END).*, av[0].id
FROM (SELECT RAW ARRAY_AGG(p)
     FROM "1" AS c
     UNNEST (SUB QUERY) AS p
     GROUP BY IDIV(UNNEST_POSITION(p),2)) AS av;
7

Seems to be working, Thank you

8

where i can get documentation for “UNNEST_POSITION” ? not found on the couchbase documentation .

9

UNNEST_POS(arg1) OR UNNEST_POSITION(arg1) is function takes single argument.
argument must be UNNEST implicit or explicit alias. It returns position in array that contributed to UNNEST (Join). DOC-3857

10

How to add incremental index the above solution like each set should have unique index

11

with UNNEST_POSITION§ we can generate index , thank you.

12

You can’t create secondary index on UNNEST_POSITION(). It is only query specific when used UNNEST clause. It gives position in array that unnesting.