How to Fetch Multiple Values from JSON Columns in MySQL

A step-by-step guide on displaying key-value pairs from JSON data as columns in MySQL, ideal for sales data representation.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Fetch multiple values stored as key-value pairs in the json date type and display as columns

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Fetch Multiple Values from JSON Columns in MySQL

In today's data-driven world, efficient handling of data formats is crucial, especially when working with databases. One common format is the JSON data type, which allows us to store data as key-value pairs. In this post, we’ll tackle a specific problem: how to fetch multiple values stored as key-value pairs in a JSON column and format them into a table for easier interpretation on your dashboard.

Understanding the Problem

Imagine you have sales data that includes different charges formatted as JSON. Here’s an example of how the data might look:

Sale IDAmountExtra Charges1231000[{"key": "Handling Charges", "amount": 20}, ...]3451500[{"key": "Transportation Charges", "amount": 10}, ...]567240[{"key": "Handling Charges", "amount": 10}, ...]You want to transform this data into a more conventional format like this:

Sale IDAmountHandling ChargesTransportation ChargesPacking Charges12310002001534515000100567240102015This transformation will help you display useful information on a dashboard effectively.

The Solution

To achieve this transformation, we can utilize the JSON_EXTRACT function in MySQL. Below, I will break down the approach step by step.

Step 1: Using JSON Functions

The key to extracting data from the JSON column is using the JSON functions properly. Here is how you can perform this extraction:

Extract Each Key's Amount: We will recursively use JSON_EXTRACT to navigate through the JSON structure.

COALESCE Function: This ensures that if the key doesn’t exist, instead of returning NULL, we will get a default value of 0.

Step 2: The SQL Query

Here’s the final SQL query that accomplishes the above:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Query

COALESCE: The function is used to return the first non-null value among its arguments.

JSON_EXTRACT: This extracts values from the JSON field using a specified path.

JSON_SEARCH: This finds the path to the key we are interested in, in this case, "Handling Charges", "Transportation Charges", and "Packing Charges".

Conclusion

By utilizing these JSON functions effectively, you can easily reshape your JSON data into a more readable and functional tabular format. This approach is both efficient and compatible with MySQL 5.7, ensuring that you can access these features without needing to upgrade your database.

Now you can present your sales data with extra charges directly on your dashboard, leading to better data insights and decision-making.

If you have additional questions or need further clarification regarding working with JSON in MySQL, feel free to reach out!