MariaDB 函數 – JSON_TYPE , JSON_LENGTH 與 JSON_DEPTH

測試環境為 CentOS 8 x86_64

先來看一下 JSON 不同的資料類型 (透過 JSON_TYPE 函數來檢視目前資料是哪一種型態)：

1. 物件 (Object) : 使用 { } , 無序成對 (pair) 的 {鍵 Key name :值 value} 方式來儲存,鍵值之間使用逗號來分隔.

   MariaDB [(none)]> SELECT JSON_TYPE('{"A":"1"}');
   +------------------------+
   | JSON_TYPE('{"A":"1"}') |
   +------------------------+
   | OBJECT                 |
   +------------------------+
   1 row in set (0.000 sec)
   

2. 陣列 (Array) : 使用 [ ] , 資料彼此間使用逗號來分割,如：[value, value] .

   MariaDB [(none)]> SELECT JSON_TYPE('[0,1]');
   +--------------------+
   | JSON_TYPE('[0,1]') |
   +--------------------+
   | ARRAY              |
   +--------------------+
   1 row in set (0.000 sec)
   

3. 數字 (Number) : 直接使用整數 (INTEGER) 或是浮點數 (DOUBLE).

   MariaDB [(none)]> SELECT JSON_TYPE('0');
   +----------------+
   | JSON_TYPE('0') |
   +----------------+
   | INTEGER        |
   +----------------+
   1 row in set (0.000 sec)
   
   MariaDB [(none)]> SELECT JSON_TYPE('0.1');
   +------------------+
   | JSON_TYPE('0.1') |
   +------------------+
   | DOUBLE           |
   +------------------+
   1 row in set (0.000 sec)
   

4. 字串 (String) : 使用 “” (括號)來表示字串.

   MariaDB [(none)]> SELECT JSON_TYPE('"ST"');
   +-------------------+
   | JSON_TYPE('"ST"') |
   +-------------------+
   | STRING            |
   +-------------------+
   1 row in set (0.000 sec)
   

5. 布林函數 (Boolean) : 使用 TRUE 或 FALSE.

   MariaDB [(none)]> SELECT JSON_TYPE('true');
   +-------------------+
   | JSON_TYPE('true') |
   +-------------------+
   | BOOLEAN           |
   +-------------------+
   1 row in set (0.000 sec)
   
   MariaDB [(none)]> SELECT JSON_TYPE('false');
   +--------------------+
   | JSON_TYPE('false') |
   +--------------------+
   | BOOLEAN            |
   +--------------------+
   1 row in set (0.000 sec)
   

6. NULL 類型.

   
   MariaDB [(none)]> SELECT JSON_TYPE(NULL);
   +-----------------+
   | JSON_TYPE(NULL) |
   +-----------------+
   | NULL            |
   +-----------------+
   1 row in set (0.000 sec)
   

JSON_LENGTH

• A scalar’s length is always 1.

  MariaDB [(none)]> SELECT JSON_LENGTH('0') , JSON_LENGTH('1.1') , JSON_LENGTH('"TH"');
  +------------------+--------------------+---------------------+
  | JSON_LENGTH('0') | JSON_LENGTH('1.1') | JSON_LENGTH('"TH"') |
  +------------------+--------------------+---------------------+
  |                1 |                  1 |                   1 |
  +------------------+--------------------+---------------------+
  1 row in set (0.000 sec)
  

• If an array, the number of elements in the array.

  MariaDB [(none)]> SELECT JSON_LENGTH('[1,2,3]');
  +------------------------+
  | JSON_LENGTH('[1,2,3]') |
  +------------------------+
  |                      3 |
  +------------------------+
  1 row in set (0.001 sec)
  

• If an object, the number of members in the object.

  MariaDB [(none)]> SELECT JSON_LENGTH('{"A":1 , "B":2}');
  +--------------------------------+
  | JSON_LENGTH('{"A":1 , "B":2}') |
  +--------------------------------+
  |                              2 |
  +--------------------------------+
  1 row in set (0.000 sec)
  

JSON_DEPTH

• Scalar values or empty arrays or objects have a depth of 1.

  MariaDB [(none)]> SELECT JSON_DEPTH('[]'), JSON_DEPTH('true'), JSON_DEPTH('{}');
  +------------------+--------------------+------------------+
  | JSON_DEPTH('[]') | JSON_DEPTH('true') | JSON_DEPTH('{}') |
  +------------------+--------------------+------------------+
  |                1 |                  1 |                1 |
  +------------------+--------------------+------------------+
  1 row in set (0.000 sec)
  

• Arrays or objects that are not empty but contain only elements or member values of depth 1 will have a depth of 2.

  MariaDB [(none)]> SELECT JSON_DEPTH('[1]'), JSON_DEPTH('{"A":"1"}');
  +-------------------+-------------------------+
  | JSON_DEPTH('[1]') | JSON_DEPTH('{"A":"1"}') |
  +-------------------+-------------------------+
  |                 2 |                       2 |
  +-------------------+-------------------------+
  1 row in set (0.000 sec)
  

• In other cases, the depth will be greater than 2.
  下面範例可以看到 B 裡面有 B1 , B1 裡面有 B11 ,所以深度為 2(Array 與 Object 深度為)+2(B1 與 B11)=4

  MariaDB [(none)]> SELECT JSON_DETAILED('{"A":1, "B":{"B1":{"B11":2}}}');
  +-----------------------------------------------------------------------------------------------+
  | JSON_DETAILED('{"A":1, "B":{"B1":{"B11":2}}}')                                                |
  +-----------------------------------------------------------------------------------------------+
  | {
      "A": 1,
      "B": 
      {
          "B1": 
          {
              "B11": 2
          }
      }
  } |
  +-----------------------------------------------------------------------------------------------+
  1 row in set (0.000 sec)
  
  MariaDB [(none)]> SELECT JSON_DEPTH('{"A":1, "B":{"B1":{"B11":2}}}');
  +---------------------------------------------+
  | JSON_DEPTH('{"A":1, "B":{"B1":{"B11":2}}}') |
  +---------------------------------------------+
  |                                           4 |
  +---------------------------------------------+
  1 row in set (0.001 sec)