JSON as a SQL Datatype

JSON as a SQL Datatype

• 1.
  JSON in aSQLWorld By Robert Sell Twitter: @DukeOfNode Linkedin: http://www.linkedin.com/in/robert-sell
• 2.
  Who Am I •Application Architect & Data Engineer akaThe Data Donkey • The guy who creates money from information via our Analytic Product • In another life I ran and managed ecommerce operations and integrated them into IT Systems • Spent a large amount of time moving data and analyzing it so people can make good business decisions
• 3.
  Popular Ways toEncode KeyValue Information • UncomplexTuples (CSV,TSV) => (Rob, Sell) • XML (Soap) => <person><firstName>Rob</firstName><lastName>Sell</ lastName ></person> • JSON (YAML, CSON) => {“person”:{“firstName”: “Rob”, “lastName”: “Sell”}}
• 4.
  Great JSON Properties •First Class Support for Basic DataTypes: Numbers, Strings, Boolean • First Class Support for Collections! • First Class Support for Hierarchical Structures! • Other Stuff: Readable, Not Bloated, Easily ParsibleVia Javascript
• 5.
  NoSQL Advantages • Easyto get started • Loosely structured so easier to change • Easier to scale out then some SQL databases • Data is in a form closer to how the program thinks of it
• 6.
  WorkingWith Data Causesa War • OnlyYou Can Prevent Forest Fires !!! • SQL uses set and aggregation operators to manipulate and aggregate information • JSON and OOP languages uses map and reduce • SQL no longer equals flat data with JSON in it
• 7.
  JSON Comes toSQL • JSON in Postgres 9.4 and JSONB in 9.5 • MySQL in 5.7 • T-SQL in 2016
• 8.
  Why Use JSONin SQL • SQL is all ready your primary system for storing and transmitting data • It’s painful to make temporary classes for storage (statically typed languages) • Decent performance with very little transformation necessary • Databases normally apply logic to information so apply logic to JSON instead of in an application • You don’t want to lose information
• 9.
  Use Cases • DataWarehousing •Need to Backup data from external platform and use it later • Don’t want to pay for ETL applications to move data to SQL databases (Zendesk, Shopify, etc)