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)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料