json_parse

description

Syntax

1. JSON json_parse(VARCHAR json_str)
2. JSON json_parse_error_to_null(VARCHAR json_str)
3. JSON json_parse_error_to_value(VARCHAR json_str, VARCHAR default_json_str)

json_parse functions parse JSON string to binary format. A series of functions are provided to satisfy different demand for exception handling.

• all return NULL if json_str is NULL
• if json_str is not valid
• json_parse will report error
• json_parse_error_to_null will return NULL
• json_parse_error_to_value will return the value specified by default_json_str

example

1. parse valid JSON string

1. mysql> SELECT json_parse('{"k1":"v31","k2":300}');
2. +--------------------------------------+
3. | json_parse('{"k1":"v31","k2":300}') |
4. +--------------------------------------+
5. | {"k1":"v31","k2":300}                |
6. +--------------------------------------+
7. 1 row in set (0.01 sec)

1. parse invalid JSON string

1. mysql> SELECT json_parse('invalid json');
2. ERROR 1105 (HY000): errCode = 2, detailMessage = json parse error: Invalid document: document must be an object or an array for value: invalid json

4. mysql> SELECT json_parse_error_to_null('invalid json');
5. +-------------------------------------------+
6. | json_parse_error_to_null('invalid json') |
7. +-------------------------------------------+
8. | NULL                                      |
9. +-------------------------------------------+
10. 1 row in set (0.01 sec)

12. mysql> SELECT json_parse_error_to_value('invalid json', '{}');
13. +--------------------------------------------------+
14. | json_parse_error_to_value('invalid json', '{}') |
15. +--------------------------------------------------+
16. | {}                                               |
17. +--------------------------------------------------+
18. 1 row in set (0.00 sec)

refer to json tutorial for more.

keywords

JSONB, JSON, json_parse, json_parse_error_to_null, json_parse_error_to_value