Converting JSON Values to a Relational Table

MySQL provides many functions that make working with JSON easy. The database management system offers seamless import and storage for JSON documents through the importJson command line utility and the JSON data type. Due to its proficiency in handling relational data, MySQL recognizes the need to convert JSON documents into a relational data format before storing their contents.

The JSON_TABLE function

MySQL offers a function called JSON_TABLE(expression, path) to facilitate this conversion process. The first argument, expression, represents the JSON data that will be processed, while the second argument, path, specifies the JSON path to be applied during the conversion. This powerful function allows developers to extract specific data elements from JSON documents and organize them in a tabular format.

In its simplest form, the expression parameter can be a JSON string literal. However, it is also possible to reference a column in an existing table that already stores JSON documents, enabling seamless migration of existing data structures.

By leveraging the capabilities of JSON_TABLE(), we can easily transform JSON documents into a relational format compatible with MySQL’s efficient data handling. This conversion process enables efficient querying, analysis, and manipulation of JSON data using well-established functions and operators for dealing with relational data.

JSON object

In previous examples, we consider the products provided as JSON documents. For a demonstration of JSON_TABLE(), we want to fill a table, Product, with dummy products from the mentioned JSON API: