AnalyticDB：JSON函數

• JSON_ARRAY_CONTAINS：判斷JSON中是否包含value指定的值。

• JSON_ARRAY_LENGTH：返回JSON數組的長度。

• JSON_CONTAINS：判斷指定Path中是否包含candidate的值；若未指定Path，則判斷Target中是否包含candidate指定的值。

• JSON_CONTAINS_PATH：判斷JSON中是否包含Path列表中的路徑。

• JSON_EXTRACT：從JSON中返回json_path指定的值。

• JSON_KEYS：若指定了json_path，表示擷取JSON在指定路徑下的所有鍵。若未指定json_path，表示擷取根路徑（即json_path='$'）下的所有鍵。

• JSON_OVERLAPS：從指定JSON中返回包含candidate1、candidate2、candidate3等任意一個元素的值。

• JSON_REMOVE：從json中移除json_path指定的元素，並返回一個新的字串。您可以通過array[json_path,json_path,...]指定移除多個元素。

• JSON_SIZE：從JSON中返回json_path指定JSON對象或JSON數組的大小。

• JSON_UNQUOTE：去除json_value的雙引號並將json_value中的部分轉義符進行轉義後，返回處理結果。

JSON_ARRAY_CONTAINS

json_array_contains(json, value)

• 命令說明：判斷JSON數組中是否包含value指定的值。

• 輸入實值型別：value可以是數值、字串類型或BOOLEAN類型。

• 傳回值類型：BOOLEAN。

• 樣本：

  • 判斷JSON數組 [1, 2, 3]中是否包含值為2的元素，語句如下：

    SELECT json_array_contains('[1, 2, 3]', 2);

    返回結果如下：

    +-------------------------------------+
    | json_array_contains('[1, 2, 3]', 2) |
    +-------------------------------------+
    |                1                    |
    +-------------------------------------+

JSON_ARRAY_LENGTH

json_array_length(json)

• 命令說明：返回JSON數組的長度。

• 輸入實值型別：字串類型或JSON類型。

• 傳回值類型：BIGINT。

• 樣本：

  • 返回JSON數組[1, 2, 3]的長度，語句如下：

    SELECT json_array_length('[1, 2, 3]');

    返回結果如下：

    +--------------------------------+
    | json_array_length('[1, 2, 3]') |
    +--------------------------------+
    |                 3              |
    +--------------------------------+

JSON_CONTAINS

JSON_CONTAINS函數用於判斷指定JSON中是否包含特定內容，您可以在查詢資料時使用JSON Array索引，避免掃描全表資料或對整個JSON文檔進行解析，提升資料查詢效率。

json_contains(target, candidate[, json_path])

json_contains(json_path, cast('[candidate1,candidate2,candidate3]' as json)) 

JSON_CONTAINS_PATH

json_contains_path(json, one_or_all, json_path[，path,...])

• 命令說明：判斷JSON中是否包含Path對應的值。

  • 當one_or_all為'one'，JSON中包含所有Path中的其中之一時，返回1，否則返回0。

  • 當one_or_all為'all'，JSON中包含所有Path路徑時，返回1，否則返回0。

• 輸入實值型別：json為JSON類型，one_or_all為VARCHAR類型（為'one'或者'all'，不區分大小寫），json_path為Path路徑。

• 傳回值類型：BOOLEAN。

• 樣本：

  • 判斷JSON中是否包含$.a和$.e至少一個的路徑，語句如下：

    SELECT json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e') AS RESULT;

    返回結果如下：

    +--------+
    | result |
    +--------+
    |      1 |
    +--------+

  • 判斷JSON中是否包含$.a和$.e全部的路徑，語句如下：

    SELECT json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e') AS RESULT;

    返回結果如下：

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+

JSON_EXTRACT

json_extract(json, json_path)

• 命令說明：從JSON中返回json_path指定的值。

• 輸入實值型別：字串類型或JSON類型。

• 傳回值類型：JSON。

• 樣本：

  • 返回數組[10, 20, [30, 40]]中路徑為$.1的值，語句如下：

    SELECT json_extract('[10, 20, [30, 40]]', '$.1');

    返回結果如下：

    +-------------------------------------------+
    | json_extract('[10, 20, [30, 40]]', '$.1') |
    +-------------------------------------------+
    |                     20                    |
    +-------------------------------------------+

JSON_KEYS

json_keys(json, json_path)
json_keys(json)

• 命令說明

  • 若指定了json_path，表示擷取JSON在指定路徑下的所有鍵。

  • 若未指定json_path，表示擷取根路徑（即json_path='$'）下的所有鍵。

• 輸入實值型別：僅支援輸入JSON類型的參數。

  您可以通過如下方式構造JSON資料：

  • 直接使用JSON資料。例如json '{"a": 1, "b": {"c": 30}}'。

  • 通過CAST函數將字串進行顯式轉換為JSON資料。例如CAST('{"a": 1, "b": {"c": 30}}' AS json)。

• 傳回值類型：JSON ARRAY。

• 樣本：

  • 返回$.b路徑下的所有鍵，語句如下：

    SELECT json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json),'$.b');

    返回結果如下：

    +-----------------------------------------------------------+
    | json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json),'$.b') |
    +-----------------------------------------------------------+
    |                           ["c"]                           |
    +-----------------------------------------------------------+

  • 返回根路徑下的所有鍵，語句如下：

    SELECT JSON_KEYS(json '{"a": 1, "b": {"c": 30}}');

    返回結果如下：

    +--------------------------------------------+
    | JSON_KEYS(json '{"a": 1, "b": {"c": 30}}') |
    +--------------------------------------------+
    |             ["a","b"]                      |
    +--------------------------------------------+

JSON_OVERLAPS

json_overlaps(json_path, cast('[candidate1,candidate2,candidate]' as json)) 

• 命令說明：返回指定JSON中包含candidate1、candidate2、candidate3等任意一個元素的資料。

• 輸入實值型別：candidate1candidate2,candidate3,......為數實值型別或字串類型，且多個值的類型需相同。

• 傳回值類型：VARCHAR。

• 樣本：

  • 返回指定JSON列vj中包含CP-018673的資料。

    SELECT * FROM json_test WHERE json_overlaps(vj, cast('["CP-018673"]' AS json));

    返回結果如下：

    +-----+----------------------------------------------------------------------------+
    |  id |   vj                                                                       |
    +-----+----------------------------------------------------------------------------+
    |  2  | ["CP-018673", 1, false]                                                    |
    +-----+----------------------------------------------------------------------------+
    |  3  | ["CP-018673", 1, false, {"a": 1}]                                          |
    +-----+----------------------------------------------------------------------------+
    |  5  | ["CP-018673","CP-018671","CP-018672","CP-018670","CP-018669","CP-018668"]  |
    +-----+----------------------------------------------------------------------------+

  • 返回指定JSON列vj中包含1、2、3任意一個元素的資料。

    SELECT * FROM json_test WHERE json_overlaps(vj, cast('[1,2,3]' AS json))

    返回結果如下：

    +-----+-------------------------------------+
    |  id |                 vj                  |
    +-----+-------------------------------------+
    |  1  | [1,2,3]                             |
    +-----+-------------------------------------+
    |  2  | ["CP-018673", 1, false]             |
    +-----+-------------------------------------+
    |  3  | ["CP-018673", 1, false, {"a": 1}]   |
    +-----+-------------------------------------+

JSON_REMOVE

json_remove(json,json_path)
json_remove(json,array[json_path])

• 命令說明：從json中移除json_path指定的元素，並返回一個新的字串。您可以通過array[json_path,json_path,...]指定移除多個元素。

• 輸入實值型別：json為JSON格式的VARCHAR類型。json_path為JSON格式的VARCHAR類型。

• 傳回值類型：VARCHAR。

• 樣本

  • 移除路徑為$.glossary.GlossDiv的部分，並返回修改後的字串，語句如下：

      SELECT json_remove(
      '{
          "glossary": {
              "title": "example glossary",
              "GlossDiv": {
                  "title": "S",
                  "GlossList": {
                      "GlossEntry": {
                          "ID": "SGML",
                          "SortAs": "SGML",
                          "GlossTerm": "Standard Generalized Markup Language",
                          "Acronym": "SGML",
                          "Abbrev": "ISO 8879:1986",
                          "GlossDef": {
                              "para": "A meta-markup language, used to create markup languages such as DocBook.",
                              "GlossSeeAlso": ["GML", "XML"]
                          },
                          "GlossSee": "markup"
                      }
                  }
              }
          }
      }'
      , '$.glossary.GlossDiv') a;

    返回結果如下：

    {"glossary":{"title":"example glossary"}}

  • 移除路徑為$.glossary.title和$.glossary.GlossDiv.title的部分，並返回修改後的字串，語句如下：

    SELECT json_remove(
      '{
          "glossary": {
              "title": "example glossary",
              "GlossDiv": {
                  "title": "S",
                  "GlossList": {
                      "GlossEntry": {
                          "ID": "SGML",
                          "SortAs": "SGML",
                          "GlossTerm": "Standard Generalized Markup Language",
                          "Acronym": "SGML",
                          "Abbrev": "ISO 8879:1986",
                          "GlossDef": {
                              "para": "A meta-markup language, used to create markup languages such as DocBook.",
                              "GlossSeeAlso": ["GML", "XML"]
                          },
                          "GlossSee": "markup"
                      }
                  }
              }
          }
      }'
      , array['$.glossary.title', '$.glossary.GlossDiv.title']) a;

    返回結果如下：

    {"glossary":{"GlossDiv":{"GlossList":{"GlossEntry":{"GlossTerm":"Standard Generalized Markup Language","GlossSee":"markup","SortAs":"SGML","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"ID":"SGML","Acronym":"SGML","Abbrev":"ISO 8879:1986"}}}}}

JSON_SIZE

json_size(json, json_path)

• 命令說明：從JSON中返回json_path指定JSON對象或JSON數組的大小。

  說明

  若json_path指向的不是JSON對象或者JSON數組時，返回0。

• 輸入實值型別：字串類型或JSON類型。

• 傳回值類型：BIGINT。

• 樣本：

  • json_path指向的是JSON對象，語句如下：

    SELECT json_size('{"x":{"a":1, "b": 2}}', '$.x') as result;

    返回結果如下：

    +--------+
    | result |
    +--------+
    |      2 |
    +--------+

  • json_path指向的不是JSON對象或者JSON數組，語句如下：

    SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a') as result;

    返回結果如下：

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+

JSON_UNQUOTE

json_unquote(json_value)

• 命令說明：去除json_value的雙引號並將其中的部分轉義符進行轉義後，返回處理結果。

  AnalyticDB for MySQL不會判斷json_value的合法性，即無論json_value是否符合JSON文法都會按上述邏輯進行處理。

  支援的轉義符如下表。

  轉義前	轉義後
  \"	雙引號（"）。
  \b	退格鍵。
  \f	換頁符。
  \n	分行符號。
  \r	斷行符號符。
  \t	Tab鍵。
  \\	反斜線（\）。
  \uXXXX	UTF-8字元表示。

• 輸入實值型別：VARCHAR。

• 傳回值類型：VARCHAR。

• 樣本：

  • 返回去除引號後的字串abc，語句如下：

    SELECT json_unquote('"abc"');

    返回結果如下：

    +-----------------------+
    | json_unquote('"abc"') |
    +-----------------------+
    |          abc          |
    +-----------------------+

  • 返回去除引號並解析後的字串，語句如下：

    SELECT json_unquote('"\\t\\u0032"');

    返回結果如下：

    +------------------------------+
    | json_unquote('"\\t\\u0032"') |
    +------------------------------+
    |               2              |
    +------------------------------+