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

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.

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