Get the size of documents returned by a query, *including* attachments

Hi All,

Per the title… I have a simple query to return the size of all documents owned by a particular user:

select meta(`datastore`).id, length(ENCODE_JSON(datastore)) as size from `datastore` where owner = "<id>"

Of course, this missed attachments.
The attachments all have their size listed in the metadata:

select * from `datastore` where meta().id = '<id>'

[
  {
    "datastore": {
      "_sync": {
        "attachments": {
          "blob_/attachment-=sq1oQ@]PW?>S&W+<L*AmWUGb^wU]@Aorax@b66c": {
            "content_type": "U4xu=&[lDwOfI=e-kcx(P*a{ti+zXjj3Uh[F>]Eq",
            "digest": "sha1-mgPDhHBU+GVI+ISW3c2M0JrX4m0=",
            "length": 901456,
            "revpos": 4,
            "stub": true
          },
          "blob_/attachment-Be*MfysfmfZIfMD~1skq9^2~1*kWP:OKud$Ev:-}d~1": {
            "content_type": "C]2y3&BdkrRCLH$a0Dxv1xgdQ=>6bZxCk#y-5qx2",
            "digest": "sha1-ULA7JU889NqFmgLwnOpVbP2ly9E=",
            "length": 603616,
            "revpos": 4,
            "stub": true
          },
          "blob_/text": {
            "content_type": "application/octet-stream",
            "digest": "sha1-Etwhh//GUEHE6TrYDEQqrQO2b3M=",
            "length": 784,
            "revpos": 4,
            "stub": true
          }
        },

Note: the content type and attachment name might look weird - that’s because its all encrypted.
So what I need to do is traverse the “attachments” object, and for each sub-object, return its length value, and then sum them all up.

Something like:
select sum(length) from attachments

I can’t seem to get my head around this query and was wondering if anyone can help.
Thx.
Paul.

If you are looking N1QL query by converting _sync.attachments OBJECT into ARRAY and iterate it using ARRAY looping syntax or Subquery expression.

SELECT
    META(d).id,  dsize+asize AS size
FROM `datastore` AS d
LET dsize =  LENGTH(ENCODE_JSON(d)),
    asize = ARRAY_SUM(ARRAY v.`length` FOR v IN OBJECT_VALUES(d._sync.attachments) WHEN is_number(v.`length`) END)
WHERE d.owner = "<id>";

OR

SELECT
      META(d).id,  dsize+asize AS size
FROM `datastore` AS d
LET dsize =  LENGTH(ENCODE_JSON(d)),
    asize = (SELECT RAW SUM(v.`length`) FROM OBJECT_VALUES(d._sync.attachments) AS v)[0]
FROM `datastore` AS d
WHERE d.owner = "<id>";

@vsr1 This is AWESOME. Thank you.

I made a couple of changes:

  • asize can be null causing dsize+asize to be NULL, so I used IFNULL
  • made it a complete sum

To anyone who wants this - the following query will return the approximate stored size of a set of synced documents and their attachments.

SELECT
      SUM(dsize+IFNULL(asize, 0))
FROM `datastore` AS d
LET dsize =  LENGTH(ENCODE_JSON(d)),
    asize = (SELECT RAW SUM(v.`length`) FROM OBJECT_VALUES(d._sync.attachments) AS v)[0]
WHERE d.owner = '<id>';

As an aside - I am assuming that couchbase is storing documents in JSON. That might not be true under the hood, but this function serves well enough for my purposes.

IFMISSINGORNULL(asize, 0) will be better

That makes sense. Thx.