• notice
  • Congratulations on the launch of the Sought Tech site

MySQL 8.0 can manipulate JSON

Brief overview

  • Null is not allowed; Json format definition is similar to LONGBLOB or LONGTEXT; its maximum length is controlled by max_allowed_packet;

  • When viewing the function of the size of the space occupied by the JSON field JSON_STORAGE_SIZE (xxx);

  • In addition to ordinary Json operations, some related operations are additionally supported by GeoJSON (geometric-based data exchange format for geospatial data);

  • Support index for Json field (combined with new features of Mysql8.0, function index);

  • An optional optimization item that can support part of the Json Column update in place is added to MySql8.0; the functions that can be used are JSON_SET() , JSON_REPLACE() , JSON_REMOVE() ; There are some constraints when used, but there will be more performance;


JSON basic tools ;

//Use the JSON_ARRAY method to define a JSON array; SELECT JSON_ARRAY ( 1 , "abc" , NULL, TRUE, CURTIME( )) //Result: [1, "abc", null, true, "11:30:24.000000"]  //JSON_OBJECT method defines JSON object SELECT JSON_OBJECT ( 'id' , 87 , 'name' , 'carrot' ) //Result {"id": 87, "name": "carrot"}//Scenario where arrays and objects are nested; [99, { "id" : "HK500" , "cost" : 75.99 }, [ "hot" , "cold" ]] { "k1" : "value" , "k2 " : [ 10 , 20 ]}//Date/time type definition [ "12:18:29.000000" , "2015-07-29" , "2015-07-29 12:18:29.000000" ]
//JSON_QUOTE escapes the JSON object into String, which means to escape the internal symbols and wrap them in double quotes; JSON_QUOTE( '"null"' ) //Result "\"null\""//Beautify and output the JSON content;JSON_PRETTY()//The elements inside JSON/JSON can be converted to other data types; //The id element in JSON jdoc is converted to unsigned int as follows; [ https://dev.mysql.com/doc/refman/8.0/en /json.html#json-converting-between-types ] (https: //dev.mysql.com/doc/refman/8.0/en/json.html#json-converting-between-types) ORDER BY CAST ( JSON_EXTRACT( jdoc, '$.id ' ) AS UNSIGNED) ;

The operation of merging JSON JSON_MERGE_PRESERVE()and JSON_MERGE_PATCH()the possibility of actual business use are rare;

-> --> operator, find the value according to the key; the difference is that --> will remove the wrapped "and escape symbols; its equivalent Function form is JSON_EXTRACT()

The latest MySQL interview questions are organized and recommended to see this article: http://www.javastack.cn/mst/

// {"mascot": "Our mascot is a dolphin named \"Sakila\"."} mysql> SELECT col -> "$.mascot" FROM qtest; //Result: | "Our mascot is a dolphin named \"."" the Sakila \ | the SELECT sentence -> > "$.mascot" the FROM of FACTS; // result: | Our mascot is a dolphin named "Sakila" |.


Above JSON Path expression --> The content in the double quotation marks is the so-called JSON Path expression;
this grammar is part of the ECMAScript specification, so front-end programmers should be particularly familiar with it;

Take the following JSON as an example;

[ 3 , { "a" : [ 5 , 6 ], "b" : 10 }, [ 99 , 100 ]]
$[ 0 ] = 3 ;
$[ 1 ] = { "a" : [ 5 , 6 ], "b" : 10 };
$[ 2 ] = [ 99 , 100 ];

At the same time, $[1], $[2] are not scalars, further

$ [1].a = [5,6] $ [1].a [1] = 6$ [1].b = 10;$ [2] [0] = 99;

Further supported syntax features $[n to m]

$[ 1 to 2 ] = [{ "a" : [ 5 , 6 ], "b" : 10 }, [ 99 , 100 ]]
$[last -2 to last -1 ] = [ 3 , { "a" : [ 5 , 6 ], "b" : 10 }]

in conclusion;

a.* represents all members in object;
b [*] represents all cells in array;
c [prefix] ** suffix represents all paths starting with prefix and ending with suffix;

Find and modify JSON

//As above, it should be possible to use --> syntax instead ; mysql> SELECT JSON_EXTRACT ( '{"a": 1, "b": 2, "c": [3, 4, 5]}' , '$.* ' ) ; //[1, 2, [3, 4, 5]]   SELECT JSON_EXTRACT ( '{"a": 1, "b": 2, "c": [3, 4, 5]}' , ' $.c[*]' ) //[3, 4, 5] SELECT JSON_EXTRACT ( '{"a": {"b": 1}, "c": {"b": 2}}' , '$ **.b' ) ; //[1, 2] SELECT JSON_EXTRACT ( '[1, 2, 3, 4, 5]' , '$[1 to 3]' ) ; //[2, 3, 4]//JSON_SET JSON_INSERT JSON_REPLACE JSON_REMOVE SET @j = '["a", {"b": [true, false]}, [10, 20]]' ;SELECT JSON_SET ( @j , '$[1].b[0]' , 1 , '$[2][2]' , 2 ) ; //| ["a", {"b": [1, false ]}, [10, 20, 2]]    SELECT JSON_INSERT ( @j , '$[1].b[0]' , 1 , '$[2][2]' , 2 ) ; //["a", {"b": [true, false] }, [10, 20, 2]]JSON_REPLACE( @j , '$[1].b[0]' , 1 , '$[2][2]' , 2 ) //["a", {"b": [1, false]}, [10, 20]]SELECT JSON_REMOVE ( @j , '$[2]' , '$[1].b[1]' , '$[1].b[1]' ) ; //["a", {"b": [true]}]

A common scenario of JSON Table Functions is that JSON data itself is a table structure;
JSON_TABLE(*expr *, *path * COLUMNS (*column_list *) [AS\] *alias*)

SELECT * FROM JSON_TABLE ('[{ "a" : "3" },{ "a" : 2 },{ "b" : 1 },{ "a" : 0 },{ "a" :[ 1 , 2 ]}]',   ->      "$[*]" 
 ->      COLUMNS (   ->        rowid FOR ORDINALITY,   ->        ac VARCHAR ( 100 ) PATH "$.a" DEFAULT ' 111 ' ON EMPTY DEFAULT ' 999 ' ON ERROR,  ->        aj JSON PATH "$.a"DEFAULT'{ "x" : 333 }'ON EMPTY,   ->        bx INT EXISTS PATH "$.b"
 ->      )   ->    ) AS tt;
  • Comparison and Ordering of JSON Values
    currently does not feel down to the value;

  • Aggregation of JSON Values
    does not currently feel inferior value; you can use the aggregation function by converting the return value to other types;



Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+