MySql: JSON
Json support
As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column:
Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.
Caveats
The size of any JSON document stored in a JSON column is limited to the value of the max_allowed_packet system variable.
A JSON column cannot have a non-NULL default value.
Json functions
Along with the JSON data type, a set of SQL functions is available to enable operations on JSON values, such as creation, manipulation, and searching.
Name | Description |
---|---|
-> | Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT(). |
->> | Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()). |
JSON_APPEND() | (deprecated 5.7.9) 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 |
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 |
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() | (deprecated 5.7.22) Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE() |
JSON_MERGE_PATCH() | Merge JSON documents, replacing values of duplicate keys |
JSON_MERGE_PRESERVE() | Merge JSON documents, preserving duplicate keys |
JSON_OBJECT() | Create JSON object |
JSON_PRETTY() | Print a JSON document in human-readable format |
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_STORAGE_SIZE() | Space used for storage of binary representation of a JSON document |
JSON_TYPE() | Type of JSON value |
JSON_UNQUOTE() | Unquote JSON value |
JSON_VALID() | Whether JSON value is valid |
Export table to json file
- Return json object
--- Return record as json in query
SELECT
json_object(
'entityId', entityId,
'categoryName', categoryName,
'description', description,
'picture', picture
) as json, 'Category'
FROM Category
WHERE entityId = 1 ;
- Json object result
{
"picture": null,
"entityId": 1,
"description": "Soft drinks, coffees, teas, beers, and ales",
"categoryName": "Beverages"
}
- Return record as json array
-- Use json_array and json_object functions
DROP TEMPORARY TABLE IF EXISTS tmp_json_data;
CREATE TEMPORARY TABLE tmp_json_data (
jsonText TEXT
)
SELECT
json_object(
'entityId', entityId,
'categoryName', categoryName,
'description', description,
'picture', picture
) as json
INTO jsonText
FROM Category ;
SET SESSION group_concat_max_len = 9999;
SELECT concat('[', group_concat(jsonText),']') jsonArray
FROM tmp_json_data;
- Json array result
[
{
"categoryName" : "Beverages",
"description" : "Soft drinks, coffees, teas, beers, and ales",
"entityId" : 1,
"picture" : null
},
{
"categoryName" : "Condiments",
"description" : "Sweet and savory sauces, relishes, spreads, and seasonings",
"entityId" : 2,
"picture" : null
},
{
"categoryName" : "Confections",
"description" : "Desserts, candies, and sweet breads",
"entityId" : 3,
"picture" : null
}
]