Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to query relations in NoSQL?

Tags:

mongodb

nosql

I have decided to move my database from MySQL to Mongo because majority of the time, my data is not structured. And it allowed me possibilities that was too complex in a traditional SQL.

There is one problem that I am currently facing and how to approach a traditional relational model of SQL in NoSQL. I have read many times that NoSQL is not designed to deal with relations. Do I need to add them as an array to the document with a relation?

Here is one situation that has made me stuck. In SQL I had a separate table for oauth access tokens that has user_id, client_id, access_token, expires as its attributes. It was 1-N relation between a user and an access_token. How would I do that in NoSQL? By adding an array field oauth_tokens? If I do that, how do I search for the token in the array? How do I query

search for a document where the _id is $user_id and there is an element
with $token in the access_tokens array?
like image 914
Gasim Avatar asked Nov 01 '22 16:11

Gasim


1 Answers

You have at least 2 options here:

  1. You can store oauth_tokens in separate collection (just like you had in MySQL in other table), and add to oauth_token field like user_id containing _id value for this current user from users collection. Finding tokens for specified user is just searching in oauth_tokens collection for documents with given user_id. Keep in mind that this kind of relation isn't "supported" in any way by Mongo - database will not help you keeping values of field user_id correct.

Example:

db.tokens.insert({ client_id : "1", user_id : "20", access_token : "1234567890", expires : new Date(2014-12-31)})

query:

db.tokens.find({user_id:"20"})
  1. Just like you wrote: you can embedded tokens in user document and query for existing tokens. Check docs to see how you can query for embedded documents: link
like image 92
Piotr Chowaniec Avatar answered Nov 10 '22 16:11

Piotr Chowaniec