N1QL join with same Bucket with some logic

Question

I have below json.

First Campaign

"campaign|1000":{
  "_id": 1000,
  "_type": "Campaign",
  "country": 14,
  "created": "2016-03-08T18:30:00.000Z",
  "user": 45
  "bids":[{
          click:123     
         },
        {
           click:50
        }
         ]
}

second USER

"User|257"{
  "IMId": "",
  "IMType": 0,
  "_id": 257,
  "_type": "User",
  "children:[1,4,45,67,106]
  "roles":[4]
  "email": "[email protected]",
}

Now I need to join this json base on who has roles=[4] and join it children with campaign. here children is user like child 45 means "User|45" I am trying below query

select Users._id,count(Campaign._id) total from Inheritx Campaign 
join Inheritx Users on keys("User|"|| TOSTRING(Campaign.`user`))
join reachEffect realted_users on keys ARRAY "User|" || TOSTRING FOR  
c IN Users.children END  where Campaign._type="Campaign" and              
Users.roles=[4] group by Users._id

But I need to join with campaign and children of user whose users has roles is 4
I need output like below

{
 user:257
 clicks:157
}

Answer 1

Two options.

(1) You can start with campaign, join to users, and then filter on roles afterwards.

(2) You can start with users, filter on roles, and then use an INDEX JOIN to join users to campaigns.

See https://dzone.com/articles/join-faster-with-couchbase-index-joins