JSON - AnalyticDB - Alibaba Cloud

AnalyticDB for MySQL mendukung tipe data JSON. Topik ini menjelaskan format, catatan penggunaan, dan contoh data JSON.

Catatan penggunaan

AnalyticDB for MySQL mendukung format JSON standar. Saat menulis string JSON, Anda harus mengikuti spesifikasi format JSON standar secara ketat.
Nilai default tidak dapat diatur untuk kolom dengan tipe data JSON.

Persyaratan format JSON

AnalyticDB for MySQL memiliki persyaratan berikut untuk properti key dan properti value data JSON:

Properti keykey
Apit key dengan tanda kutip ganda (""). Misalnya, "addr" dalam {"addr":"xyz"}.
Properti valuevalue
- Properti value mendukung tipe data berikut: BOOLEAN, NUMBER, VARCHAR, ARRAY, OBJECT, NULL.
  Catatan
  - Saat menggunakan indeks JSON, nilai NUMBER tidak boleh melebihi rentang nilai DOUBLE.
  - Tipe ARRAY dapat berupa array biasa atau array bersarang. Contohnya, {"hobby":["basketball", "football"]} dan {"addr":[{"city":"beijing", "no":0}, {"city":"shenzhen", "no":0}]}.
- Jika value bertipe STRING, apit value tersebut dengan tanda kutip ganda ("").
  Catatan
  Jika value bertipe STRING dan mengandung tanda kutip ganda (""), Anda harus menambahkan karakter escape sebelum tanda kutip ganda tersebut untuk menunjukkan bahwa tanda kutip tersebut merupakan bagian dari value. Sebagai contoh, jika value dalam {"addr":"xyz"ab"c"} adalah "xyz"ab"c", Anda harus melakukan escape menjadi "xyz\"ab\"c". Saat menulis data, garis miring terbalik (\) juga harus di-escape. Oleh karena itu, Anda harus menulis {"addr":"xyz\\"ab\\"c"}.
- Jika value bertipe numerik, Anda harus menuliskan datanya secara langsung. Jangan apit value dengan tanda kutip ganda ("").
- Jika value bertipe Boolean, tuliskan true atau false. Jangan gunakan 1 atau 0. Nilai true dan false harus ditulis dalam huruf kecil.
- Jika value bernilai Null, Anda harus menuliskan Null secara langsung.
- Sebuah key dapat memiliki values dengan tipe yang berbeda. Kueri akan mengembalikan hasil sesuai tipe yang ditentukan.
  Sebagai contoh, pernyataan INSERT INTO test_tb1 VALUES ({"id": 1}) memasukkan nilai id sebagai angka 1. Sedangkan pernyataan INSERT INTO test_tb1 VALUES ({"id": "1"}) memasukkan nilai id sebagai string "1".
  Jika Anda menjalankan kueri SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= 1;, hasil yang dikembalikan adalah entri dengan "id": 1. Jika Anda menjalankan kueri SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= '1';, hasil yang dikembalikan adalah entri dengan "id": "1".

Contoh

Buat tabel

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

Tulis data

Saat menulis data, kolom bertipe JSON dituliskan seperti kolom bertipe VARCHAR. Anda harus mengapit string JSON dengan tanda kutip tunggal. Contoh SQL berikut menunjukkan berbagai format data 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"]}');

Kueri data menggunakan fungsi `json_extract`

Sintaks
```
json_extract(json, jsonpath)
```
Deskripsi perintah
Mengembalikan nilai yang ditentukan oleh jsonpath dari JSON.
Parameter
- json: Nama kolom yang bertipe JSON.
- jsonpath: Jalur menuju properti key JSON, dipisahkan dengan titik (.). $ merepresentasikan jalur paling luar.
Untuk informasi selengkapnya tentang fungsi JSON, lihat Fungsi JSON.

Contoh

Kueri dasar

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

Kueri kesetaraan

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';

Kueri rentang

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';

Kueri IS NULL atau 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;

Kueri 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);

kueri 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 Query
```
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.addr[0].city') = 'beijing' and json_extract(vj, '$.addr[1].no') = 0;
```
Catatan
Saat melakukan kueri terhadap data ARRAY, Anda harus menggunakan subskrip data tertentu untuk mengambil nilai. Nomor urut dimulai dari 0 dan bertambah. Iterasi melalui seluruh array tidak didukung.

Unnest array JSON menggunakan fungsi `unnest`

Versi kernel kluster harus 3.2.5 atau lebih baru.

Catatan

Untuk melihat dan memperbarui versi minor, buka bagian Configuration Information pada halaman Cluster Information di Konsol AnalyticDB for MySQL.

Sintaks
```
unnest(json_array)
```
Deskripsi perintah
Menyebarkan array JSON sehingga setiap elemen dalam array menjadi satu baris dalam set hasil.
Parameter
json_array: Data JSON.

Contoh

SELECT * FROM unnest(json '[{"a":"123"},{"a":"456"}]');

Hasil yang dikembalikan

+-------------+
| _col0       |
+-------------+
| {"a":"123"} |
| {"a":"456"} |
+-------------+

Referensi

Indeks JSON