Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Mongo on Demand

Max Mulatz
February 04, 2016

Mongo on Demand

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

Max Mulatz

February 04, 2016
Tweet

More Decks by Max Mulatz

Other Decks in Programming

Transcript

  1. { "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",
  2. { "id": "100000000000000", "email": "[email protected]", "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" }
  3. { "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" ] } }
  4. # 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
  5. CREATE TABLE users ( id serial not null, settings json

    not null default '{}', preferences jsonb not null default '{}' ); CREATE
  6. 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
  7. 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
  8. 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
  9. USE class User < ActiveRecord::Base scope :programmers, -> { where('preferences

    @> ?', {interests: ['programming']}.to_json ) } end user.preferences['newsletter'] = true user.save #=> updates whole preferences document
  10. 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