Home  >  Article  >  Database  >  How to use json_extract function in MySQL

How to use json_extract function in MySQL

WBOY
WBOYforward
2023-06-02 20:16:091907browse

    1. json_extract usage scenario description

    In daily business development, usually a field in the mysql database will need to store a json format string when querying Sometimes the json data is large, and it is inefficient and troublesome to retrieve it all and then parse and query it every time.

    Fortunately, Mysql5.7 and later versions provide the json_extract function, which can query the value through key ( If it is a json array type, you can get the value of the corresponding position through the subscript), which is very convenient.

    2. MySQL json_extract function introduction

    2.1 Function introduction

    New functions after Mysql5.7 version, Mysql provides a native Json type, the Json value will not be It is stored as a string, but in an internal binary format that allows fast reading of document elements. Json text will be automatically verified when inserting or updating a Json column. Text that fails verification will generate an error message. Json text is created in a standard way, and most comparison operators can be used for comparison operations, such as: =, 91eef5681cad6f0cc96f9adeb2931b45, >=, a8093152e673feb7aba1828c43532094, != and <=> ;.

    2.2 Usage

    The data stored is json string (type is vachar).

    If you want to query the value of a certain field in json, the method used is: JSON_EXTRACT().

    Syntax:

    **JSON_EXTRACT(json_doc, path[, path] …)**

    Usage tips:

    • If the json string is not an array, use it directly $.Field name

    • If the json string is an array [Array], then Directly use $[corresponding index ID]

    2.3 Notes

    JSON_EXTRACT performance verification, verify that everything is complete by viewing the execution plan Table scan.
    Usage scenarios: If the amount of data is small, it can be used if the json string is large. If the amount of data is large, it is not recommended.

    3. Data verification

    3.1 Extract values ​​from ordinary json

    Instructions:

    • Use ordinary fields $ .KEY Get the

    • array field using $.KEY[index] to get, note that index starts from 0

    • In actual use, the following json string only needs to be replaced with the corresponding table field, but please note that ETL conversion operations such as empty judgment and replacement of json are required.

    select 
    	json_extract(&#39;{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}&#39;,"$.name") as name,
    	json_extract(&#39;{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}&#39;,"$.tel_no") as tel_no,
    	json_extract(&#39;{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}&#39;,"$.hobbies[0]") as hobby_1,
    	json_extract(&#39;{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}&#39;,"$.hobbies[1]") as hobby_2,
    	json_extract(&#39;{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}&#39;,"$.hobbies[2]") as hobby_3,
    	json_extract(&#39;{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}&#39;,"$.hobbies[3]") as hobby_4;

    Result :

    ##nametel_nohobby_1hobby_2hobby_3hobby_4##"zhangsan"3.2 Extract the value of the json array
    136-6666-6666 "basketball" " run" "sing" NULL

    site_user table

    id1234Extract the user’s first tag:
    name #tags
    zhangsan [“COMMON”]
    lisi [“VIP”]
    wangwu [“VVIP”,“PLATINUM”]
    zhaoliu
    select 
    	id,
    	name,
    	tags, 
    	json_extract(if(LENGTH(tags)>0,tags, &#39;[]&#39;),"$[0]")  # 如果tags无数据,赋值为空数组
    from site_user;

    Result:

    ##idnametags##1zhangsanlisiwangwuzhaoliu
    "COMMON" 2
    "VIP" 3
    "VVIP" 4
    NULL

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

    Statement:
    This article is reproduced at:yisu.com. If there is any infringement, please contact admin@php.cn delete