Free SQL Help for Devs, DBAs, and the Curious

With the increasing prevalence of semi-structured data, database management systems have had to adapt and evolve. One of the most significant developments in this regard has been the introduction of JSON functions in SQL databases such as MySQL 8.0, SQL Server 2019, DB2 11.5, Azure SQL, and Azure Synapse. These functions allow developers to interact with JSON data directly within their SQL queries, opening up new possibilities for data manipulation and analysis. This blog post will delve into the practical use of MySQL’s JSON functions.

JSON, or JavaScript Object Notation, is a lightweight data-interchange format that is easy for humans to read and write and easy for machines to parse and generate. JSON is often used when data is sent from a server to a web page. In MySQL 8.0, you can store JSON data in a SQL database and use JSON functions to manipulate it.

For instance, consider the JSON_EXTRACT function. This function retrieves a value from a JSON document using a specified JSON path. The following code example illustrates how you can use JSON_EXTRACT to retrieve a value from a JSON document stored in a MySQL table:

-- MySQL code example
SELECT JSON_EXTRACT(json_column, '$.key') 
FROM my_table;

In the case of SQL Server 2019, Microsoft introduced native JSON support, enabling you to parse JSON formatted data, transform JSON data to relational format, and to format relational data as JSON. To parse JSON data, we use the OPENJSON function. It takes a single JSON object or a collection of JSON objects and transforms them into one or more rows. Here is how to use the OPENJSON function:

-- T-SQL code example
SELECT value
FROM OPENJSON('{"key": "value"}');

IBM's DB2 11.5 also has robust support for JSON. The JSON_VAL function in DB2 extracts a value from a JSON document with a specified JSON path. It functions similarly to the JSON_EXTRACT function in MySQL:

-- DB2 code example
SELECT JSON_VAL(json_column, 'key', 's:32') 
FROM my_table;

Azure SQL and Azure Synapse also have comprehensive JSON support. For example, Azure SQL includes the JSON_VALUE function, which extracts a scalar value from a JSON string. For Azure Synapse, JSON functions are available in both serverless SQL pool and dedicated SQL pool:

-- Azure SQL code example
SELECT JSON_VALUE(json_column, '$.key') 
FROM my_table;

In conclusion, the introduction of JSON functions in SQL databases has made it much easier to work with semi-structured data. With these functions, developers can now manipulate and analyze JSON data directly within their SQL queries, resulting in more efficient and streamlined workflows.