MySQL Common JSON functions

Source: Internet
Author: User

Official Document: JSON Functions

Name Description
JSON_APPEND() Append data to JSON document
JSON_ARRAY() Create JSON Array
JSON_ARRAY_APPEND() Append data to JSON document
JSON_ARRAY_INSERT() Insert into JSON array
-> Return value from the JSON column after evaluating path; Equivalent to Json_extract ().
JSON_CONTAINS() Whether JSON document contains specific object at path
JSON_CONTAINS_PATH() Whether JSON document contains any data at path
JSON_DEPTH() Maximum Depth of JSON document
JSON_EXTRACT() Return Data from JSON document
->> Return value from JSON column after evaluating path and unquoting the result; Equivalent to Json_unquote (Json_extract ()).
JSON_INSERT() Insert data into JSON document
JSON_KEYS() Array of keys from JSON document
JSON_LENGTH() Number of elements in JSON document
JSON_MERGE() Merge JSON Documents
JSON_OBJECT() Create JSON Object
JSON_QUOTE() Quote JSON Document
JSON_REMOVE() Remove Data from JSON document
JSON_REPLACE() Replace values in JSON document
JSON_SEARCH() Path to value within JSON document
JSON_SET() Insert data into JSON document
JSON_TYPE() Type of JSON value
JSON_UNQUOTE() Unquote JSON Value
JSON_VALID() Whether JSON value is valid

1. Overview

The JSON in MySQL is divided into JSON array and JSON object. $ represents the entire JSON object, with subscript (for JSON array, starting from 0), or key value (for JSON object, key with special characters "enclosed, such as $.") in the index data. My name ").

For example: [3, {"A": [5, 6], "B": 10}, [99, 100]], then:

$[0]:3

$[1]: {"A": [5, 6], "B": 10}

$[2]: [99, 100]

$[3]: NULL

$[1].a:[5, 6]

$[1].a[1]:6

$[1].b:10

$[2][0]:99

Ii. Rules of comparison

The data in JSON can be compared with =, <, <=,, >=, <>,! =, and <=>. However, the data types in JSON can be varied, so when comparing between different types, there is a priority, high priority is greater than the low priority (you can use the Json_type () function to view the type). The priority level from high to low is as follows:

Blobbitopaquedatetimetimedatebooleanarrayobjectstringinteger, Doublenull

  

Iii. Common functions 3.1 creating a function 3.1.1 Json_array

Json_array (Val1,val2,val3 ...)

Generates a JSON array containing the specified elements.

Mysql> SELECT Json_array (1, "abc", NULL, TRUE, Curtime ()); +---------------------------------------------+| Json_array (1, "abc", NULL, TRUE, Curtime ()) |+---------------------------------------------+| [1, "ABC", NULL, True, "11:30:24.000000"]   | +---------------------------------------------+

  

3.1.2 Json_object

Json_object (Key1,val1,key2,val2 ...)

Generates a JSON object that contains the specified k-v pair. If a key is null or the number of arguments is odd, the error is thrown.

mysql> SELECT json_object (' id ', the ' name ', ' carrot '); +-----------------------------------------+| Json_object (' id ', carrot, ' name ', ' |+ ')-----------------------------------------+| {"id": "," "Name": "Carrot"}            | +-----------------------------------------+

3.1.3 Json_quote

Json_quote (Json_val)

Enclose the json_val with a "number."

mysql> SELECT json_quote (' null '), json_quote (' "null"), +--------------------+----------------------+| Json_quote (' null ') | Json_quote (' "null" ') |+--------------------+----------------------+| "Null"             | "\" Null\ ""           |+--------------------+----------------------+mysql> Select Json_quote (' [1, 2, 3] '); +----------- --------------+| Json_quote (' [1, 2, 3] ') |+-------------------------+| "[1, 2, 3]"             |+-------------------------+

  

3.1.4 CONVERT

CONVERT (Json_string,json)

Mysql> Select CONVERT (' {' "Mail": "[email protected]", "name": "Amy"} ', JSON); +------------------------------------- ---------------------+| CONVERT (' {' "Mail": "[email protected]", "name": "Amy"} ', JSON) |+---------------------------------------------------- ------+| {"Mail": "[email protected]", "name": "Amy"}                 | +----------------------------------------------------------+

  

  

End

MySQL Common JSON functions

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.