Couchbase aggregation of nested field

Question

I'm completely new to Couchbase and, whilst learning pretty quickly, I'm struggling to see if it's possible to create a query / view to aggregate data stored in a nested field, where the structure of the nested field is uncertain.

For example, considering the following documents:

[
    {
        "customer": 1,
        "user": 21,
        "group": 2,
        "data": {
            "cat": 2,
            "dog": 1
        }
    },
    {
        "customer": 1,
        "user": 22,
        "group": 3,
        "data": {
            "cat": 1,
            "rabbit": 3
        }
    },
    {
        "customer": 1,
        "user": 23,
        "group": 2,
        "data": {
            "budgie": 1,
            "mouse": 1,
            "dog": 1
        }
    }
]

I would like to have a query that would, for instance, group by "group" and "customer" and produce a result similar to the following:

[
    {
        "customer": 1,
        "group": 2,
        "data": {
            "cat": 2,
            "dog": 2,
            "budgie": 1,
            "mouse": 1
        }
    },
    {
        "customer": 1,
        "group": 3,
        "data": {
            "cat": 1,
            "rabbit": 3
        }
    }
]

The top level data (customer / user / group / data) will be static and always contain those fields, but, the items in "data" are free-form.

Is this something that's possible natively in Couchbase, or would I need to do this programmatically with the client application?

I've settled for the programmatic solution for the time being but, would be nice to know.

geraldss geraldss · Accepted Answer · 2016-11-08T18:57:48

This will work on Couchbase 4.5.1. For the upcoming 4.6, change p.`value` to p.val.

SELECT customer, `group`, OBJECT d.name : d.sum FOR d IN ARRAY_AGG( { "name":name, "sum":sum } ) END AS data
FROM
(
SELECT d.customer, d.`group`, p.name, SUM( p.`value` ) AS sum
FROM default AS d UNNEST OBJECT_PAIRS( data ) AS p
GROUP BY d.customer, d.`group`, p.name
) AS s
GROUP BY customer, `group`;

Couchbase aggregation of nested field

1 Answers