Mongo on Demand

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