One of the latest additions to the SQL language is the JSON features. This new group of features makes it easier to benefit from the advantages of storing certain types of unstructured and schema-less data like the JSON format in a very structured and relational way using SQL native functions. This allows for many things, such as applying integrity contraints on certain JSON fields contained in a JSON document, indexing certain JSON fields, easily converting and returning unstructured data into relational data and vice versa, and inserting or updating unstructured data by the means of the proven reliability of SQL transactions.
To fully appreciate this new group of features, let's insert some data into the test database by executing a query that will convert the JSON data into relational data.
First, please execute the following command on the container's CLI:
# mysql -uroot test < /srv/www/json_example.sql
Once the new table is loaded into the database, you can execute the following query:
SELECT id,
JSON_VALUE(json, "$.name") AS name,
JSON_VALUE(json, "$.roles[0]") AS main_role,
JSON_VALUE(json, "$.active") AS active
FROM json_example
WHERE id = 1;
Once executed, you should see this result:
As we can see, converting JSON unstructured data into relational and structured data was very easy using the new JSON functions. Inserting unstructured data into a structured database is just as easy. Moreover, the added constraint would verify that the JSON string being inserted is valid. To verify this feature, let's attempt to insert invalid JSON data into our test table:
INSERT INTO `json_example` (`id`, `json`) VALUES (NULL, 'test');
Upon trying to execute the query, we would get the following error message:
Thus, Modern SQL makes working with JSON-formatted data easy in an SQL environment. This will greatly optimize performance at the application level, as it will now be possible to eliminate the overhead that comes with having to json_encode() and json_decode() every time an application needs to retrieve or store JSON-formatted data into a relational database.
There are many more Modern SQL features that we could try to better understand, but not all RDBMSs have implemented them and many of these features would require that we analyze implementation details. We will simply look at two more features that have not been implemented within the MariaDB server, but that have been within the PostgreSQL server. In order to start and use the PostgreSQL server that is included in the Linux for PHP container, please enter the following commands on the container's CLI:
# /etc/init.d/postgresql start # cd /srv # wget --no-check-certificate -O phpPgAdmin-5.1.tar.gz https://superb-sea2.dl.sourceforge.net/project/phppgadmin/phpPgAdmin%20%5Bstable%5D/phpPgAdmin-5.1/phpPgAdmin-5.1.tar.gz # tar -xvf phpPgAdmin-5.1.tar.gz # sed -i "s/extra_login_security'] = true/extra_login_security'] = false/" phpPgAdmin-5.1/conf/config.inc.php # cd fasterweb/chapter_6 # ln -s ../../phpPgAdmin-5.1 ./phppgadmin
# cd /srv/www
After entering these commands, you should be able to access the PostgreSQL server via the phpPgAdmin web interface at http://localhost:8181/phppgadmin. Point your browser to this address and click on the Servers icon in the upper right-hand corner of the screen in order to see the following interface:
From here, click on the PostgreSQL link in the center of the page, type postgres as the Username and leave the Password empty on the login page:
Then, click on the Login button and you should be able to access the server:
Finally, we will create a database that will allow us to learn how to use the two last Modern SQL features that we will cover in this book. In the phpPgAdmin interface, click on the Create database link and fill out the form in order to create the test database:
By clicking on the Create button, you will create the test database alongside the postgres database:
Once this is done, enter the following commands on the container's CLI:
# su postgres # psql test < sales.sql # exit
We are now ready to try out the FILTER clause.