JSON Basics

JSON

• JSON is a minimal, textual, language-independent representation widely used for data exchange.
• It is a notation for representing data in a format that is easy for humans to read and write.
• JSON is versionless and does not have namespaces, it uses context to avoid ambiguity.
• Character encoding is strictly Unicode.
• JSON is not a document format and is not a markup language.

Generating JSON from Relational Data

• JSON can be generated from relational data using object-relational mapping.
• Example: json.build_object(‘ID’, 12345, ‘name’, ‘Einstein’)
• JSON collections can be created using aggregation functions such as json_agg in PostgreSQL.

Semi-Structured Data Models

• Semi-structured data models have flexible schemas, allowing each tuple to have a different set of attributes.
• They support multivalued data types, such as sets, multisets, and key-value maps.
• Examples: set of interests {‘basketball, ‘La Liga’, ‘cooking’, ‘anime’, ‘jazz’}, key-value map {(brand, Apple), (ID, MacBook Air), (size, 13), (color, silver)}.
• Operations on maps: put(key, value), get(key), delete(key).

Arrays

• Arrays are widely used for scientific and monitoring applications.
• Examples: readings taken at regular intervals can be represented as an array of values instead of (time, value) pairs.
• Array databases provide specialized support for arrays, such as compressed storage and query language extensions.
• Examples of array databases: Oracle GeoRaster, PostGIS, SciDB.

Object-Relational Data Model

• The object-relational data model provides a richer type system with complex data types and object orientation.
• Applications are often written in object-oriented programming languages, which do not match the relational type system.
• Approaches for integrating object-orientation with databases include:
  • Building an object-relational database by adding object-oriented features to a relational database.
  • Automatically converting data between programming language models and relational models using object-relational mapping.
  • Building an object-oriented database that natively supports object-oriented data and direct access from programming languages.

Object-Relational Database Systems

• User-defined types can be created using object-relational database systems.
• Table types can be created using array and multiset data types.
• Example: create type Person (ID varchar(20) primary key, name varchar(20), address varchar(20)) ref from(ID); create table people of Person;

Type and Table Inheritance

• Type inheritance allows for the creation of new types based on existing types.
• Example: create type Student under Person (degree varchar(20)); create type Teacher under Person (salary integer);
• Table inheritance syntax in PostgreSQL and Oracle: create table students (degree varchar(20)) inherits people; create table teachers (salary integer) inherits people;

Reference Types

• Creating reference types: create type Person (ID varchar(20) primary key, name varchar(20), address varchar(20)) ref from(ID); create table people of Person;
• Using references in path expressions: select head->name, head->address from departments;

Object-Relational Mapping

• Object-relational mapping (ORM) systems allow for the specification of mapping between programming language objects and database tuples.
• Automatic creation of database tuples upon creation of objects.
• Automatic update/delete of database tuples when objects are updated/deleted.
• Interface to retrieve objects satisfying specified conditions.
• Examples: Hibernate ORM for Java, Django ORM for Python.

XML

• XML: Extensible Markup Language.
• Earlier generation notation, still used extensively.
• XML uses tags to mark up text.
• Example: Intro.to Computer Science “..” specifies the parent.doc(name) returns the root of a named document.