jsonb_parse

description

jsonb_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
    • jsonb_parse will report error
    • jsonb_parse_error_to_null will return NULL
    • jsonb_parse_error_to_value will return the value specified by default_json_str

Syntax

JSONB jsonb_parse(VARCHAR json_str) JSONB jsonb_parse_error_to_null(VARCHAR json_str) JSONB jsonb_parse_error_to_value(VARCHAR json_str, VARCHAR default_json_str)

example

  1. parse valid JSON string
  1. mysql> SELECT jsonb_parse('{"k1":"v31","k2":300}');
  2. +--------------------------------------+
  3. | jsonb_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 jsonb_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 jsonb_parse_error_to_null('invalid json');
  5. +-------------------------------------------+
  6. | jsonb_parse_error_to_null('invalid json') |
  7. +-------------------------------------------+
  8. | NULL                                      |
  9. +-------------------------------------------+
  10. 1 row in set (0.01 sec)
  12. mysql> SELECT jsonb_parse_error_to_value('invalid json', '{}');
  13. +--------------------------------------------------+
  14. | jsonb_parse_error_to_value('invalid json', '{}') |
  15. +--------------------------------------------------+
  16. | {}                                               |
  17. +--------------------------------------------------+
  18. 1 row in set (0.00 sec)

refer to jsonb tutorial for more.

keywords

JSONB, JSON, jsonb_parse, jsonb_parse_error_to_null, jsonb_parse_error_to_value