How do PHP and MySQL parse JSON data?

How do PHP and MySQL parse JSON data?

In web development, PHP and MySQL are two commonly used technologies. At the same time, JSON (JavaScript Object Notation) has also become a widely used data exchange format in modern network applications. This article will introduce how to parse JSON data in PHP and MySQL, and provide code examples for reference.

First, let us understand the basic structure and syntax rules of JSON. JSON is a lightweight data exchange format consisting of key-value pairs. Keys must be strings and values ​​can be strings, numbers, booleans, arrays, objects, or null. JSON data uses curly brackets ({}) to represent objects and square brackets ([]) to represent arrays.

To parse JSON data in PHP, you can use the json_decode() function. This function converts a JSON string to a PHP object or array. The syntax of this function is as follows:

mixed json_decode ( string $json [, bool $assoc = false [, int $depth = 512 [, int $options = 0 ]]] )

Parameter description:

• $json: The JSON string to be parsed.
• $assoc: Optional parameter, if set to true, JSON will be converted into an associative array; if set to false, then Convert JSON to object. Default is false.
• $depth: Optional parameter, set the maximum depth of recursive analysis. The default is 512.
• $options: Optional parameters, used to set options during the parsing process. Default is 0.

The following is an example of parsing JSON data:

$json = '{"name":"John", "age":30, "city":"New York"}';
$data = json_decode($json);

echo $data->name;  // 输出 "John"
echo $data->age;   // 输出 30
echo $data->city;  // 输出 "New York"

To parse JSON data in MySQL, you can use JSON_EXTRACT() and JSON_UNQUOTE() function. JSON_EXTRACT() is used to extract the value of a specific key in the JSON data, and JSON_UNQUOTE() is used to remove quotes from the extracted value.

The following is an example of parsing JSON data in MySQL:

CREATE TABLE users (
  id INT PRIMARY KEY,
  info JSON
);

INSERT INTO users VALUES (1, '{"name":"John", "age":30, "city":"New York"}');

SELECT id, JSON_UNQUOTE(JSON_EXTRACT(info, '$.name')) AS name,
  JSON_EXTRACT(info, '$.age') AS age,
  JSON_UNQUOTE(JSON_EXTRACT(info, '$.city')) AS city
FROM users;

Running the above code will output the following results:

+----+------+-----+----------+
| id | name | age | city     |
+----+------+-----+----------+
| 1  | John | 30  | New York |
+----+------+-----+----------+

By running the above code, we get the data from the JSON field The corresponding values ​​were extracted and returned as columns.

To sum up, this article introduces how to parse JSON data in PHP and MySQL. In PHP, we use the json_decode() function to convert a JSON string to a PHP object or array; in MySQL, we use JSON_EXTRACT() and JSON_UNQUOTE()Function to extract the value of the key in JSON data. These technologies can help developers process and operate JSON data more conveniently and improve development efficiency.

I hope this article will help you understand and apply PHP and MySQL to parse JSON data!

The above is the detailed content of How do PHP and MySQL parse JSON data?. For more information, please follow other related articles on the PHP Chinese website!