Mongo on Demand

F85cfa2c6c69d94bc48ece99b7b2cb59?s=47 Max Mulatz
February 04, 2016

Mongo on Demand

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

F85cfa2c6c69d94bc48ece99b7b2cb59?s=128

Max Mulatz

February 04, 2016
Tweet

Transcript

  1. MONGO ON DEMAND NOSQL IN POSTGRESQL

  2. max klappradla

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

    tabular relations.." wikipedia
  4. WHY?

  5. None
  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",
  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" }
  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" ] } }
  9. MONGO POSTGRES in

  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
  11. None
  12. JSON JSONB INDEXES, FUNCTIONS DOUBLICATED KEYS, PRESERVE ORDERING

  13. CREATE TABLE users ( id serial not null, settings json

    not null default '{}', preferences jsonb not null default '{}' ); CREATE
  14. INSERT INTO users (settings) VALUES (1, '{ "newsletter":true, "interests":[ "biking",

    "programming" ] }' ); INSERT
  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
  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
  17. DEMO

  18. RAILS?

  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
  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
  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
  22. DEMO