Mongo on Demand

Talk at vienna.rb #30
NoSQL in PostgreSQL without giving up your beloved relational database.

Transcript

1. MONGO ON DEMAND
   NOSQL IN POSTGRESQL
   

   View Slide

2. max
   klappradla
   

   View Slide

3. NoSQL
   "...data which is modeled in means
   other than the tabular relations.."
   wikipedia
   

   View Slide

4. WHY?
   

   View Slide

5. View Slide

6. {
   "meta": {
   "disclaimer": "openFDA is a beta research project and not for clinical use. While we make every effort to ensure that data is accurate, you should assume all results are unvalidated.",
   "license": "http://open.fda.gov/license",
   "last_updated": "2016-01-28",
   "results": {
   "skip": 0,
   "limit": 1,
   "total": 4160934
   }
   },
   "results": [
   {
   "receivedate": "20040319",
   "patient": {
   "patientonsetage": "56",
   "reaction": [
   {
   "reactionmeddrapt": "ARTHRALGIA"
   },
   {
   "reactionmeddrapt": "OEDEMA PERIPHERAL"
   },
   {
   "reactionmeddrapt": "PURPURA"
   }
   ],
   "patientonsetageunit": "801",
   "patientsex": "1",
   "drug": [
   {
   "drugcharacterization": "2",
   "medicinalproduct": "PYOSTACINE (PRISTINAMYCIN)"
   },
   {
   "drugcharacterization": "2",
   "openfda": {
   "product_ndc": [
   "0004-1963",
   "0004-1964"
   ],
   "nui": [
   "N0000011161",
   "N0000175488"
   ],
   "generic_name": [
   "CEFTRIAXONE SODIUM"
   ],
   "spl_set_id": [
   "9467f6c9-3e59-45c6-a1be-77200f2d4554"
   ],
   "pharm_class_cs": [
   "Cephalosporins [Chemical/Ingredient]"
   ],
   "brand_name": [
   "ROCEPHIN"
   ],
   "manufacturer_name": [
   "Genentech, Inc."
   ],
   "unii": [
   "75J73V1629"
   ],
   "rxcui": [
   "204871",
   "105212"
   ],
   "spl_id": [
   "86e3103c-9d8b-4693-b5db-3fd62330c754"
   ],
   "substance_name": [
   "CEFTRIAXONE SODIUM"
   ],
   "product_type": [
   "HUMAN PRESCRIPTION DRUG"
   ]
   },
   "medicinalproduct": "ROCEPHIN"
   },
   {
   "drugcharacterization": "2",
   "openfda": {
   "product_ndc": [
   "55111-160",
   "55111-161",
   "55111-162",
   "0093-7181"
   ],
   "nui": [
   "N0000175937",
   "N0000007606"
   ],
   "package_ndc": [
   "16571-130-50",
   "55111-161-50",
   "61314-012-05",
   "17478-713-11",
   "17478-713-10",
   ],
   "generic_name": [
   "OFLOXACIN",
   "OFLOXAXIN"
   ],
   "spl_set_id": [
   "8db221b1-32f3-f6ca-e404-71f56a860d08",
   "1d19a6db-6da5-e7de-f929-2d18bdfa2cf5",
   "95b9fc17-9c94-4762-910c-df0bb0b2aa85",
   "7aab4449-3dda-4e2c-8e40-b3244a548bf5"
   ],
   "pharm_class_cs": [
   "Quinolones [Chemical/Ingredient]"
   ],
   "unii": [
   "A4P49JAZ9H"
   ],
   "rxcui": [
   "207202",
   "312075",
   "198048",
   "198049",
   "198050",
   "242446"
   ],
   "route": [
   "AURICULAR (OTIC)",
   "ORAL",
   "OPHTHALMIC"
   ],
   "application_number": [
   "ANDA076407",
   "ANDA076182",
   "NDA019921",
   "ANDA091656",
   "ANDA076128",
   "ANDA078222",
   "ANDA090395",
   "ANDA076527",
   "ANDA076622",
   "ANDA076513",
   "ANDA076616",
   "ANDA202692",
   "ANDA078559",
   

   View Slide

7. { "id": "100000000000000",
   "email": "vienna@rb.com",
   "first_name": "Vienna",
   "gender": "other",
   "last_name": "Rb",
   "link": "https://www.facebook.com/vienna.rb",
   "locale": "de_AT",
   "name": "Vienna Rb",
   "timezone": "2",
   "updated_time": "2014-03-31T08:26:19+0000",
   "username": "viennarb",
   "verified": "false"
   }
   {
   "username":"vienna-rb",
   "bio":"",
   "website":"",
   "profile_picture":"http://instagram.com/123.jpg",
   "full_name":"",
   "counts":{"media":1,"followed_by":0,"follows":0},
   "id":"1000000001"
   }
   

   View Slide

8. {
   "preferences":{
   "newsletter":false,
   "web_notifications":[
   "Notifications::CommentOnUsersPost",
   "Notifications::AlsoCommentedPost",
   "Notifications::NewWallComment"
   ],
   "immediate_mail_notifications":[
   "Notifications::AlsoCommentedPost",
   "Notifications::LocationApproved",
   "Notifications::MeetingUpdated",
   "Notifications::NewWallComment"
   ],
   "daily_mail_notifications":[
   "Notifications::NewUserPost",
   "Notifications::NewLocationPost",
   "Notifications::NewMeeting"
   ]
   }
   }
   

   View Slide

9. MONGO
   POSTGRES
   in
   

   View Slide

10. # Serialize a preferences attribute.
    class User < ActiveRecord::Base
    serialize :preferences
    end
    # Serialize preferences using JSON as coder.
    class User < ActiveRecord::Base
    serialize :preferences, JSON
    end
    # Serialize preferences as Hash using YAML coder.
    class User < ActiveRecord::Base
    serialize :preferences, Hash
    end
    

    View Slide

11. View Slide

12. JSON
    JSONB
    INDEXES, FUNCTIONS
    DOUBLICATED KEYS,
    PRESERVE ORDERING
    

    View Slide

13. CREATE TABLE users (
    id serial not null,
    settings json not null default '{}',
    preferences jsonb not null default '{}'
    );
    CREATE
    

    View Slide

14. INSERT INTO users (settings) VALUES (1,
    '{
    "newsletter":true,
    "interests":[
    "biking",
    "programming"
    ]
    }'
    );
    INSERT
    

    View Slide

15. SELECT count(*)
    FROM users
    WHERE preferences ->> 'newsletter' = 'true';
    SELECT count(*)
    FROM users
    WHERE preferences -> 'interests' ? 'biking';
    SELECT count(*)
    FROM users
    WHERE preferences @> '{"newsletter":true}';
    QUERY
    

    View Slide

16. CREATE INDEX idxpreferences_interests_exp
    ON users ((preferences->'interests'));
    CREATE INDEX idxpreferences_news_exp
    ON users ((preferences->>'newsletter'));
    CREATE INDEX idxpreferences_gin
    ON users USING GIN (preferences);
    INDEX
    

    View Slide

17. DEMO
    

    View Slide

18. RAILS?
    

    View Slide

19. CREATE
    class CreateUsers < ActiveRecord::Migration
    def change
    create_table :users do |t|
    t.string :name, null: false
    t.jsonb :preferences, null: false, default: '{}'
    end
    add_index :users, :preferences, using: :gin
    end
    end
    

    View Slide

20. USE
    class User < ActiveRecord::Base
    scope :programmers, -> {
    where('preferences @> ?',
    {interests: ['programming']}.to_json
    )
    }
    end
    user.preferences['newsletter'] = true
    user.save
    #=> updates whole preferences document
    

    View Slide

21. ACCESS
    class User < ActiveRecord::Base
    store_accessor :preferences, :newsletter, :interests
    end
    user = User.new(newsletter: false, interests: ['runninng'])
    user.preferences
    #=> {"newsletter" ...
    user.newsletter
    #=> false
    user.preferences[:newsletter]
    #=> false
    

    View Slide

22. DEMO
    

    View Slide