Introduction

Last week, we began talking about the semi-structured data model.
We said in this model the schema is often implied by the data rather than being declared separately.
This data model can be useful when doing information integration of data stored in different DBMS in slightly different formats.
It is also the data model of many web formats.
We said a database of semi-structured data is a collection of nodes, with a distinguished root node from which all other nodes can be reached.
We then look at how XML data is a source of semi-structured data.
We said how to create well-formed XML-data, and how to create new XML languages that XML documents/databases might conform to.
We then introduced the Postgres database.
We wanted to use Postgres databases rather than Mysql or Sqlite, because, like DB2 and MSSQL, Postgres supports SQL/XML.
SQL/XML is part 14 of the SQL spec, initially, created in 2003.
We briefly showed how creating, inserting, and querying XML data in Postgres works last day. Today, we start by mentioning a little about XML support in Mysql and Oracle, then saying some more about XPath. Finally, we will consider JSON as another semi-structured datasource.

XML in Other DBMSs

Different RDBMS database vendors support XML in different ways.
SQLite, perhaps the most ubiquitous database, does not support XML natively, so you would have to use some kind of mapping to tables approach to support it.

MySQL and MariaDB typically store XML in TEXT or LONGTEXT types and then use two functions ExtractXML(xml,xpath) and UpdateValue(xml, xpath, replacement) to manipulate the XML. For example, consider the following modifications of the MySQL docs

SELECT ExtractValue('<a><b>X</b><b>Y</b><b>Z</b></a>','//b[2]');

would return Y and

SELECT 
  UpdateXML('<a><b>CAT</b><d></d></a>', '/a', '<e>FOX</e>') AS val1, 
  UpdateXML('<a><b>CAT</b><d></d></a>', '/b', '<e>FOX</e>') AS val2, 
  UpdateXML('<a><b>CAT</b><d></d></a>', '//b', '<e>FOX</e>') AS val3,
  UpdateXML('<a><b>CAT</b><d></d></a>', '/a/d', '<e>FOX</e>') AS val4 , 
  UpdateXML('<a><d></d><b>CAT</b><d></d></a>', '/a/d', '<e>FOX</e>') 
  AS val5 
  \G 
  *************************** 1. row *************************** 
  val1: <e>FOX</e> 
  val2: <a><b>CAT</b><d></d></a> 
  val3: <a><e>FOX</e><d></d></a> 
  val4: <a><b>CAT</b><e>FOX</e></a> 
  val5: <a><d></d><b>CAT</b><d></d></a>

Oracle has its own XMLType and XMLIndex for XML columns and indexes. It is uses XQuery for querying XML data.

Quiz

Which of the following is true?

Stored procedures in Mysql do not support local variables.
OUT variables in a Mysql stored procedure's parameter list will be returned by the procedure.
There is no spec language used to define new XML languages, you can only check if a document is well-formed.

SQL/XML DML commands

Let's return to Postgres now and look at the data manipulation language for SQL/XML in more detail.

We can create a table with an XML column using the syntax like the below:

CREATE TABLE PRODUCT(
PRODNR CHAR(6) NOT NULL PRIMARY KEY, 
PRODNAME VARCHAR(60) NOT NULL, 
PRODTYPE VARCHAR(15),
AVAILABLE_QUANTITY INTEGER,
REVIEW XML);

Here we are imagining reviews for a product come in some kind of XML format, but we are not specifying a DTD or Schema.

We can insert rows in this new table with a traditional INSERT STATEMENT:

INSERT INTO PRODUCT VALUES('000120', 'Conundrum', 'white', 12,
XMLPARSE(CONTENT '<review><author>Bart Baesens</author>
<date>27/02/2017</date>
<description>This is an excellent white wine with intriguing aromas 
of green apple, tangerine and honeysuckle blossoms.
</description><rating max-value="100">94</rating>
</review>'));

Notice we didn't give a doctype or schema in our review that we parsed to XML type with XMLPARSE. That is why we used the keyword CONTENT. If we wanted a DTD or schema we could use the keyword DOCUMENT.

SQL/XML Querying

SQL/XML support various functions for outputting XML from database tables.
XMElement has the syntax: xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content, ...]) and can be used to output a tag:
```
SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'content');

outputs

<foo bar="2020-09-09">content</foo>
```
XMLForest outputs a forest from a sequence of provided content. It has the syntax: xmlforest(content [AS name] [, ...]). For example,
```
SELECT xmlforest('abc' AS foo, 123 AS bar);

          xmlforest
------------------------------
 <foo>abc</foo><bar>123</bar>
```

XMLAgg aggregates return XML values into one document. For example,

CREATE TABLE test (y int, x xml);
INSERT INTO test VALUES (1, '<foo>abc</foo>');
INSERT INTO test VALUES (2, '<bar/>');
SELECT xmlagg(x) FROM test;
        xmlagg
----------------------
 <foo>abc</foo><bar/>

This can also work with an ORDER BY clause:

SELECT xmlagg(x ORDER BY y DESC) FROM test;
        xmlagg
----------------------
 <bar/><foo>abc</foo>

Other functions include: XMLPI (for making pi node), XMLComment (for outputting comments), XMLConcat (concatenating XML documents), XMLRoot (for changing root nodes of a document).

XPath Expressions

Last week, we briefly mentioned XPath queries without being too specific.
XPath is a language used to refer to parts of an XML document.
In what follow I will try to give a flavor of XPath without completely spelling out its syntax.
Generally, an XPath expression starts from a context node then navigates down the document tree to the collection of nodes that are desired.
For example,
```
doc("winecellar.xml")/winecellar/wine
```
matches all wine nodes (and their contents) in the winecellar.xml file which start from the whole document, then immediately go through a winecellar sub-tag then have a wine tag.
We can use the expression // to skip multiple levels in the tree. So winecellar//wine would match any wine element that was a descendant of a winecellar element.
We can use wild-cards like /*/ to match a single tag name without specifying what it is.
If we are interested in the 2nd wine element children of a particular winecellar, we could use the syntax:
```
doc("winecellar.xml")/winecellar/wine[2]
```
We can place requirements on the values of attributes of a element by using [...] notation. For example:
```
doc("winecellar.xml")/winecellar/wine[price > 20]/name
```
returns the names of all wine nodes with price attribute greater than 20.
Finally, if you want the value of an attribute to be returned rather than the contents of a node, you can use @. For example, //img/@src to get all the url's from img tags in an HTML document.

SQL/XML Querying with XPath

XPath can be incorporated into XML queries in a variety of ways:
- XMLExists is a predicate which returns true or false depending on whether the particular XML document has nodes satiafying an XQuery (only XPath subset of XQuery works for Postgres) expression:
```
SELECT XMLEXISTS('//town[text() = ''Toronto'']' 
PASSING BY REF '<towns><town>Toronto</town><town>Ottawa</town></towns>');
```
  would return t (for true).
- You can also use the XPATH function to get path a portion of a document:
```
SELECT XPATH('//author', REVIEW) FROM PRODUCT;
```
- As you might expect you can also use XPATH in a WHERE clause:
```
SELECT xpath('//author/text()', REVIEW) 
FROM PRODUCT 
WHERE CAST(xpath('//author/text()', REVIEW) AS TEXT) LIKE '%B%';
```

JSON

We next consider a second semi-structured data format: JSON.
JSON Stands for Javascript Object Notation.
It is commonly used for sending data when REST is used by Javascript can immediately use the data.

Primitive types in JSON are written as you expect:

12.3  -- an example Number
"hi there" -- an example String
true -- an example Boolean, other possibility false

Arrays are written in square brackets and comma separated:
```
[1, 4, 9]
```
Objects are written in braces and the name value pair are separated by a colon:
```
{"bob": 29,  "sally" : 35}
```
JSON data can be assigned to a variable in Javascript as an object with the syntax:
```
myObj = eval( "(" + data + ")" );
```

SQL/JSON

SQL 2016 and more fully SQL 2023 add support for JSON data and these features are largely supported by Postgresql.
My examples are modified from PostgreSQL JSON Tutorial.
First, we create a new tables with a JSON column:
```
CREATE TABLE CART (
    id SERIAL PRIMARY KEY,
    details JSON NOT NULL
);
```
A serial column will count up for us if we don't bother to include it in inserts (like Mysql AUTOINCREMENT)

We can insert rows into the JSON column with commands like:

INSERT INTO CART (details)
VALUES('{ "customer": "Chris Pollett", "items": {"product": "computer","qty": 1}}'),
('{ "customer": "Sponge Bob", "items": {"product": "pineapple","qty": 5}}');

Here we are inserting two tuples into the table.

We can then perform queries using the following syntax:
```
SELECT details -> 'customer' AS customer
FROM CART;
SELECT details ->> 'customer' AS customer
FROM CART;
```
The -> operator gets a JSON object out, the ->> operator retrieves a specific node as text.
Operators can be chained to give a path through a JSON document to a specific node we want:
```
SELECT details -> 'items' ->> 'product' as product
FROM CART
ORDER BY PRODUCT;
```

JSON in the WHERE Clause

JSON paths can also be used in the WHERE clause of queries:

SELECT details ->> 'customer' AS customer
FROM CART
WHERE details -> 'items' ->> 'product' = 'pineapple';

We might have to CAST the returned value (if other than a string) when using it in a comparison:

SELECT
    details ->> 'customer' AS customer,
    details -> 'items' ->> 'product' AS product
FROM CART
WHERE CAST ( details-> 'items' ->> 'qty' AS INTEGER) = 5

JSON and Aggregations

JSON paths can be used with aggregation operators:

SELECT 
   MIN (CAST (details -> 'items' ->> 'qty' AS INTEGER)),
   MAX (CAST (details -> 'items' ->> 'qty' AS INTEGER)),
   SUM (CAST (details -> 'items' ->> 'qty' AS INTEGER)),
   AVG (CAST (details -> 'items' ->> 'qty' AS INTEGER))
FROM
  CART;

The above would output:

 min | max | sum |        avg         
-----+-----+-----+--------------------
   1 |   5 |   6 | 3.0000000000000000

JSON Functions in Postgres

Postgres has a wide variety of built-in functions to manipulate JSON data. Below we give example common ones.

The json_each() function expands the outermost JSON object into a set of key-value pairs. For example,

SELECT json_each (details)
FROM CART;

would output:

                     json_each                     
---------------------------------------------------
 (customer,"""Chris Pollett""")
 (items,"{""product"": ""computer"",""qty"": 1}")
 (customer,"""Sponge Bob""")
 (items,"{""product"": ""pineapple"",""qty"": 5}")
(4 rows)

You can get the object keys of the outermost JSON object using json_object_keys. For example,

SELECT json_object_keys (details->'items')
FROM CART;

would output:

 json_object_keys 
------------------
 product
 qty
 product
 qty
(4 rows)

To determine the type of the JSON item being returned you can use json_typeof. For example,

SELECT json_typeof (details->'items'->'qty')
FROM CART;

would output:

 json_typeof 
-------------
 number
 number
(2 rows)

Finish Semi-Structured Data

Outline

Introduction

XML in Other DBMSs

Quiz

SQL/XML DML commands

SQL/XML Querying

XPath Expressions

SQL/XML Querying with XPath

JSON

SQL/JSON

JSON in the WHERE Clause

JSON and Aggregations

JSON Functions in Postgres