Databases

Lecture 8: Alternative data models

• Lecture notes: chapter 9
• Book: parts of chapter 11, 12

Data models

Different kinds of databases

• Relational databases
• Graph databases
• Key/value stores
• Document databases
  • Semi-structured data (SSD)
  • Document formats: XML, JSON, YAML, …

Today

Semi-structured document formats

• HTML
• XML & XPath
• JSON
• JSON in PostgreSQL
• JSON Schema & JSON Path

XML or JSON

• Both XML and JSON are used as formats for data exchange
  • XML appeared in the 1990s (after the rise of HTML).
  • JSON appeared in the 2000s (after the rise if JavaScript).
• The focus in the course has moved from XML to JSON.
  • JSON is used in Task 4 in the assignment.
  • Old exam questions about XML can be translated into the corresponding JSON questions.

HTML: HyperText Markup Language

• HTML is the language for web pages
• Example: HTML for a simple web page:

  
  Visit the web page

• Key ingredients:
  • Text
  • Elements that indicate the structure of the web page
    • Delimited by start tags and end tags, e.g. <h1>…</h1>
    • Elements can contain text and other elements.
    • Some elements have attributes, e.g. links: <a href="…">…</a>

Documents are trees

XML: Extensible Markup Language

• Like HTML, but for arbitrary tree-structured data

  

• Different document types use different sets of elements

Attributes vs elements

• Text in XML can appear inside elements or in attributes
  • <university> <name>Chalmers</name> <website>www.chalmers.se</website> </university>
• <university name="Chalmers" website="www.chalmers.se"></university>
• It's a somewhat arbitrary choice…
• Side note: there is short-hand syntax for empty elements:
  <university name="Chalmers" website="www.chalmers.se"/>

Well-formed XML documents

• XML documents have a single root element.
• All elements have a start tag and an end tag.
  • Good: <grades><grade>3</grade><grade>4</grade><grade>5</grade></grades>
  • Bad: <grades><grade>3<grade>4<grade>5</grades>
  • Short-hand for empty elements: <tag/> instead of <tag></tag>
• Elements must be properly nested.
  • Good: <a><b>text</b></a>, compare with [(text)]
  • Bad: <a><b>text</a></b>, compare with [(text])

Document Type Definition

• An XML document can contain (or refer to) a DTD (Document Type Definition)
• A DTD specifies:
  • The set of elements that are allowed.
  • How elements can be nested.
  • Which attributes elements have.
• An XML document is valid if it complies with the constraints expressed in its DTD.
• An HTML document can be seen as a XML document of a specific type.
  • XHTML follows the rules of XML.
  • Normal HTML syntax is more relaxed, e.g. it allows some end tags to be omitted.

XPath queries

Since documents are trees…

• We can access parts of a document by specifying a path from the root to the parts.
• Examples of XPath queries:
  • /
  • /city
  • /city/name
  • /city/university/website
  • /city//website

JSON: JavaScript Object Notation

• JSON documents are native values in JavaScript.
  • Strings, numbers, booleans and null
  • Arrays and objects
• Example:

  • {"city": { "name": "Gothenburg",
               "universities":
                 [{"name": "Chalmers",
                   "website": "www.chalmers.se"},
                  {"name": "University of Gothenburg",
                   "website": "www.gu.se"}
                 ]
             }
    }
    

JSON

Syntax of JSON values

• value ::= null  false  true  number  "string" array  object  array ::= [ value, …, value ]  object ::= { member, …, member }  member ::= "key" : value

JSON

Examples of JSON values

• 7  -3.14  5.3e6

• true  false

• null

• "Manhattan"

• {}

• {city:"Boston", population:700000}

• []

• [2,3,5,7,11,13]

• [1, "hello", {"start":0}, true, [3,4,5]]

JSON

Accessing parts of a JSON value

• {"city": { "name": "Gothenburg",
             "universities":
               [{"name": "Chalmers",
                 "website": "www.chalmers.se"},
                {"name": "University of Gothenburg",
                 "website": "www.gu.se"} ] } }
  

• Accessing parts (in JavaScript, assuming the above documents is called

  d

  )

  • d.city.name == "Gothenburg"

  • d.city.universities[0] == {"name": "Chalmers",
                               "website": "www.chalmers.se"}

  • d.city.universities[1].website == "www.gu.se"

  • d.name == null 

    (or error…)

JSON

Another example: a table as JSON value

• Countries

  country	currency	value
  Finland	EUR	1.09
  Estonia	EUR	1.09
  Sweden	SEK	0.12

• Corresponding JSON value

• [{"country": "Finland",currency: "EUR", value: 1.09},
   {"country": "Estonia",currency: "EUR", value: 1.09},
   {"country": "Sweden", currency: "SEK", value: 0.12}]
  

XML vs JSON

• XML
  <teacher>  <firstname>Thomas</firstname>  <lastname>Hallgren</lastname>  <course>Databases</course>  </teacher>
• JSON

• { "teacher" : {
      "firstname": "Thomas",
      "lastname": "Hallgren",
      "course": "Databases"
    }
  }
  

JSON in PostgreSQL

• PostgresSQL (especially version 12) has extensive support for JSON
• Types

  • JSON

    : JSON values stored as text

  • JSONB

    : JSON values converted to an internal binary format
    • More efficient for queries
• Functions
  • Various functions to create JSON values
  • Operators to access parts of JSON values

JSON in PostgreSQL

Example (by Jonas Duregård)

Database for some kind of social media applicaiton

• CREATE TABLE Users(uname TEXT PRIMARY KEY, email TEXT UNIQUE);
  
  CREATE TABLE Posts(
    id SERIAL PRIMARY KEY,
    author TEXT NOT NULL REFERENCES Users(uname),
    created TIMESTAMP NOT NULL,
    content JSONB NOT NULL
  );
  

• See the complete example in jsonpostgres.sql.

JSON in PostgreSQL → Example (by Jonas Duregård)

Inserting data

• INSERT INTO Posts VALUES (
    DEFAULT, 
    'Jonas', 
    CURRENT_TIMESTAMP,
    '{"link" : "https://xkcd.com/327/", "preview":true}' :: JSONB
  );
  
  INSERT INTO Posts VALUES (
    DEFAULT, 
    'Jonas', 
    CURRENT_TIMESTAMP,
    '{"picture" : "funnycat.gif", "prop":{"size":15434}}' :: JSONB
  );
  

JSON in PostgreSQL → Example (by Jonas Duregård)

Try it in PostgreSQL

Files

• tables.sql
• inserts.sql
• queries.sql

JSON in PostgreSQL → Example (by Jonas Duregård)

Queries to try

• SELECT * FROM Posts;
  
  SELECT id,content->'link' AS Url FROM Posts;
  
  SELECT id,content FROM Posts WHERE content->'picture' IS NOT NULL
  
  SELECT * FROM Posts WHERE content->'preview' = 'true'
  
  SELECT id,content->'prop'->'size' AS postsize FROM Posts
  

JSON in PostgreSQL → Example (by Jonas Duregård)

Building JSON objects in query results

• Use

  jsonb_build_object

   to build a JSON object from normal SQL values

• SELECT id, jsonb_build_object(
                 'postid',id,
                 'user',author
                 ) AS jsondata
  FROM Posts;
  

• Aggregate the authors of all posts into a JSON array:

• SELECT jsonb_agg(author) FROM Posts;
  

• All posts grouped by author, using || to join two JSON objects:

• SELECT author,jsonb_agg(jsonb_build_object('id',id) || content)
  FROM Posts
  GROUP BY author;
  

JSON Schema and JSON Path

• See slides by Matthías Páll Gissurarson.
• 

Further reading

• Introducing JSON, Introduktion till JSON.
• PostgreSQL JSON tutorial.
• jsonpostgres.sql: example by Jonas Duregård.
• JSON Schema, JSON Path evaluator
• PostgreSQL reference manual:
  • JSON Types,
  • JSON Functions and Operators
• XML validation and XPath queries in Query Converter.
• XPath on Wikipedia.