Use the Power of your Database: XML and JSON

Today databases have a lot of functionality that is often not used by software developers because they simply not know that this exists.

blog-post-img

Today databases have a lot of functionality that is often not used by software developers because they simply not know that this exists.
But knowing about this features can save a lot of time because you may write less code.

One of this hidden gems is the ability of producing XML or JSON data using a SQL SELECT statement.
In the examples Oracle Database is used but these features are also available in other Databases like PostgreSQL or SQL Server.

Model

Let’s assume we have the following model and we want to have employees with their phones.

ERD

XML

In Oracle Database producing XML would look like this.


select xmlelement(
        name "employees",
        xmlagg(xmlelement(name "employee",
            xmlattributes(EMPLOYEE.ID, EMPLOYEE.NAME),
            xmlelement(name "phones", (select xmlagg(xmlelement(name "phone",
                                              xmlattributes(PHONE.PHONENUMBER, PHONE.TYPE)))
                                       from PHONE
                                       where PHONE.EMPLOYEE_ID = EMPLOYEE.ID)))))
from "EMPLOYEE"
    

The result of the query will be this XML:


<employees>
    <employee ID="1" NAME="Ursula Friedman">
        <phones>
            <phone PHONENUMBER="031 333 11 12" TYPE="WORK"/>
        </phones>
    </employee>
    <employee ID="2" NAME="Hans Boss">
        <phones>
            <phone PHONENUMBER="031 333 11 01" TYPE="HOME"/>
            <phone PHONENUMBER="032 311 43 12" TYPE="WORK"/>
        </phones>
    </employee>
</employees>
    

JSON

For sure this also works for JSON in Oracle Database.


SELECT
	json_arrayagg("employee".employee)
FROM (SELECT
		json_object(
			KEY 'id' value EMPLOYEE.ID,
			KEY 'name' value EMPLOYEE.NAME,
			KEY 'phones' value json_arrayagg(json_object(KEY 'number' value PHONE.PHONENUMBER, KEY 'type' value PHONE.TYPE))
		) employee
	FROM EMPLOYEE JOIN PHONE ON PHONE.EMPLOYEE_ID = EMPLOYEE.ID
	GROUP BY EMPLOYEE.ID, EMPLOYEE.NAME) "employee"
    

The query produces this result:


[
  {
    "id": 1,
    "name": "Ursula Friedman",
    "phones": [
      {
        "number": "031 333 11 12",
        "type": "WORK"
      }
    ]
  },
  {
    "id": 2,
    "name": "Hans Boss",
    "phones": [
      {
        "number": "031 333 11 01",
        "type": "HOME"
      },
      {
        "number": "032 311 43 12",
        "type": "WORK"
      }
    ]
  }
]
    

Conclusion

Knowing the features of your database can save you time.
Start reading the manual of your database today. Have fun!