MySQL 5.7 supports JSON data type fields. JSON is currently the most popular form of data interaction, and MySQL is following suit, adding JSON data types in version 5.7. Although there are still few apps available, it may become a trend. Let’s take a look at some of MySQL’s built-in functions for JSON data types.

PS: the following contents have unreasonable places, such as the actual write SQL keyword should be capitalized, disable SELECT *, etc., just for the sake of the visual, sorry ~

Create a table with JSON fields

create table test_json ( 
    `id` int auto_increment,
    `obj_json` JSON,
    `arr_json` JSON,
    primary key (`id`)
)engine = InnoDB default charset = utf8mb4;
Copy the code

The length of the JSON field is not required, and the default value cannot be set

Insert JSON record

The JSON type of MySQL supports JSON arrays and OBJECTS

#JSON_ARRAY ["xin", 2019, null, true, false, "2019-5-14 21:30:00"] #JSON_OBJECT {"key1": "value", "key2": 2019, "time": "The 2015-07-29 12:18:29. 000000"}Copy the code

JSON_ARRAY and JSON_OBJECT values can be strings, numbers, null, time types, and Booleans

The JSON_OBJECT key must be a string

Insert method: Directly through the string form

insert into test_json (obj_json, arr_json) values ('{"key1": "value", "key2": 2019, "time": "The 2015-07-29 12:18:29. 000000"} ', '[" xin ", 2019, null, true, false, "the 2019-5-14 21:30:00"] ");Copy the code

The query results

select * from test_json
Copy the code

Insert via JSON_OBJECT(), JSON_ARRAY()

Insert into test_json (obj_json, arr_json) values (JSON_OBJECT('key1', 'insert by JSON_OBJECT', 'key2', 3.14159) JSON_ARRAY('Go', 'Ruby', 'Java', 'PHP'));Copy the code

The query results

select * from test_json
Copy the code

PS: The two types can be nested.

Query the fields containing the JSON type

You can query the record containing JSON using the SELECT statement, and the result is shown above.

What if you want to extract a specific value from a JSON field?

The OBJECT type

Col ->path, where path is $.key

select obj_json->'$."key1"' key1, obj_json->'$."key2"' key2 from test_json;
Copy the code

ARRAY type

Col ->path where path = $[index]

select arr_json->'$[0]' index1, arr_json->'$[1]' index2 , arr_json->'$[2]' index3 from test_json;
Copy the code

JSON type field update

Update You can of course override the entire field by simply updating it

Let’s take a quick look at updates to JSON

Built-in functions JSON_SET(), JSON_INSERT(), JSON_REPLACE(), JSON_REMOVE()

JSON_SET() inserts a value, overwriting it if it exists

update test_json
    set obj_json = JSON_SET(obj_json, '$."json_set_key"', 'json_set_value', '$.time', 'new time'),
        arr_json = JSON_SET(arr_json, '$[6]', 'seven element', '$[0]', 'replace first') 
    where id = 1;
Copy the code
select * from test_json where id = 1;
Copy the code

JSON_INSERT() inserts the value without overwriting the original value

update test_json
    set obj_json = JSON_INSERT(obj_json, '$."json_insert_key"', 'json_insert_value', '$."key1"', 'Set existing key'),
        arr_json = JSON_INSERT(arr_json, '$[4]', 'json_insert_value', '$[0]', 'Set existing index') 
    where id = 2;
Copy the code
select * from test_json where id = 2;
Copy the code

JSON_REPLACE() only overwrites existing values

update test_json set obj_json = JSON_REPLACE(obj_json, '$."key1"', 'json_replace_key1', '$."json_replace_insert"', 'test'), arr_json = JSON_REPLACE(arr_json, '$[3]', 'PHP is best language! ', '$[5]', 'json_replace_insert') where id = 2;Copy the code
select * from test_json where id = 2;
Copy the code

JSON_REMOVE () removed

update test.test_json
    set obj_json = JSON_REMOVE(obj_json, '$."key1"', '$."Nonexistent key"'),
        arr_json = JSON_REMOVE(arr_json, '$[0]', '$[5]')
    where id = 2;
Copy the code
select * from test_json where id = 2;
Copy the code

other

What if the stored JSON has quotes?

You need to escape when you insert

insert into test_json (obj_json, arr_json) values ('{"key1":  "test_obj_value1\\""}', '["\\"test_arr_value1\\""]');
insert into test_json (obj_json, arr_json) values (JSON_OBJECT('key1', '\"test_obj_value1 \'single\''), 
                                                   JSON_ARRAY('\"test_arr_value1\" \'single\''));
Copy the code

The query

select obj_json->'$."key1"' obj_key1, arr_json->'$[0]' arr_index1 
    from test_json where id in (3, 4);
Copy the code

If the query result does not want to preserve the escape, the form col->>path can be used

select obj_json->>'$."key1"' obj_key1, arr_json->>'$[0]' arr_index1 
    from test_json where id in (3, 4);
Copy the code

Merge functions JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH()

Since 8.0, merge functions have been provided to merge multiple JSON files

The difference is that JSON_MERGE_PATCH() overwrites the original value, while JSON_MERGE_PRESERVE() does not

Let’s try this out (without inserting a table)

select
    JSON_MERGE_PATCH(
        JSON_OBJECT('obj_key1', 'obj_value1', 'obj_key2', 'obj_value2'),
        JSON_OBJECT('obj_key2', 'new_obj_value2')
    ) as col1,
    JSON_MERGE_PATCH(
        JSON_ARRAY('arr_index1', 'arr_index2', 'arr_index3'),
        JSON_ARRAY('arr_index4', 'arr_index5', 'arr_index6')
    ) as col12;
Copy the code

select
    JSON_MERGE_PRESERVE(
        JSON_OBJECT('obj_key1', 'obj_value1', 'obj_key2', 'obj_value2'),
        JSON_OBJECT('obj_key2', 'new_obj_value2')
    ) as col2,
    JSON_MERGE_PRESERVE(
        JSON_ARRAY('arr_index1', 'arr_index2', 'arr_index3'),
        JSON_ARRAY('arr_index4', 'arr_index5', 'arr_index6')
    ) as col3;
Copy the code

As mentioned earlier, JSON data types support nesting, which is briefly demonstrated

select
    JSON_MERGE_PRESERVE(
        JSON_ARRAY('arr_index1', 'arr_index2'),
        JSON_OBJECT('obj_key1', 'obj_value1')
    ) as col1,
    JSON_MERGE_PRESERVE(
        JSON_OBJECT('obj_key1', 'obj_value1'),
        JSON_ARRAY('arr_index1', 'arr_index2')
    ) as col2;
Copy the code

Write in the last

When using JSON-type fields, you should carefully consider the database design to see if the JSON data type is appropriate. Development is often combined with the use of other languages. As a TRDB, MySQL sometimes has complicated manipulation of JSON data types. For example, ORM mapping of strongly typed languages, whether to use JSON data types needs to be considered according to the actual situation.

Resources: dev.mysql.com/doc/refman/…