Get all unique keys from object of objects using N1QL in Couchbase

user_3840981 0
Viewed 719

If I have documents in Couchbase which all have a property foo which is an object of objects (all bars) like this:

{
    foo: {
        bar_1: {
            prop1: "hello",
            prop2: "world"
        },
        bar_2: {
            prop1: "i'm",
            prop2: "confused"
        }
    }
}

Using N1QL how can I select all unique bars (just the key not the values of bar)?

I'm currently using SELECT DISTINCT RAW OBJECT_NAMES(foo) FROM ... which returns an array of arrays each with the list of keys from each foo. I'm then parsing this into a C# string[][] and flattening it and making distinct.

The DISTINCT means that where documents have exactly the same bars they aren't duplicated but often this is not the case. This means that I currently have uneeded code execution in C# where I'm sure there's a nice way to do it all in N1QL.

n1ql couchbase database
1 Answers

UNNEST the Array 

   SELECT DISTINCT RAW n
   FROM default d
   UNNEST OBJECT_NAMES(d.foo) AS n;
user_60805360
Related