# Help with query touching 2 indexes (like an exists join in sql)

**URL:** <https://discuss.elastic.co/t/help-with-query-touching-2-indexes-like-an-exists-join-in-sql/103571>\
**Category:** Elasticsearch\
**Created:** [October 11, 2017, 3:16pm UTC](https://discuss.elastic.co/t/help-with-query-touching-2-indexes-like-an-exists-join-in-sql/103571 "2017-10-11T15:16:48Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ptnewman](https://avatars.discourse-cdn.com/v4/letter/p/7993a0/32.png) [@ptnewman](https://discuss.elastic.co/u/ptnewman)\
**Post date:** [October 11, 2017, 3:16pm UTC](https://discuss.elastic.co/t/help-with-query-touching-2-indexes-like-an-exists-join-in-sql/103571/1 "2017-10-11T15:16:48Z")

</div>

i have a requirement to retrieve documents that a user has permissions to - not the elastic user but a user of our application. in our case, there are accounts and users, users have access to 1 or more accounts with some users having access to all accounts.

Now we have documents that include an account id, many documents can reference the same account, but a document will only reference a single account at any one time.

What i am trying to do is design the elastic structures such that given a user id, i can find all the documents that the user has permissions to see. question is how to design this. couple of options that i can think of:

- create an array within each document that holds all the user ids that can access it. the list could be large - possibly 5k users or more, and the list can change as users are added/deleted

- create a separate document that is basically a map of user ids to account ids - this would be a join query using terms or parent/child construct, not really sure how to do this efficiently

- create a separate document that is basically a map of user ids to document ids - same as previous option using terms or parent/child.

we will have about 5-10 million documents and these documents will be updated as well - mostly status changes.

in a relational db, i would create a doc table and a user table (map of users to accounts they have permission to). then write a query like  
select \* from doc where doc.account\_id in (select user.account\_id from users where user id = xxxx)  
or a similar where exists query.

any help would be greatly appreciated.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 8, 2017, 3:17pm UTC](https://discuss.elastic.co/t/help-with-query-touching-2-indexes-like-an-exists-join-in-sql/103571/2 "2017-11-08T15:17:25Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.