N1ql SUM on UNNEST Array

Question

N1ql SUM on UNNEST Array

I have a single bucket (Couchbase Community edition 6.5) consisting of the following documents:

FishingDoc

{
    "boatIds": ["boatId_1","boatId_2","boatId_3"],
    "areaIds": ["areaId_1","areaId_2","areaId_3"],
    "total": 10,
    "date": "2021-05-13T00:00:00Z",
    "type": "fishing"
},
{
    "boatIds": ["boatId_1","boatId_3"],
    "areaIds": ["areaId_2","areaId_3"],
    "total": 25,
    "date": "2021-05-15T00:00:00Z",
    "type": "fishing"
}

RiverDoc

{
    "_id": "areaId_1",
    "size": 5,
    "type": "river"
},
{
    "_id": "areaId_1",
    "size": 10,
    "type": "river"
},
{
    "_id": "areaId_1",
    "size": 15,
    "type": "river"
}

BoatDoc

{
    "_id": "areaId_1",
    "name": "Small Boat",
    "type": "boat"
},
{
    "_id": "areaId_1",
    "name": "Medium Boat",
    "type": "boat"
},
{
    "_id": "areaId_1",
    "name": "Large Boat",
    "type": "boat"
}

I need a query where I can get all of the fishing docs broken up per river and per boat. I got this working using the UNNEST operator in the following query:

SELECT river.size,
        boat.name,
        fishing.total
FROM bucket_name fishing
UNNEST fishing.riverIds AS river
UNNEST fishing.boatIds AS boat
WHERE fishing.type = "fishing"

But the problem in this query is that the total value in the above query is the total for the entire fishing object.

I need to get the total, relative to the size of the unnested river. So I need to join in and sum the total of all the rivers for the fishing object and get the specific river's size relative to the total.

Here is the select statement I have in mind but I have no idea on how to actually write the correct query:

SELECT river.size,
        boat.name,
        river.size/SUM( fishing.riverIds[0].size, fishing.riverIds[1].size, fishing.riverIds[2].size ) * fishing.total
FROM bucket_name fishing
UNNEST fishing.riverIds AS river
UNNEST fishing.boatIds AS boat
WHERE fishing.type = "fishing"

Answer 1

INSERT INTO default VALUES ("f01", { "boatIds": ["boatId_1","boatId_2","boatId_3"], "areaIds": ["areaId_1","areaId_2","areaId_3"], "total": 10, "date": "2021-05-13T00:00:00Z", "type": "fishing" });
INSERT INTO default VALUES ("f02", { "boatIds": ["boatId_1","boatId_3"], "areaIds": ["areaId_2","areaId_3"], "total": 25, "date": "2021-05-15T00:00:00Z", "type": "fishing" });
INSERT INTO default VALUES ("areaId_1", { "_id": "areaId_1", "size": 5, "type": "river" });
INSERT INTO default VALUES ("areaId_2", { "_id": "areaId_2", "size": 10, "type": "river" });
INSERT INTO default VALUES ("areaId_3", { "_id": "areaId_3", "size": 15, "type": "river" });
INSERT INTO default VALUES ("boatId_1", { "_id": "boatId_1", "name": "Small Boat", "type": "boat" });
INSERT INTO default VALUES ("boatId_2", { "_id": "boatId_2", "name": "Medium Boat", "type": "boat" });
INSERT INTO default VALUES ("boatId_3", { "_id": "boatId_3", "name": "Large Boat", "type": "boat" });

SELECT ARRAY {"size": f.river.[v], "name": f.boat.[f.boatIds[pos]], "total": f.total*f.river.[v]/ARRAY_SUM(OBJECT_VALUES(f.river))}
       FOR pos:v IN f.areaIds END AS distribution
FROM (SELECT d.*,
          OBJECT v._id:v.size FOR v IN (SELECT r._id, r.size FROM default AS r USE KEYS d.areaIds) END AS river,
          OBJECT v._id:v.name FOR v IN (SELECT b._id, b.name FROM default AS b USE KEYS d.boatIds) END AS boat
      FROM default AS d
      WHERE d.type = "fishing") AS f;
{
    "requestID": "fbe127b4-2ebb-4b01-a8a1-0bfe5310ed42",
    "signature": {
        "distribution": "array"
    },
    "results": [
    {
        "distribution": [
            {
                "name": "Small Boat",
                "size": 5,
                "total": 1.6666666666666667
            },
            {
                "name": "Medium Boat",
                "size": 10,
                "total": 3.3333333333333335
            },
            {
                "name": "Large Boat",
                "size": 15,
                "total": 5
            }
        ]
    },
    {
        "distribution": [
            {
                "name": "Small Boat",
                "size": 10,
                "total": 10
            },
            {
                "name": "Large Boat",
                "size": 15,
                "total": 15
            }
        ]
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "6.946602ms",
        "executionTime": "6.881065ms",
        "resultCount": 2,
        "resultSize": 730,
        "serviceLoad": 2
    }
}

OR

SELECT river AS SectionSize,
       f.total*(river/ARRAY_SUM(f.sections)) AS total,
       f.boats[UNNEST_POS(river)] AS name
FROM (SELECT d.total,
             (SELECT RAW r.size FROM default AS r USE KEYS d.areaIds) AS sections,
             (SELECT RAW b.name FROM default AS b USE KEYS d.boatIds) AS boats
      FROM default AS d
      WHERE d.type = "fishing") AS f
UNNEST f.sections AS river;

{
"results": [
    {
        "SectionSize": 5,
        "name": "Small Boat",
        "total": 1.6666666666666665
    },
    {
        "SectionSize": 10,
        "name": "Medium Boat",
        "total": 3.333333333333333
    },
    {
        "SectionSize": 15,
        "name": "Large Boat",
        "total": 5
    },
    {
        "SectionSize": 10,
        "name": "Small Boat",
        "total": 10
    },
    {
        "SectionSize": 15,
        "name": "Large Boat",
        "total": 15
    }
    ]
}