Detailed explanation of how to extract data from JSON string using MySQL

1. Background knowledge

JSON (JavaScript Object Notation) is a lightweight data exchange format that is easy to read and write, and is also easy to machine parse and generate. MySQL supports JSON data types since version 5.7, making it possible to store and manipulate JSON data in a database.

In many applications, JSON strings may be stored in a field in a table, and we need to extract and convert this data for further analysis or presentation.

2. Sample data

Assume we arewf_lcdyThere is a field in the tablelct, which stores the following JSON string:

{"15775d64e52c4ba3a8eef4bafc5f40e5":"875 162","75b67fab657748a9ab4bba141bfa0d36":"375 98","428299fd90814b3eaf129e8246f82b2a":"155 126"}

We want to convert it to an array of the following format:

[{"id":"15775d64e52c4ba3a8eef4bafc5f40e5","x":875,"y":162},{"id":"75b67fab657748a9ab4bba141bfa0d36","x":375,"y":98},{"id":"428299fd90814b3eaf129e8246f82b2a","x":155,"y":126}]

3. SQL query analysis

Here is the SQL query that implements this transformation:

SELECT
    CONCAT('[', GROUP_CONCAT(
            CONCAT(
                    '{"id":"',
                    SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', 1), '"', -1),
                    '", "x":',
                    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', 1) AS UNSIGNED),
                    ', "y":',
                    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', -1) AS UNSIGNED),
                    '}'
            )
                ), ']') AS result
FROM (
         SELECT
             TRIM(BOTH '"' FROM kv) AS kv
         FROM (
                  SELECT
                      SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(lct, '{', ''), '}', ''), '"', ''), ',', ), ',', -1) AS kv
                  FROM wf_lcdy
                           JOIN (
                      SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
                      SELECT 9 UNION ALL SELECT 10
                  ) numbers
                  WHERE CHAR_LENGTH(lct) - CHAR_LENGTH(REPLACE(lct, ',', '')) >=  - 1 AND ID = '0c86346993d64d98ad17892974bf8963'
              ) AS temp
     ) AS kv_pairs;

3.1 Query structure analysis

1. Inner query：

   • Remove excess characters:First, useREPLACEThe function willlctin the field{、}and"Remove. This can simplify subsequent processing.
   • Split string:useSUBSTRING_INDEXSplit each key-value pair. We implement it through a numeric table (1 to 10). The purpose of the numeric table is to help us iterate over each key-value pair, because we cannot know the number of key-value pairs in JSON in advance.

SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(lct, '{', ''), '}', ''), '"', ''), ',', ), ',', -1) AS kv

1. This code splits the JSON string into multiple key-value pairs.kvThe column will contain such values, for example:

   • 15775d64e52c4ba3a8eef4bafc5f40e5:875 162
   • 75b67fab657748a9ab4bba141bfa0d36:375 98
   • 428299fd90814b3eaf129e8246f82b2a:155 126

2. Middle-level query：

   • In this query, we willkvColumns are processed further. useTRIM(BOTH '"' FROM kv)Remove the extra quotes to ensure that subsequent operations are not affected.

SELECT
    TRIM(BOTH '"' FROM kv) AS kv

• External query：
  • Aggregation and formatting: In outer query, we useGROUP_CONCATAggregate allkvRight, and useCONCATGenerates a JSON string in the target format.
  • Extract data:useSUBSTRING_INDEXExtractionid、xandyvalues ​​and convert them to the corresponding format. The key here is to split the string and extract the numbers.

GROUP_CONCAT(
    CONCAT(
        '{"id":"',
        SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', 1), '"', -1),
        '", "x":',
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', 1) AS UNSIGNED),
        ', "y":',
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', -1) AS UNSIGNED),
        '}'
    )
)

1. • Final result: The final result will be a string in the format JSON array.

4. Query results

After running the above query, you will get the desired result format:

[{"id":"15775d64e52c4ba3a8eef4bafc5f40e5","x":875,"y":162},{"id":"75b67fab657748a9ab4bba141bfa0d36","x":375,"y":98},{"id":"428299fd90814b3eaf129e8246f82b2a","x":155,"y":126}]

5. Performance considerations

• Character length calculation：CHAR_LENGTH(lct) - CHAR_LENGTH(REPLACE(lct, ',', ''))The calculation is used to ensure that we only process key-value pairs that exist. This method has a certain impact on performance, especially for large texts.
• Use of digital tables: Because the structure of JSON may change, the use of numeric tables can be extended to support more key-value pairs. In practical applications, you can increase the range of numbers as needed.

6. Summary

Through the above SQL query, we successfully extracted the data from a field containing a JSON string and converted it to another structured format. This approach demonstrates MySQL's flexibility and powerful capabilities in handling JSON data.

In practical applications, you can make appropriate modifications to the query according to specific needs to adapt to JSON data of different structures. In addition, understanding the use of string processing and aggregation functions in SQL is crucial to improving the ability and efficiency of data processing. Hope this article is helpful to you when processing JSON data!

The above is a detailed explanation of the method of extracting data from JSON strings using MySQL. For more information about extracting data from MySQL JSON, please pay attention to my other related articles!