Analysis of JSON string stored in Mysql

Preface

JSON can convert a set of data represented in a JavaScript object into a string, and then this character can be easily passed between functions string, or pass a string from a Web client to a server-side program in an asynchronous application. This string can represent arrays and complex objects, not just simple lists of keys and values. Storing Json strings in Mysql can greatly simplify the storage complexity, and at the same time, reading the database becomes solves the first problem that many people encounter.

Example: { "key": "value" }

1. What is Json?

A lightweight data exchange format is JSON (JavaScript Object Notation). JSON uses a completely language-independent text format. These characteristics make JSON an ideal data exchange language. Easy for humans to read and write, and easy for machines to parse and generate.

2. Different situations

1. Fuzzy query json type field

The stored data format (field name people_json):

{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}

The code is as follows (example ):

select * from table_name where people_json->'$.name' like '%zhang%'

2. Accurately query the data format stored in the json type field

(field name people_json):

{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}

The code is as follows (example):

select * from table_name where people_json-> '$.age' = 13

3. Fuzzy query JsonArray type field

Stored data format (field name people_json):

[{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]

The code is as follows (example):

select * from table_name where people_json->'$[*].name' like '%zhang%'

4. Precise query JsonArray Type field

Stored data format (field name people_json):

[{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]

The code is as follows (example):

select * from table_name where JSON_CONTAINS(people_json,JSON_OBJECT('age', "13"))

The above is the detailed content of Analysis of JSON string stored in Mysql. For more information, please follow other related articles on the PHP Chinese website!