Tipe data JSON - AnalyticDB

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

Catatan penggunaan

AnalyticDB for MySQL mendukung format JSON standar. Saat menulis string JSON ke tabel, pastikan Anda mematuhi format JSON standar.
Nilai default tidak dapat diatur untuk kolom dengan tipe data JSON.

Persyaratan format JSON

Saat menggunakan data JSON di AnalyticDB for MySQL, perhatikan persyaratan berikut terkait keys dan values:

Keys
Setiap key harus diapit dalam tanda kutip ganda (""). Contohnya: "addr" dalam {"addr":"xyz"}.
Values
- Berikut adalah tipe data yang didukung untuk sebuah value: BOOLEAN, NUMBER, VARCHAR, ARRAY, OBJECT, dan NULL.
  Catatan
  - Nilai tipe NUMBER dalam indeks JSON harus berada dalam rentang nilai yang diizinkan untuk tipe DOUBLE.
  - AnalyticDB for MySQL mendukung penulisan array JSON, baik sederhana maupun bersarang. Contohnya: {"hobby":["basketball", "football"]} dan {"addr":[{"city":"beijing", "no":0}, {"city":"shenzhen", "no":0}]}.
- Jika sebuah value bertipe STRING, apitlah value tersebut dalam tanda kutip ganda ("").
  Catatan
  Jika sebuah value bertipe STRING dan mengandung tanda kutip ganda (""), tambahkan karakter escape sebelum tanda kutip ganda untuk menunjukkan bahwa tanda tersebut merupakan bagian dari value. Sebagai contoh, value dalam {"addr":"xyz"ab"c"} adalah "xyz"ab"c". Nilai tersebut harus di-escape menjadi "xyz\"ab\"c". Garis miring terbalik (\) juga di-escape saat data ditulis. Dalam hal ini, tulislah {"addr":"xyz\\"ab\\"c"}.
- Jika sebuah value bertipe NUMBER, langsung tuliskan value tanpa mengapitnya dalam tanda kutip ganda ("").
- Jika sebuah value bertipe BOOLEAN, gunakan true atau false alih-alih 1 atau 0. Pastikan true dan false ditulis dalam huruf kecil.
- Jika sebuah value adalah NULL, tulislah Null ke tabel.
- Sebuah key dapat memiliki values dengan tipe data yang berbeda. Tipe data yang Anda tentukan akan dikembalikan selama query.
  Contohnya, jika pernyataan INSERT INTO test_tb1 VALUES ({"id":1}) dieksekusi, nilai dari id adalah 1. Jika pernyataan INSERT INTO test_tb1 VALUES ({"id":"1"}) dieksekusi, nilai dari id adalah "1".
  Jika pernyataan SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= 1; dieksekusi, "id": 1 dikembalikan. Jika pernyataan SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= '1'; dieksekusi, "id": "1" dikembalikan.

Contoh

Buat tabel

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

Tulis data

Bidang tipe JSON ditulis ke tabel dalam tanda kutip tunggal (''), sama seperti bidang tipe VARCHAR. Pernyataan SQL berikut memberikan contoh format JSON yang berbeda:

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

AnalyticDB for MySQL memungkinkan Anda menggunakan fungsi json_extract() untuk mengkueri data.

Syntax
```
json_extract(json, jsonpath)
```
Description
Fungsi ini mengekstrak satu atau lebih nilai yang ditentukan oleh jsonpath dari string JSON yang valid.
Parameters
- json: nama kolom tipe JSON.
- jsonpath: jalur dari key yang dipisahkan oleh titik (.). $ menentukan jalur terluar.
Untuk informasi lebih lanjut tentang fungsi JSON, lihat fungsi JSON.

Examples

Basic query

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

Equivalent query

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

Range query

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 or IS NOT NULL query

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 query

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 query

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 mengkueri array, Anda dapat menggunakan subskrip data tertentu. Nilai subskrip dimulai dari 0. Anda tidak dapat melakukan iterasi melalui seluruh array.

Referensi

Indeks JSON