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

Mongo on Demand

Avatar for Max Mulatz Max Mulatz
February 04, 2016

Mongo on Demand

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

Avatar for Max Mulatz

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