How can I merge, sort and do summation of array item values within the list results

SQL++
,
1

Hi CouchBase Gurus,

Need some help. How can I merge, sort and do summation of array item values within the list results.

Here’s the array of documents:

[
{
    "contact": "contact::45”,
    "createdAt": "2020-01-18",
    "digitalAssetSharedItems": [
      {
        "digitalAsset": “01”,
        "lastViewedAt": "2021-01-18",
        "views": 2
      },
      {
        "digitalAsset": "02",
        "lastViewedAt": "2021-01-18",
        "views": 1
      },
    ]
  },
{
    "contact": "contact::45",
    "createdAt": "2020-03-18",
    "digitalAssetSharedItems": [
      {
        "digitalAsset": "01",
        "lastViewedAt": "2020-06-18",
        "views": 5
      },
      {
        "digitalAsset": “02”,
        "lastViewedAt": "2021-01-18",
        "views": 3
      },
      {
        "digitalAsset": “04”,
        "lastViewedAt": "2021-01-18",
        "views": 13
      }
    ]
  },
]

Here is the result I want to achieve:

  • merged with same id “digitalAsset”
  • sum up: “views” value
  • and Group By contacts
  • I want to put the createdAt value on their specific document
[
  "contact": "contact::45”,
  "digitalAssetSharedItems": [
      {
       "createdAt": "2020-01-18",
        "digitalAsset": “01”,
        "lastViewedAt": "2021-01-18",
        "views": 7
      },
      {
        "createdAt": "2020-01-18",
        "digitalAsset": “02”,
        "lastViewedAt": "2021-01-18",
        "views": 4
      },
      {
       "createdAt": "2020-03-18",
        "digitalAsset": “04”,
        "lastViewedAt": "2021-01-18",
        "views": 13
      }
    ]
]

Here’s my partial query:

SELECT
 das.contact,
 ARRAY_FLATTEN(ARRAY_AGG(das.digitalAssetSharedItems), 2) AS digitalAssetSharedItems
FROM `bluebonnet` AS das
WHERE `type` = 'digital-asset-shared'
AND tenant = 'tenant::01'
AND contact = "contact::45"
GROUP BY das.contact

But I’ve have no log on this.

Need your help guys