Скачать или смотреть How to Query a JSON Map in MySQL

Discover how to effectively `query JSON fields` in MySQL, extracting values with ease. Learn to use functions like `JSON_EXTRACT()` in this comprehensive guide!
---
This video is based on the question https://stackoverflow.com/q/69824594/ asked by the user 'membersound' ( https://stackoverflow.com/u/1194415/ ) and on the answer https://stackoverflow.com/a/69824800/ provided by the user 'Bill Karwin' ( https://stackoverflow.com/u/20860/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

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: How to query a JSON map in mysql?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Query a JSON Map in MySQL: A Step-by-Step Guide

In today’s data-driven world, many applications utilize JSON (JavaScript Object Notation) due to its lightweight data interchange format. MySQL supports this format, allowing developers to store and query JSON data directly within their databases. A common requirement is to query a specific value from a JSON document; in this post, we’ll address a typical scenario where you want to extract a certain field from a JSON map stored in a MySQL database.

The Problem: Querying JSON Data

Imagine you have a JSON structure that looks like this:

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

In this example, your goal is to search for "key1" and extract the firstname field of the first entry, which is "jane". The challenge lies in querying this effectively using MySQL’s JSON functions.

The Solution: Using JSON Functions in MySQL

MySQL provides a robust set of built-in functions that make working with JSON straightforward. The primary functions we will use for this operation are JSON_EXTRACT() and JSON_UNQUOTE().

Step 1: Set Up Your JSON Data

Before we can execute our query, let’s assume you have your JSON data stored in a variable. You can set it up in your MySQL session as follows:

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

Step 2: Querying the Data

To retrieve the firstname from the first object in the "key1" array, you can use the JSON_EXTRACT() function along with JSON_UNQUOTE(). Here’s the complete SQL query:

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

Breakdown of the Query

JSON_EXTRACT(-j, '$.key1[0].firstname'): This function call extracts the value associated with the specified path in the JSON document. In this case, it’s looking for firstname within the first object in the "key1" array.

JSON_UNQUOTE(): Since the value extracted by JSON_EXTRACT() is a JSON string, it may include quotation marks around it. JSON_UNQUOTE() removes those quotation marks, resulting in a plain string output.

Step 3: Viewing the Result

After executing the query, your result will look like this:

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

Congratulations! You've successfully queried a JSON map in MySQL and extracted the desired field.

Conclusion

Querying JSON data within a MySQL database doesn’t have to be complicated. By utilizing the JSON_EXTRACT() and JSON_UNQUOTE() functions, you can effectively retrieve specific values from your JSON structures with minimal effort. This approach allows for easier data manipulation and retrieval when working with JSON formatted data types.

This guide illustrated a basic use case, but the possibilities are extensive. Keep exploring MySQL's features to make the most of your JSON data!

If you have any questions or additional use cases for querying JSON data in MySQL, feel free to share them in the comments below!