Using SQL to generate JSON output

I recently read [1], and it had a very interesting notion.

The idea is to let the database generate JSON, and provide it straight into your client.

So I decided to find out if MariaDB had some support for this as well.

It does².

So, it basically was nothing more then calling a NativeQuery on the EntityManager, and returning a concatted resultset with a '['prefix and a ']'postfix and a comma-delimiter and away we go.

The native query looked like the following:

It worked flawlessly!

Caveat: of course, this is only in the case where your middleware (as in this example) really doesn't need to do anything with the result.

I mean, you are going to have to do all checks in the database query.

I think this example shows its strength when you just really want to read a lot of data, and do not need to process it.

References

[1] Stop Mapping Stuff in Your Middleware. Use SQL’s XML or JSON Operators Instead

https://blog.jooq.org/2019/11/13/stop-mapping-stuff-in-your-middleware-use-sqls-xml-or-json-operators-instead/

[2] MariaDB - starting with 10.2.3 - JSON Functions

https://mariadb.com/kb/en/library/json-functions/

MariaDB - JSON with MariaDB Platform: What Is JSON and Why Use It – With Examples

https://mariadb.com/resources/blog/json-with-mariadb-10-2/

MariaDB - Relational and Semi-structured Data

https://mariadb.com/database-topics/semi-structured-data/

MariaDB - Function Differences Between MariaDB 10.4 and MySQL 8.0

https://mariadb.com/kb/en/library/function-differences-between-mariadb-104-and-mysql-80/#present-in-mysql-only