MySQL has been supporting JSON manipulation since 5.7. This article is based on the mysql5.7.35-log version.

Command for querying the MySQL version

select version();
Copy the code

JSON sample

Here is an example of a jSON string:

JSON object

{
    "id":1."name":"Zhang"."age":18
}
Copy the code

JSON array

[{"id":1."name":"Zhang"."age":18
    },
    {
        "id":2."name":"Bill"."age":20
    },
    {
        "id":3."name":"Fifty"."age":23}]Copy the code

Extract the KEY of the JSON

The JSON_KEYS function is used to extract JSON keys as follows:

SELECT JSON_KEYS('{" id ": 1," name ":" zhang ", "age" : 18}') AS `keys`;
Copy the code

Results:

Extract the value of JSON

The JSON_EXTRACT function is used to extract JSON values as follows:

Extract a JSON object with a single key value via $. Key

SELECT JSON_EXTRACT('{" id ": 1," name ":" zhang ", "age" : 18}'.'$.name') AS `name`;
Copy the code

Results:

As you can see that the results are quoted, you can use the JSON_UNQUOTE function to unquote individual results.

SELECT JSON_UNQUOTE(JSON_EXTRACT('{" id ": 1," name ":" zhang ", "age" : 18}'.'$.name')) AS `name`;
Copy the code

Results:

Extract JSON object, all key values through $.* all values

SELECT JSON_EXTRACT('{" id ": 1," name ":" zhang ", "age" : 18}'.'$. *') AS `values`;
Copy the code

Results:

Extract the value of the JSON array

Extracting the value of a JSON array is essentially the same as extracting the value of a single JSON object. There’s just a difference in values. Value: A digit indicates the index. The value starts from 0 and * indicates all indexes. The value can be:

$[0] JSON array first object $[*] JSON array all objects $[0]. Key The value of the first object in the JSON array $[*]. Key All objects in the JOSN array, the value of the keyCopy the code

Take the example of fetching all the values of name from a JSON array:

SELECT JSON_EXTRACT('[{" id ": 1," name ":" zhang ", "age" : 18}, {" id ": 2," name ":" bill ", "age" : 20}, {" id ": 3," name ":" detective ", "age" : 23}]'.'$[*].name') AS `names`;
Copy the code

Results:

(after)

The paper come zhongjue shallow, and must know this to practice.