MONGO ON DEMAND NOSQL IN POSTGRESQL

max klappradla

NoSQL "...data which is modeled in means other than the tabular relations.." wikipedia

WHY?

No content

{ "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",

{ "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" }

{ "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" ] } }

MONGO POSTGRES in

# 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

No content

JSON JSONB INDEXES, FUNCTIONS DOUBLICATED KEYS, PRESERVE ORDERING

CREATE TABLE users ( id serial not null, settings json not null default '{}', preferences jsonb not null default '{}' ); CREATE

INSERT INTO users (settings) VALUES (1, '{ "newsletter":true, "interests":[ "biking", "programming" ] }' ); INSERT

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

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

DEMO

RAILS?

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

USE class User < ActiveRecord::Base scope :programmers, -> { where('preferences @> ?', {interests: ['programming']}.to_json ) } end user.preferences['newsletter'] = true user.save #=> updates whole preferences document

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

DEMO