CODEWITHSUNDEEP
couchbasesql++
Jaco Van Niekerk
Jaco Van Niekerk

Reputation: 4182

N1QL query on array

I have the following structure in my Couchbase document database:

{
  "documentType": "connection",
  "id": "09690237-9ef5-4381-93dc-7312f7417f82",
  "entities": [
    {
      "id": "8a282f31-5d6c-4741-909f-a84ef48571af",
      "type": "item",
    },
    {
      "id": "01f6a9eb-0d7e-4495-a90f-f96a38aef621",
      "type": "group",
    }
  ]
}

Numerous 'items' exists for each 'group' in the database. I want to retrieve a list of all the items for a specific group id. For example, if "01f6a9eb-0d7e-4495-a90f-f96a38aef621" is supplied I need something like:

[
  "8a282f31-5d6c-4741-909f-a84ef48571af",
  "9a282f31-5d6c-4741-909f-a84ef48571af",
  ...
]

However, I get a whole lot of small arrays called 'entity' in separate little structures and I cannot combine them. This is my N1QL query at the moment:

select array entity.id for entity in entities.entities when entity.type = 'item' end as entity
from entities
where entities.documentType = 'connection'
      and any entity within entities satisfies entity.id = '01f6a9eb-0d7e-4495-a90f-f96a38aef621'
                                           and entity.type = 'group' end

I am a total newbie at N1QL, so some explanation will also be appreciated.

Thank you.

Upvotes: 2

Views: 702

Answers (1)

vsr
vsr

Reputation: 7414

select ARRAY_FLATTEN(ARRAY_AGG(ARRAY entity.id for entity in entities.entities when entity.type = 'item' end),2) as entity
from entities
where entities.documentType = 'connection'
      and any entity IN entities satisfies entity.id = '01f6a9eb-0d7e-4495-a90f-f96a38aef621' and entity.type = 'group' end

Upvotes: 3

Related Questions