如何使用Table StoreSQL查詢JSON文檔 - Tablestore

如果表中某列資料以JSON格式儲存，您可以使用JSON函數查詢JSON資料。Tablestore SQL的JSON函數遵循MySQL 5.7的文法。本文介紹Tablestore SQL支援的JSON函數以及JSON函數的用法。

支援的JSON函數

目前Tablestore SQL支援的JSON函數請參見下表。

JSON函數	說明
->>	從JSON文檔中解出某一路徑對應的文檔並取值 (Dereference)轉換為字串，等效於`JSON_UNQUOTE(JSON_EXTRACT())`。
JSON_UNQUOTE	去除JSON值外的引號，返回結果為字串。
JSON_EXTRACT	從JSON文檔中解出某一路徑對應的子文檔。

->>

->>用於從JSON文檔中解出某一路徑對應的文檔並取值 (Dereference)轉換為字串，等效於JSON_UNQUOTE(JSON_EXTRACT())。

文法

column->>path

函數說明

傳回值為路徑參數匹配的值。

如果任何參數為NULL或所設定的路徑在文檔中未找到值，則返回NULL。

參數

類型

說明

column

String

列名。

path

String

用於定位JSON文檔中的某個路徑。

path必須以$為開頭，$表示整個JSON文檔。其後可以選擇添加直接選取器，選取器可以組合使用。更多資訊，請參見JSON path說明。

樣本

以下樣本用於查詢json_table表pkint主索引值為1的行對應coljson列值中路徑為$.a的資料。

SELECT coljson, coljson->>'$.a' AS subdoc FROM json_table WHERE pkint = 1;

返回結果樣本如下圖所示。

image..png

JSON_UNQUOTE

JSON_UNQUOTE用於去除JSON值外的引號，返回結果為字串。

文法

JSON_UNQUOTE(json_val)

函數說明

傳回值為去除JSON值外面引號的字串。

如果參數為NULL，則返回NULL。

重要

如果值以雙引號開頭和結尾，但不是有效JSON字串文字，則會發生錯誤。

參數

參數	說明
json_val	JSON_EXTRACT語句。更多資訊，請參見JSON_EXTRACT。

樣本

以下樣本用於查詢json_table表pkint主索引值為1的行對應coljson列值中路徑為$.a的資料。

SELECT coljson, JSON_UNQUOTE(JSON_EXTRACT(coljson, '$.a')) AS subdoc FROM json_table WHERE pkint = 1;

返回結果樣本如下圖所示。

1673770680585-e4dc3466-2434-4ccb-be2f-f5166a7e3e49..png

JSON_EXTRACT

JSON_EXTRACT用於從JSON文檔中解出某一路徑對應的子文檔。由於Table Store未支援原生的JSON類型，直接使用會拋出非法型別參數的錯誤，因此JSON_EXTRACT需要結合JSON_UNQUOTE進行使用。

文法

JSON_EXTRACT(json_doc, path[, path] ...)

函數說明

傳回值由路徑參數匹配的所有值組成。如果這些參數可能返回多個值，則匹配的值將自動封裝為一個數組，順序與產生值的路徑相對應，否則傳回值為單個匹配值。

如果任何參數為NULL或所設定的路徑在文檔中未找到值，則返回NULL。

參數

類型

說明

json_doc

String

JSON格式的文檔。

重要

如果json_doc參數不是有效JSON文檔或任何路徑參數不是有效路徑運算式，則會發生錯誤。

path

String

用於定位JSON文檔中的某一個路徑。

path必須以$為開頭，$表示整個JSON文檔。其後可以選擇添加直接選取器，選取器可以組合使用。更多資訊，請參見JSON path說明。

樣本

以下樣本用於查詢json_table表pkint主索引值為1的行對應coljson列值中路徑為$.a的資料。其中coljson列的值為{"a": 1, "b":2, "c":{"d":4}}。

SELECT coljson, JSON_UNQUOTE(JSON_EXTRACT(coljson, '$.a')) AS subdoc FROM json_table WHERE pkint = 1;

返回結果樣本如下圖所示。

1673770680585-e4dc3466-2434-4ccb-be2f-f5166a7e3e49..png

您也可以一次查詢多個path，多個path的結果以數組格式組織。以下SQL語句樣本用於查詢json_table表pkint主索引值為1的行對應coljson列值中路徑為$.a、$.b、$.c.d的資料。其中coljson列的值為{"a": 1, "b":2, "c":{"d":4}}。

SELECT coljson, JSON_UNQUOTE(JSON_EXTRACT(coljson, '$.a', '$.b', '$.c.d')) AS subdoc FROM json_table WHERE pkint = 1;

返回結果樣本如下圖所示。

1673771181990-4c5db21a-7ccc-4a7c-ba4a-c87a36e2aa13..png

JSON path說明

path主要用於定位JSON文檔中的某一個路徑。

path必須以$為開頭，$表示整個JSON文檔。其後可以選擇添加直接選取器，選取器可以組合使用。

重要

如果對應路徑在JSON文檔中不存在資料，則會返回NULL。

直接選取器

主要的直接選取器如下：

$.key用於JSON Object類型。在半形句號（.）後可以添加對應的索引值，用於選中索引值對應的對象，例如$.a。如果key中包含空格，則需要為key添加雙引號，例如$."a b"。
[N]：用於JSON Array類型，選擇數組對應的下標。下標從0開始，例如$[0]、$[1]。
路徑也支援包含星號（*）和**萬用字元，主要用法如下：
- .*：用於計算JSON Object中所有成員的值。
- [*]：用於計算JSON Array中所有元素的值。
- prefix**suffix：表示全部以prefix開始，以suffix結尾的路徑。

樣本

JSON Object查詢

假設要查詢的JSON Object對象如下：

{"a": 1, "f": [1, 2, 3], "c": {"d": 4}}

不同直接選取器配置時的傳回值說明請參見下表。

直接選取器	傳回值
$	`{"a": 1, "c": {"d": 4}, "f": [1, 2, 3]}`
$.a	1
$.c	`{"d": 4}`
$.c.d	4
$.f[1]	2

對於key中含有空格的情況，假設要查詢的JSON Object對象如下：

{"a fish": "shark", "a bird": "sparrow"}

不同直接選取器配置時的傳回值說明請參見下表。

直接選取器	傳回值
$."a fish"	shark
$."a bird"	sparrow

JSON Array查詢

假設要查詢的JSON Array對象如下：

[3, {"a": [5, 6], "b": 10}, [99, 100]]

不同直接選取器配置時的傳回值說明請參見下表。

說明

當傳回值為非標量值時，您可以繼續進行巢狀查詢。例如$[1]和$[2]返回的為非標量值，可以繼續使用$[1].a、$[2][0]等進行巢狀查詢。

直接選取器	傳回值
$[0]	3
$[1]	`{"a": [5, 6], "b": 10}`
$[1].a	`[5, 6]`
$[1].a[1]	6
$[1].b	10
$[2]	`[99, 100]`
$[2][0]	99
$[3]	NULL

萬用字元查詢

假設要查詢的JSON對象如下：

{"a": 1, "b": 2, "c": [3, 4, 5]}

不同直接選取器配置時的傳回值說明請參見下表。

直接選取器	傳回值
$.*	`[1, 2, [3, 4, 5]]`
$.c[*]	`[3, 4, 5]`

假設要查詢的JSON對象如下：

{"a": {"b": 1}, "c": {"b": 2}}

設定直接選取器為$**.b時，傳回值為[1, 2]。等效於（$.a.b和$.c.b）。