SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
FR: Custom Assistance for JSON Functions in MySQL

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
FR: Custom Assistance for JSON Functions in MySQL
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post FR: Custom Assistance for JSON Functions in MySQL Reply with quote
I've tried to put together some info for Custom Assistance for JSON Functions in MySQL. It is certainly incomplete and might contain error here and there. Could you check them to see if they'd fit (and possibly fix them in case they don't)?

Code:

--Functions That Create JSON Values
JSON_ARRAY([val[, val] ...])JSON_ARRAY;Evaluates a (possibly empty) list of values and returns a JSON array containing those values.
JSON_OBJECT([key, val[, key, val] ...])JSON_OBJECT;Evaluates a (possibly empty) list of key-value pairs and returns a JSON object containing those pairs. An error occurs if any key name is NULL or the number of arguments is odd.
JSON_QUOTE(string)string;Quotes a string as a JSON value by wrapping it with double quote characters and escaping interior quote and other characters, then returning the result as a utf8mb4 string. Returns NULL if the argument is NULL.


--Functions That Search JSON Values
JSON_CONTAINS(json_doc, val[, path])integer;Returns 0 or 1 to indicate whether a specific value is contained in a target JSON document, or, if a path argument is given, at a specific path within the target document. The value to test can be a scalar or JSON document. Returns NULL if any argument is NULL, or if the path argument does not identify a section of the target document. An error occurs if the document argument is not a valid JSON document, or if the path argument is not a valid path expression or contains a * or ** wildcard.
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)integer;Returns 0 or 1 to indicate whether a JSON document contains data at a given path or paths. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, or one_or_all is not 'one' or 'all'.
JSON_EXTRACT(json_doc, path[, path] ...)data;Returns data from a JSON document, selected from the parts of the document matched by the path arguments. Returns NULL if any argument is NULL or no paths locate a value in the document. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression.
JSON_KEYS(json_doc[, path])keys;Returns the keys from the top-level value of a JSON object as a JSON array, or, if a path argument is given, the top-level keys from the selected path. Returns NULL if any argument is NULL, the json_doc argument is not an object, or path, if given, does not locate an object. An error occurs if the json_doc argument is not a valid JSON document or the path argument is not a valid path expression or contains a * or ** wildcard.
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])path;Returns the path to the given string within a JSON document. Returns NULL if any of the json_doc, search_str, or path arguments are NULL; no path exists within the document; or search_str is not found. An error occurs if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, one_or_all is not 'one' or 'all', or escape_char is not a constant expression.


--Functions That Modify JSON Values
JSON_APPEND(json_doc, path, val[, path, val] ...);Appends values to the end of the indicated arrays within a JSON document and returns the result. This function was renamed to JSON_ARRAY_APPEND() in MySQL 5.7.9.
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...);Appends values to the end of the indicated arrays within a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...);Updates a JSON document, inserting into an array within the document and returning the modified document. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard or does not end with an array element identifier.
JSON_INSERT(json_doc, path, val[, path, val] ...);Inserts data into a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.
JSON_MERGE(json_doc, json_doc[, json_doc] ...)json;Merges two or more JSON documents and returns the merged result. Returns NULL if any argument is NULL. An error occurs if any argument is not a valid JSON document.
JSON_REMOVE(json_doc, path[, path] ...)json;Removes data from a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or is $ or contains a * or ** wildcard.
JSON_REPLACE(json_doc, path, val[, path, val] ...);Replaces existing values in a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.
JSON_SET(json_doc, path, val[, path, val] ...);Inserts or updates data in a JSON document and returns the result. Returns NULL if any argument is NULL or path, if given, does not locate an object. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.
JSON_UNQUOTE(json_val)string;Unquotes JSON value and returns the result as a utf8mb4 string. Returns NULL if the argument is NULL. An error occurs if the value starts and ends with double quotes but is not a valid JSON string literal.


--Functions That Return JSON Value Attributes
JSON_DEPTH(json_doc)integer;Returns the maximum depth of a JSON document. Returns NULL if the argument is NULL. An error occurs if the argument is not a valid JSON document.
JSON_LENGTH(json_doc[, path])integer;Returns the length of a JSON document, or, if a path argument is given, the length of the value within the document identified by the path. Returns NULL if any argument is NULL or the path argument does not identify a value in the document. An error occurs if the json_doc argument is not a valid JSON document or the path argument is not a valid path expression or contains a * or ** wildcard.
JSON_TYPE(json_val)string;Returns a utf8mb4 string indicating the type of a JSON value. This can be an object, an array, or a scalar type, as shown here:
JSON_VALID(val)integer;Returns 0 or 1 to indicate whether a value is valid JSON. Returns NULL if the argument is NULL.


Sat Jul 15, 2017 6:35 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Thank you so much
Sat Jul 15, 2017 7:47 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.