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

2 
SELECT d.contact, ARRAY_AGG({d.createdAt, d.digitalAsset, d.lastViewedAt, d.views}) AS digitalAssetSharedItems
FROM (SELECT b.contact, b.createdAt, das.digitalAsset, das.lastViewedAt, SUM(das.views) AS views
     FROM `bluebonnet` AS b
     UNNEST b.digitalAssetSharedItems AS das
     WHERE b.`type` = 'digital-asset-shared'
           AND b.tenant = 'tenant::01'
           AND b.contact = "contact::45"
     GROUP BY b.contact, b.createdAt, das.digitalAsset, das.lastViewedAt) AS d
GROUP BY d.contact;
3

Hi @vsr1,
Thank you for immediate response I appreciate it.
I have some questions. Is there a way I could:

  • Sort the array items in digitalAssetSharedItems by views, lastViewedAt or createdAt ?
  • And I can limit the results of array items in digitalAssetSharedItems just like pagination ?

Thanks

4

Hi @lyndondonz,

SEECT d1.contat, 
      (SELECT s.* 
       FROM d1.digitalAssetSharedItems AS s 
       WHERE ..........
       ORDER BY s.views
       OFFSET 0 LIMIT 5) AS digitalAssetSharedItems
FROM ( SELECT d.contact, ARRAY_AGG({d.createdAt, d.digitalAsset, d.lastViewedAt, d.views}) AS digitalAssetSharedItems
       FROM (SELECT b.contact, b.createdAt, das.digitalAsset, das.lastViewedAt, SUM(das.views) AS views
             FROM `bluebonnet` AS b
             UNNEST b.digitalAssetSharedItems AS das
             WHERE b.`type` = 'digital-asset-shared'
                   AND b.tenant = 'tenant::01'
                   AND b.contact = "contact::45"
             GROUP BY b.contact, b.createdAt, das.digitalAsset, das.lastViewedAt) AS d
        GROUP BY d.contact) AS d1;
5

Hi @vsr1

Cool thanks man, I works but is there a way I could group the digitalAssetSharedItems array items base on digitalAsset property? It seems that the digitalasset property is redundant.

6

SELECT s.*
FROM d1.digitalAssetSharedItems AS s
WHERE …
ORDER BY s.views
OFFSET 0 LIMIT 5

Change this by adding group by

OR

SEECT d1.contat, 
      (SELECT s.* 
       FROM d1.digitalAssetSharedItems AS s 
       WHERE ..........
       ORDER BY s.views
       OFFSET 0 LIMIT 5) AS digitalAssetSharedItems
FROM ( SELECT d.contact, ARRAY_AGG({d.createdAt,  d.lastViewedAt, d.views}) AS digitalAssetSharedItems
       FROM (SELECT b.contact, b.createdAt, das.lastViewedAt, SUM(das.views) AS views
             FROM `bluebonnet` AS b
             UNNEST b.digitalAssetSharedItems AS das
             WHERE b.`type` = 'digital-asset-shared'
                   AND b.tenant = 'tenant::01'
                   AND b.contact = "contact::45"
             GROUP BY b.contact, b.createdAt, das.lastViewedAt) AS d
        GROUP BY d.contact) AS d1;