Working with JSON in MySQL using JSON_TABLE

Posted by on | Featured

JSON is undeniably king of portable data formats and almost all APIs will return it in some form. But on the database side things are less clear cut. According to DB-Engines noSQL databases don’t rank in top 4. Therefore chances are we need to start working with JSON in MySQL using JSON_TABLE.

Working with JSON in MySQL using JSON_TABLE

How can you work with JSON data in SQL? The answer is: “quite well”. As a practical example, I’ll show how to work with JSON data in MySQL. Using my own experience I use this method cache data obtained through an GraphQL API. Later I process the data further inside the MySQL database.

MySQL 8.0 and MariaDB 10.6.0. introduce the json_table function. It provides a powerful way to work with JSON data. The key is that it allows you to apply the full power of rational databases function on your JSON data.

JSON Path

First we need json-path in order to extract data from a JSON document stored inside a column. The column has to have the JSON datatype which Oracle introduced with version 5.0 of MySQL.

JSON-path uses the dot operator to descend through a the JSON document structure. You can escape dots inside a key by putting the key in “double-quotes”.

Example

Lets start of with creating a new table and inserting some example data to use with json_table:

CREATE TABLE json_example (content JSON);

INSERT INTO json_example(content)
VALUES
('{
  "app": {
    "id": "gid://partners/App/26531111"
  }, 
  "netAmount": {
    "amount": "4.81"
  }
}');

INSERT INTO json_example(content)
VALUES
('{
  "app": {
    "id": "gid://partners/App/2653222"
  }, 
  "netAmount": {
    "amount": "14.81"
  }
}');

INSERT INTO json_example(content)
VALUES
('{
  "app": {
    "id": "gid://partners/App/2653222"
  }, 
  "netAmount": {
    "amount": "10.12"
  }
}');

This data shows different apps of a developer inside an app-store. The users of his apps are paying him money for using his apps. The developer would like to know which of his apps are making him what amount of money. Our query needs to to do three things:

  1. Extract the app-id and the net-amount information from the json data
  2. Convert the netAmount value to a number format
  3. sum up the amounts and group it by app-id

This will lead to the following query:

select app_id, sum(amount) from 
	json_example, json_table(content, '$' 
		COLUMNS(
			app_id varchar(128) PATH '$.app.id',
			amount decimal(6,2) PATH '$.netAmount.amount'
		)
	) jt
group by app_id
>>
app_id                          sum(amount)
gid://partners/App/26531111     4.81
gid://partners/App/2653222      24.93

At first, this query might look a bit confusing. You might expect that the table name json_example should be included inside the json_table function. The syntax however is slightly different: The table-name that the json_table function refers to is before the json_table function.

Final Thoughts

Finally, there are my more JSON functions in MySQL.

Comments are closed.