本文介紹AnalyticDB for MySQL叢集支援的JSON函數。
JSON_ARRAY_CONTAINS：判斷JSON中是否包含
value指定的值。JSON_ARRAY_LENGTH：返回JSON數組的長度。
JSON_CONTAINS（3.1.5.0及以上版本）：判斷指定Path中是否包含
candidate的值；若未指定Path，則判斷Target中是否包含candidate指定的值。JSON_CONTAINS_PATH（3.1.5.0及以上版本）：判斷JSON中是否包含Path列表中的路徑。
JSON_EXTRACT：從JSON中返回
json_path指定的值。JSON_KEYS：若指定了
json_path，表示擷取JSON在指定路徑下的所有鍵。若未指定json_path，表示擷取根路徑（即json_path='$'）下的所有鍵。JSON_OVERLAPS（3.1.10.6及以上版本）：從指定JSON中返回包含
candidate1、candidate2、candidate3等任意一個元素的值。JSON_REMOVE（3.1.10.0及以上版本）：從
json中移除json_path指定的元素，並返回一個新的字串。您可以通過array[json_path,json_path,...]指定移除多個元素。JSON_SIZE：從JSON中返回
json_path指定JSON對象或JSON數組的大小。JSON_SET（3.2.2.8及以上版本）：在
json中插入或更新指定json_path的資料並返回更新後的json。JSON_UNQUOTE（3.1.5.0及以上版本）：去除
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索引
僅3.1.5.0及以上核心版本的叢集支援該文法。
請在雲原生資料倉儲AnalyticDB MySQL控制台集群資訊頁面的配寘資訊地區，查看和升級核心版本。
json_contains(target, candidate[, json_path])命令說明：
若指定了
json_path，則判斷指定Path中是否包含candidate的值。包含返回1，不包含返回0。若未指定
json_path，則判斷Target中是否包含candidate指定的值。包含返回1，不包含返回0。
規則如下：
若
target和candidate均為PRIMITIVE類型（即NUMBER，BOOLEAN，STRING，NULL)，當二者相等時，視為Target包含Candidate。若
target和candidate均為JSON數組，當Candidate的所有元素均包含於Target的某個元素中時，視為Target包含Candidate。若
target為ARRAY類型且candidate為非ARRAY類型，當Candidate包含於Target的某個元素中時，視為Target包含Candidate。若
target和candidate均為JSON對象，當Candidate中的每個Key都包含於Target的Key中，且Candidate的Key對應的Value包含於Target中該Key對應的Value時，視為Target包含Candidate。
輸入實值型別：
target和candidate為JSON類型，json_path為JSONPATH類型。傳回值類型：BOOLEAN。
樣本：
判斷
$.a的路徑下是否包含值1，語句如下：SELECT json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '1', '$.a') as result;返回結果如下：
+--------+ | result | +--------+ | 1 | +--------+判斷
$.b的路徑下是否包含值1，語句如下：SELECT json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '1', '$.b') as result;返回結果如下：
+--------+ | result | +--------+ | 0 | +--------+判斷
{"d": 4}是否包含在Target中，語句如下：SELECT json_contains(json '{"a": 1, "b": 2, "c": {"d": 4}}', json '{"d": 4}') as result;返回結果如下：
+--------+ | result | +--------+ | 0 | +--------+
使用JSON Array索引
僅3.1.10.6及以上核心版本的叢集支援該文法。
指定的JSON列需建立JSON Array索引。詳情請參見建立JSON Array索引。
您可以在SQL查詢語句前增加
EXPLAIN，查看SQL的執行計畫，若執行計畫中無ScanFilterProject運算元，則表明該查詢成功利用JSON Array索引，反之，則未利用JSON Array索引。
json_contains(json_path, cast('[candidate1,candidate2,candidate3]' as json)) 命令說明：判斷指定JSON中是否同時包含
candidate1、candidate2、candidate3等元素。輸入實值型別：
candidate1,candidate2,candidate3,......為數實值型別或字串類型，且多個值的類型需相同。傳回值類型：VARCHAR。
樣本：
判斷指定JSON列
vj中是否包含CP-018673和CP-018671。SELECT json_contains(vj, cast('["CP-018673","CP-018671"]' AS json)) FROM json_test;返回結果如下：
+------------------------------------------------------------+ |json_contains(vj, cast('["CP-018673","CP-018671"]' AS json))| | +------------------------------------------------------------+ | 0 | +------------------------------------------------------------+ | 0 | +------------------------------------------------------------+ | 1 | +------------------------------------------------------------+ | 0 | +------------------------------------------------------------+ | 0 | +------------------------------------------------------------+判斷指定JSON列
vj中是否包含CP-018673、1、2。SELECT json_contains(vj, cast('["CP-018673",1,2]' AS json)) FROM json_test;返回結果如下：
+------------------------------------------------------------+ |json_contains(vj, cast('["CP-018673","CP-018671"]' AS json))| | +------------------------------------------------------------+ | 0 | +------------------------------------------------------------+ | 1 | +------------------------------------------------------------+ | 1 | +------------------------------------------------------------+ | 0 | +------------------------------------------------------------+ | 1 | +------------------------------------------------------------+
JSON_CONTAINS_PATH
json_contains_path(json, one_or_all, json_path[, json_path,...])僅3.1.5.0及以上核心版本的叢集支援使用該函數。
請在雲原生資料倉儲AnalyticDB MySQL控制台集群資訊頁面的配寘資訊地區，查看和升級核心版本。
命令說明：判斷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類型的列一樣，均不支援
ORDER BY。JSON_EXTRACT函數與JSON_UNQUOTE函數連用時，需要先使用CAST AS VARCHAR將JSON_EXTRACT函數的傳回值轉換為VARCHAR類型才能作為JSON_UNQUOTE函數的入參。
json_extract(json, json_path)命令說明：從JSON中返回
json_path指定的值。若json中的Key存在特殊字元（例如：$、.等），json_path的格式需為'$["Key"]'。例如，Key為
$data，json_path需填寫為'$["$data"]'。輸入實值型別：字串類型或JSON類型。
傳回值類型：JSON。
樣本：
返回數組
[10, 20, [30, 40]]中路徑為$[0]的值，語句如下：SELECT json_extract('[10, 20, [30, 40]]', '$[0]');返回結果如下：
+-------------------------------------------+ | json_extract('[10, 20, [30, 40]]', '$[0]') | +-------------------------------------------+ | 10 | +-------------------------------------------+返回
{"id":"1","$date":"12345"}中路徑為$date的值，語句如下：SELECT JSON_EXTRACT('{"id":"1","$date":"12345"}', '$["$date"]');返回結果如下：
+---------------------------------------------------------+ |JSON_EXTRACT('{"id":"1","$date":"12345"}', '$["$date"]') | +---------------------------------------------------------+ | "12345" | +---------------------------------------------------------+
JSON_KEYS
json_keys(json[, json_path])命令說明
若指定了
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
僅3.1.10.6及以上核心版本的叢集支援該文法。
指定的JSON列需建立JSON Array索引。詳情請參見建立JSON Array索引。
您可以在SQL查詢語句前增加
EXPLAIN，查看SQL的執行計畫，若執行計畫中無ScanFilterProject運算元，則表明該查詢成功利用JSON Array索引，反之，則未利用JSON Array索引。
json_overlaps(json, cast('[candidate1,candidate2,candidate]' as json)) 命令說明：返回指定JSON中包含
candidate1、candidate2、candidate3等任意一個元素的資料。輸入實值型別：
candidate1,candidate2,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
僅3.1.10.0及以上核心版本的叢集支援使用JSON_REMOVE函數。
請在雲原生資料倉儲AnalyticDB MySQL控制台集群資訊頁面的配寘資訊地區，查看和升級核心版本。
json_remove(json,json_path)
json_remove(json,array[json_path,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_SET
僅3.2.2.8及以上核心版本的叢集支援使用JSON_SET函數。
請在雲原生資料倉儲AnalyticDB MySQL控制台集群資訊頁面的配寘資訊地區，查看和升級核心版本。
json_set(json, json_path, value[, json_path, value] ...)命令說明：在
json中插入或更新指定json_path的資料並返回更新後的json。如果
json或json_path為null，則返回null。如果
json不符合JSON格式規範，或者任一json_path不是一個有效路徑運算式，則會拋出異常。如果指定的
json_path存在，則會使用value覆蓋原有的資料。如果指定的
json_path在json中不存在：json_path指向的是JSON對象，value會作為新資料添加到json_path指定的位置中。json_path指向的是JSON數組，先判斷指定json_path位置前是否有資料，若沒有，則補充null之後再插入value的資料。反之，則直接插入value的資料。其他情況則會拋出異常。
輸入實值型別：
json：VARCHAR 或JSON。json_path：VARCHAR。value：BOOLEAN、TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、VARCHAR、VARBINARY、DATE、DATETIME、TIMESTAMP、TIME。
傳回值類型：JSON。
樣本：
向
json中插入資料，其中json_path為null。SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', null, '10');返回結果：
+------------------------------------------------+ | JSON_SET('{ "a": 1, "b": [2, 3]}', NULL, '10') | +------------------------------------------------+ | null | +------------------------------------------------+向
json中插入資料，其中json_path不是一個有效路徑運算式。SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.b.c', '10');返回結果：
Failed to execute json_set() for json_path: $.b.c向
json中插入資料，其中json_path1已存在，json_path2不存在且指向的是JSON對象。SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');返回結果：
+-----------------------------------------------------------------------+ | JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') | +-----------------------------------------------------------------------+ | {"a":10,"b":[2,3],"c":"[true, false]"} | +-----------------------------------------------------------------------+向
json中插入資料，指定的json_path不存在，且指向的是JSON數組。SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.b[4]', '[true, false]');返回結果：
+----------------------------------------------------------------+ | JSON_SET('{ "a": 1, "b": [2, 3]}', '$.b[4]', '[true, false]') | +----------------------------------------------------------------+ | {"a":1,"b":[2,3,null,null,"[true, false]"]} | +----------------------------------------------------------------+
JSON_UNQUOTE
json_unquote(json_value)僅3.1.5.0及以上核心版本的叢集支援使用該函數。
請在雲原生資料倉儲AnalyticDB MySQL控制台集群資訊頁面的配寘資訊地區，查看和升級核心版本。
命令說明：去除
json_value的雙引號並將其中的部分轉義符進行轉義後，返回處理結果。AnalyticDB for MySQL不會判斷
json_value的合法性，即無論json_value是否符合JSON文法都會按上述邏輯進行處理。支援的轉義符如下表。
轉義前
轉義後
\"雙引號（
"）。\b退格鍵。
\f換頁符。
\n分行符號。
\r斷行符號符。
\tTab鍵。
\\反斜線（
\）。\uXXXXUTF-8字元表示。
輸入實值型別：VARCHAR。
傳回值類型：VARCHAR。
樣本：
返回去除引號後的字串
abc，語句如下：SELECT json_unquote('"abc"');返回結果如下：
+-----------------------+ | json_unquote('"abc"') | +-----------------------+ | abc | +-----------------------+返回去除引號並解析後的字串，語句如下：
SELECT json_unquote('"\\t\\u0032"');返回結果如下：
+------------------------------+ | json_unquote('"\\t\\u0032"') | +------------------------------+ | 2 | +------------------------------+
附錄：JSON Path文法說明
使用方法
使用
$.keyName[.keyName]...訪問JSON對象中的指定鍵。使用
$[nonNegativeInteger]訪問JSON數組中的第n個元素，n為非負整數。使用
$.keyName[.keyName]...[nonNegativeInteger]訪問JSON對象中嵌套的JSON數組的第n個元素，n為非負整數。
注意事項
AnalyticDB for MySQL JSON Path文法不支援萬用字元*和**。即不支援以下寫法'$.*'、'$.hobbies[*]'、'$.address.**'以及'$.hobbies.**'。
樣本
假設有以下JSON資料。
{
"name": "Alice",
"age": 25,
"address": {
"city": "Hangzhou",
"zip": "10001"
},
"hobbies":["reading", "swimming", "cycling"]
}樣本說明 | 正確樣本 | 錯誤樣本 |
訪問鍵name的值 | $.name | name |
訪問嵌套對象city鍵的值 | $.address.city | $.address[0] |
訪問JSON數組hobbies的第1個元素 | $.hobbies[0] | $.hobbies.[0] |
常見問題
如何解決使用JSON_OVERLAPS函數時，出現java.lang.NullPointerException報錯？
原因：使用ALTER語句建立JSON索引後，未執行BUILD操作，或BUILD操作尚未完成，此時JSON索引尚未生效，導致該報錯。
解決方案：
若未執行BUILD操作：
AnalyticDB for MySQL叢集會在滿足一定條件後自動觸發BUILD任務，您也可以手動觸發BUILD任務。
若已執行BUILD操作：
您可以通過如下語句，查詢BUILD任務的狀態。若返回欄位
status的值為FINISH，表示BUILD操作已完成。SELECT table_name, schema_name, status FROM INFORMATION_SCHEMA.KEPLER_META_BUILD_TASK ORDER BY create_time DESC LIMIT 10;
更多BUILD的資訊，請參見BUILD。