# JSON函数
JSON函数差异说明：
- 对于JSON函数和其他字符入参函数，如果输入中包含转义字符，默认情况下会与MySQL有一定差异。如果需要实现与MySQL的兼容，需要开启转义符开关（在GUC参数m_format_behavior_compat_options中添加enable_escape_string配置选项），但在\\0和\\uxxxx的场景与MySQL依然存在差异。
- 当JSON函数的入参的字符集不一致时，MySQL均会将参数转换为utf8mb4字符集进行处理。GaussDB对于不同字符集的入参首先需要进行字符集合并推导，如果推导失败则报错。
  ```
  -- GaussDB行为
  m_db=# select json_array(_latin1 'b', _gbk 'a');
  ERROR:  Collation mismatch between collations "latin1_swedish_ci" and "gbk_chinese_ci".
  LINE 1: select json_array(_latin1 'b', _gbk 'a');
                                              ^
  -- MySQL行为
  mysql> select json_array(_latin1 'b', _gbk 'a');
  +-----------------------------------+
  | json_array(_latin1 'b', _gbk 'a') |
  +-----------------------------------+
  | ["b", "a"]                        |
  +-----------------------------------+
  1 row in set (0.00 sec)
  ```
  
表1JSON函数列表 
| 函数名                      | 与MySQL的差异                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|:---|:---|
| JSON_APPEND()           | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| JSON_ARRAY()           | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| JSON_ARRAY_APPEND()     | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| JSON_ARRAY_INSERT()     | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| JSON_CONTAINS()       | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| JSON_CONTAINS_PATH() | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| JSON_DEPTH()           | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| JSON_EXTRACT()           | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| JSON_INSERT()         | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| JSON_KEYS()             | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| JSON_LENGTH()             | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| JSON_MERGE()            | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| JSON_MERGE_PATCH()     | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| JSON_MERGE_PRESERVE() | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| JSON_OBJECT()          | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| JSON_QUOTE()           | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| JSON_REMOVE()          | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| JSON_REPLACE()          | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| JSON_SEARCH()         | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| JSON_SET()              | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| JSON_TYPE()            | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| JSON_UNQUOTE()       | 在转义字符中\\0和\\uxxxx的场景与MySQL有差异： ``` SELECT json_unquote('"\0"'); mysql> SELECT json_unquote('"\0"'); ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_unquote: "Missing a closing quotation mark in string." at position 1. m_db=# SELECT json_unquote('"\0"'); ERROR:  invalid byte sequence for encoding "UTF8": 0x00 ```                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| JSON_VALID()           | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| JSON_PRETTY()           | 函数返回值的字符序与MySQL的存在差异。MySQL中固定为utf8mb4_bin，GaussDB中取决于GUC参数[collation_connection](https://support.huaweicloud.com/centralized-ref-v10-gaussdb/gaussdb-38-1043.html#ZH-CN_TOPIC_0000002793699349__section11073158258)的取值。 ``` -- GaussDB m_db=# SET collation_connection=gbk_bin; SET m_db=# SELECT COLLATION(JSON_PRETTY('{"1": "2"}')); collation ----------- gbk_bin (1 row) -- MySQL mysql> SET collation_connection=gbk_bin; Query OK, 0 rows affected (0.00 sec) mysql> SELECT COLLATION(JSON_PRETTY('{"1": "2"}')); +--------------------------------------+ | COLLATION(JSON_PRETTY('{"1": "2"}')) | +--------------------------------------+ | utf8mb4_bin                          | +--------------------------------------+ 1 row in set (0.00 sec) ```                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| JSON_STORAGE_SIZE()    | - 由于GaussDB的json类型内部存储方式与MySQL的存在差异，该函数返回的值与MySQL存在不一致。  - 由于GaussDB的json类型未适配部分更新功能，会实时更新json类型的存储大小。 ``` -- GaussDB m_db=# CREATE TABLE jtable (jcol JSON); CREATE TABLE                        ^ m_db=# INSERT INTO jtable VALUES  ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}'); INSERT 0 1 m_db=# SELECT  jcol, JSON_STORAGE_SIZE(jcol) AS Size FROM jtable; jcol                      | Size -----------------------------------------------+------ {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   83 (1 row) ^ m_db=# UPDATE jtable SET jcol =JSON_SET(jcol, "$.b", "a"); UPDATE 1 m_db=# SELECT  jcol,JSON_STORAGE_SIZE(jcol) AS Size from jtable; jcol                    | Size --------------------------------------------+------ {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} |   80 (1 row) -- MySQL mysql> CREATE TABLE jtable (jcol JSON); Query OK, 0 rows affected (0.42 sec) mysql> INSERT INTO jtable VALUES ->     ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}'); Query OK, 1 row affected (0.04 sec) mysql> SELECT ->     jcol, ->     JSON_STORAGE_SIZE(jcol) AS Size, ->     JSON_STORAGE_FREE(jcol) AS Free -> FROM jtable; +-----------------------------------------------+------+------+ | jcol                                          | Size | Free | +-----------------------------------------------+------+------+ | {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |    0 | +-----------------------------------------------+------+------+ 1 row in set (0.00 sec) mysql> UPDATE jtable SET jcol = ->     JSON_SET(jcol, "$.b", "a"); Query OK, 1 row affected (0.04 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT ->     jcol, ->     JSON_STORAGE_SIZE(jcol) AS Size, ->     JSON_STORAGE_FREE(jcol) AS Free -> FROM jtable; +--------------------------------------------+------+------+ | jcol                                       | Size | Free | +--------------------------------------------+------+------+ | {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} |   47 |    3 | +--------------------------------------------+------+------+ 1 row in set (0.00 sec) ```    |
   
