JSON資料類型 - AnalyticDB

AnalyticDB for MySQL支援JSON資料類型。本文主要介紹JSON資料的格式、注意事項及使用樣本。

注意事項

AnalyticDB MySQL版支援標準JSON格式，寫入JSON串時必須嚴格符合標準JSON格式規範。
JSON類型的資料列，不支援設定Default值。

JSON格式要求

AnalyticDB for MySQL對JSON資料中的屬性鍵key和屬性值value有如下要求：

屬性鍵key
必須使用雙引號（""）將key引起來，例如{"addr":"xyz"}中的"addr"。
屬性值value
- 屬性值value支援的資料類型為：BOOLEAN、NUMBER、VARCHAR、ARRAY、OBJECT、NULL。
  說明
  - 使用JSON索引時，NUMBER不能超過DOUBLE的取值範圍。
  - ARRAY類型可以為PLAIN ARRAY或嵌套ARRAY。例如，{"hobby":["basketball", "football"]}和{"addr":[{"city":"beijing", "no":0}, {"city":"shenzhen", "no":0}]}。
- 如果value是字串類型，必須使用雙引號（""）將value引起來。
  說明
  如果value是字串類型，且value中包含雙引號，需要做轉義處理。例如，{"addr":"xyz"ab"c"}中的value，即"xyz"ab"c"部分，需轉義為"xyz\"ab\"c"，但由於寫入過程中\會被轉義，因此寫入的資料應為{"addr":"xyz\\"ab\\"c"}。
- 如果value是數實值型別，直接寫資料，不能使用雙引號（""）將value引起來。
- 如果value是Boolean類型，直接寫true或者false，不能寫成1或者0，且true和false不能大寫。
- 如果value是Null，直接寫Null。
- 同一個key支援不同類型的value，查詢時會返回指定類型的結果。
  例如，執行INSERT INTO test_tb1 VALUES ({"id": 1})語句時，表示插入的id值是數字1；而執行INSERT INTO test_tb1 VALUES ({"id": "1"})語句時，表示插入的id值是字串"1"。
  如果執行SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= 1;語句進行查詢時，將返回數字"id": 1；而執行SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= '1';語句進行查詢時，將返回字串"id": "1"。

使用樣本

建立表

CREATE TABLE json_test(
  id int,
  vj json 
)
DISTRIBUTED BY HASH(id);

寫入資料

寫入資料時，JSON類型欄位的寫入方式與VARCHAR類型欄位的寫入方式相同，即在JSON串兩端使用單引號引起來。以下SQL樣本包含多種JSON資料格式，供您參考使用。

INSERT INTO json_test VALUES(0, '{"id":0, "name":"abc", "age":0}');
INSERT INTO json_test VALUES(1, '{"id":1, "name":"abc", "age":10, "gender":"f"}');
INSERT INTO json_test VALUES(2, '{"id":3, "name":"xyz", "age":30, "company":{"name":"alibaba", "place":"hangzhou"}}');
INSERT INTO json_test VALUES(3, '{"id":5, "name":"a\\"b\\"c", "age":50, "company":{"name":"alibaba", "place":"america"}}');
INSERT INTO json_test VALUES(4, '{"a":1, "b":"abc-char", "c":true}');
INSERT INTO json_test VALUES(5, '{"uname":{"first":"lily", "last":"chen"}, "addr":[{"city":"beijing", "no":1}, {"city":"shenzhen", "no":0}], "age":10, "male":true, "like":"fish", "hobby":["basketball", "football"]}');

查詢資料

查詢資料時，AnalyticDB for MySQL支援使用函數json_extract。

文法
```
json_extract(json, jsonpath)
```
命令說明
從JSON中返回jsonpath指定的值。
參數說明
- json：JSON列的列名。
- jsonpath：通過點號（.）進行分割的JSON屬性鍵key的路徑，其中$表示最外層的路徑。
更多JSON函數用法請參見JSON函數。

樣本

基本查詢

SELECT json_extract(vj,'$.name') FROM json_test WHERE id=1;

等值查詢

SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') = 'abc';
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.c') = true;
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') = 30;
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.company.name') = 'alibaba';

範圍查詢

SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') > 0;
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') < 100;
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') > 'a' and json_extract(vj, '$.name') < 'z';

IS NULL或IS NOT NULL查詢

SELECT id, vj FROM json_test WHERE json_extract(vj, '$.remark') is null;
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') is not null;

IN 查詢

SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') in ('abc','xyz');
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') in (10,20);

LIKE查詢

SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') like 'ab%';
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') like '%bc%';
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') like '%bc';

ARRAY查詢
```
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.addr[0].city') = 'beijing' and json_extract(vj, '$.addr[1].no') = 0;
```
說明
查詢ARRAY資料時，支援使用指定資料下標取值，序號從0開始遞增，暫不支援遍曆整個數組。

AnalyticDB：JSON

注意事項

JSON格式要求

使用樣本

建立表

寫入資料

查詢資料

相關文檔