$30 off During Our Annual Pro Sale. View Details »

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. 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": "[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"
    }

    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