How to use json in mysql

The data type of mysql field supports json format and can directly store json arrays and json objects.

1. There are two ways to insert json data

1. Insert in the form of ordinary strings and need to follow the json format

2. Use built-in functions (JSON_OBJECT and JSON_ARRAY )Create JSON data and then insert it

2. Query the data of the field in JSON

1. Column -> 'path' and JSON_EXTRACT(column, 'path') form to access the specific details of the specified field data. (*Note that the path must be enclosed in single quotes)

column represents the column name of the data field to be queried;
path is the access path to JSON data, and the path format is $.path or $[ idx].

$.path is used for JSON object type data;
$[idx] is used for JSON array type data;
$ represents the root node of the entire JSON data;
path is the access field key , if the field name contains spaces, it needs to be enclosed in double quotes, such as $."nick name"; (*Note that Chinese field names also need to be enclosed in double quotes)
[idx] is the index of the array.

2, -> and JSON_EXTRACT The field string type queried will also have double quotes, and a layer of processing needs to be done. You can use ->> and JSON_UNQUOTE are removed, and the escape character is also removed.

JSON_UNQUOTE(JSON_EXTRACT(column, path)) is equivalent to column->>path

3. There are two methods of multi-level query:

① column -> '$.key.childKey' Path dot connection method to access subset fields

② JSON_EXTRACT(JSON_EXTRACT(column, path), path) JSON_EXTRACT nesting method

③column ->'$[*].key' You can query all keys of the json array and return the array

3. Conditional search of JSON fields

1. Accurately query json type fields

where column-> '$.key' = value

2. Fuzzy query JsonArray type field

where column->'$[*].key' like '%value %'

3. Accurately query JsonArray type fields

where JSON_CONTAINS(column,JSON_OBJECT('key', "value"))

4. Multi-level relationships, fuzzy Query all

where column->'$**.key' like '%value%'

IV. JSON field update operation 1. Update field

JSON_SET(column, path, val[, path, val] ...) Update or insert
JSON_REPLACE(column, path, val[, path, val] ...) Only update

2, Add new fields

JSON_INSERT(column, path, val[, path, val] ...) Insert new fields and will not change existing ones

3. Delete fields

JSON_REMOVE(column, path[, path] ...) Delete field

The above is the detailed content of How to use json in mysql. For more information, please follow other related articles on the PHP Chinese website!