AnalyticDB：Fungsi JSON

• JSON_ARRAY_CONTAINS: memeriksa apakah array JSON berisi nilai yang ditentukan oleh parameter value.

• JSON_ARRAY_LENGTH: mengembalikan panjang array JSON.

• JSON_CONTAINS (Versi 3.1.5.0 atau lebih baru): memeriksa apakah jalur yang ditentukan berisi nilai dari candidate. Jika jalur tidak ditentukan, fungsi ini memeriksa apakah target berisi nilai yang ditentukan oleh candidate.

• JSON_CONTAINS_PATH (Versi 3.1.5.0 atau lebih baru): memeriksa apakah dokumen JSON berisi satu atau semua jalur yang ditentukan.

• JSON_EXTRACT: mengembalikan nilai yang ditentukan oleh parameter json_path dari dokumen JSON.

• JSON_KEYS: mengembalikan semua kunci objek JSON pada jalur yang ditentukan oleh json_path. Jika json_path tidak ditentukan, fungsi ini mengembalikan semua kunci di jalur root (json_path='$').

• JSON_OVERLAPS (Versi 3.1.10.6 atau lebih baru): memeriksa apakah dokumen JSON berisi salah satu elemen kandidat yang ditentukan, seperti candidate1, candidate2, atau candidate3.

• JSON_REMOVE (Versi 3.1.10.0 atau lebih baru): menghapus elemen yang ditentukan oleh json_path dari dokumen json dan mengembalikan string baru. Anda dapat menentukan beberapa jalur dalam sebuah array untuk menghapus beberapa elemen.

• JSON_SIZE: mengembalikan ukuran objek atau array JSON yang ditentukan oleh parameter json_path.

• JSON_SET (Versi 3.2.2.8 dan lebih baru): menyisipkan atau memperbarui data dalam dokumen json pada json_path yang ditentukan dan mengembalikan dokumen json yang diperbarui.

• JSON_UNQUOTE (Versi 3.1.5.0 atau lebih baru): menghapus tanda kutip ganda dari json_value, menghilangkan karakter escape tertentu dalam json_value, dan mengembalikan hasilnya.

JSON_ARRAY_CONTAINS

json_array_contains(json, value)

• Deskripsi: Memeriksa apakah array JSON berisi nilai yang ditentukan.

• Tipe nilai masukan: Parameter value dapat berupa tipe numerik, string, atau BOOLEAN.

• Tipe nilai kembali: BOOLEAN.

• Contoh:

  • Memeriksa apakah array JSON [1, 2, 3] berisi elemen dengan nilai 2. Pernyataannya adalah sebagai berikut:

    SELECT json_array_contains('[1, 2, 3]', 2);

    Hasil berikut dikembalikan:

    +-------------------------------------+
    | json_array_contains('[1, 2, 3]', 2) |
    +-------------------------------------+
    |                1                    |
    +-------------------------------------+

JSON_ARRAY_LENGTH

json_array_length(json)

• Deskripsi: Mengembalikan panjang array JSON.

• Tipe nilai masukan: STRING atau JSON.

• Tipe nilai kembali: BIGINT.

• Contoh:

  • Mengembalikan panjang array JSON [1, 2, 3]. Pernyataannya adalah sebagai berikut:

    SELECT json_array_length('[1, 2, 3]');

    Hasil berikut dikembalikan:

    +--------------------------------+
    | json_array_length('[1, 2, 3]') |
    +--------------------------------+
    |                 3              |
    +--------------------------------+

JSON_CONTAINS

Fungsi JSON_CONTAINS memeriksa apakah dokumen JSON berisi konten tertentu. Anda dapat menggunakan indeks array JSON dalam kueri untuk menghindari pemindaian tabel penuh atau penguraian seluruh dokumen JSON, sehingga meningkatkan kinerja kueri.

json_contains(target, candidate[, json_path])

json_contains(json_path, cast('[candidate1,candidate2,candidate3]' as json)) 

JSON_CONTAINS_PATH

json_contains_path(json, one_or_all, json_path[, json_path,...])

• Deskripsi: Memeriksa apakah dokumen JSON berisi nilai pada jalur yang ditentukan.

  • Jika one_or_all diatur ke 'one', fungsi ini mengembalikan 1 jika dokumen JSON berisi setidaknya satu dari jalur yang ditentukan. Jika tidak, fungsi ini mengembalikan 0.

  • Jika one_or_all diatur ke 'all', fungsi ini mengembalikan 1 jika dokumen JSON berisi semua jalur yang ditentukan. Jika tidak, fungsi ini mengembalikan 0.

• Tipe nilai masukan: Parameter json bertipe JSON. Parameter one_or_all bertipe VARCHAR. Nilai yang valid adalah 'one' atau 'all', yang tidak peka huruf besar/kecil. Parameter json_path adalah jalur.

• Tipe nilai kembali: BOOLEAN.

• Contoh:

  • Memeriksa apakah dokumen JSON berisi setidaknya satu dari jalur $.a dan $.e. Pernyataannya adalah sebagai berikut:

    SELECT json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e') AS RESULT;

    Hasil berikut dikembalikan:

    +--------+
    | result |
    +--------+
    |      1 |
    +--------+

  • Memeriksa apakah dokumen JSON berisi kedua jalur $.a dan $.e. Pernyataannya adalah sebagai berikut:

    SELECT json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e') AS RESULT;

    Hasil berikut dikembalikan:

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+

JSON_EXTRACT

json_extract(json, json_path)

• Deskripsi: Mengembalikan nilai yang ditentukan oleh json_path dari dokumen JSON. Jika kunci dalam json berisi karakter khusus, seperti $ atau ., format json_path harus berupa '$["Key"]'.

  Misalnya, jika kuncinya adalah $data, tentukan `json_path` sebagai '$["$data"]'.

• Tipe nilai masukan: STRING atau JSON.

• Tipe nilai kembali: JSON.

• Contoh:

  • Mengembalikan nilai pada jalur `$[0]` dari array [10, 20, [30, 40]]. Pernyataannya adalah sebagai berikut:

    SELECT json_extract('[10, 20, [30, 40]]', '$[0]');

    Hasil berikut dikembalikan:

    +-------------------------------------------+
    | json_extract('[10, 20, [30, 40]]', '$[0]') |
    +-------------------------------------------+
    |                     10                    |
    +-------------------------------------------+

  • Mengembalikan nilai dari jalur `$date` dari {"id":"1","$date":"12345"}. Pernyataannya adalah sebagai berikut:

    SELECT JSON_EXTRACT('{"id":"1","$date":"12345"}', '$["$date"]');

    Hasil berikut dikembalikan:

    +---------------------------------------------------------+
    |JSON_EXTRACT('{"id":"1","$date":"12345"}', '$["$date"]') |
    +---------------------------------------------------------+
    |                       "12345"                           |
    +---------------------------------------------------------+

JSON_KEYS

json_keys(json[, json_path])

• Deskripsi

  • Jika json_path ditentukan, fungsi ini mengembalikan semua kunci dari objek JSON pada jalur tersebut.

  • Jika json_path tidak ditentukan, fungsi ini mengembalikan semua kunci dari objek root, yang setara dengan json_path='$'.

• Tipe nilai masukan: Hanya parameter bertipe JSON yang didukung.

  Anda dapat membuat data JSON dengan cara berikut:

  • Gunakan data JSON secara langsung. Contoh: json '{"a": 1, "b": {"c": 30}}'.

  • Gunakan fungsi CAST untuk secara eksplisit mengonversi string menjadi data JSON. Contoh: CAST('{"a": 1, "b": {"c": 30}}' AS json).

• Tipe nilai kembali: Array JSON.

• Contoh:

  • Mengembalikan semua kunci pada jalur $.b. Pernyataannya adalah sebagai berikut:

    SELECT json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json),'$.b');

    Hasil berikut dikembalikan:

    +-----------------------------------------------------------+
    | json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json),'$.b') |
    +-----------------------------------------------------------+
    |                           ["c"]                           |
    +-----------------------------------------------------------+

  • Mengembalikan semua kunci pada jalur root. Pernyataannya adalah sebagai berikut:

    SELECT JSON_KEYS(json '{"a": 1, "b": {"c": 30}}');

    Hasil berikut dikembalikan:

    +--------------------------------------------+
    | JSON_KEYS(json '{"a": 1, "b": {"c": 30}}') |
    +--------------------------------------------+
    |             ["a","b"]                      |
    +--------------------------------------------+

JSON_OVERLAPS

json_overlaps(json, cast('[candidate1,candidate2,candidate]' as json)) 

• Deskripsi: Memeriksa apakah dokumen JSON berisi salah satu elemen yang ditentukan, seperti candidate1, candidate2, dan candidate3.

• Tipe nilai masukan: Parameter candidate1, candidate2, candidate3, ... menerima nilai numerik atau string. Semua nilai harus memiliki tipe data yang sama.

• Tipe nilai kembali: VARCHAR.

• Contoh:

  • Mengembalikan data yang berisi CP-018673 dari kolom JSON yang ditentukan vj.

    SELECT * FROM json_test WHERE json_overlaps(vj, cast('["CP-018673"]' AS json));

    Hasil berikut dikembalikan:

    +-----+----------------------------------------------------------------------------+
    |  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"]  |
    +-----+----------------------------------------------------------------------------+

  • Mengembalikan data yang berisi salah satu elemen 1, 2, atau 3 dari kolom JSON yang ditentukan vj.

    SELECT * FROM json_test WHERE json_overlaps(vj, cast('[1,2,3]' AS json))

    Hasil berikut dikembalikan:

    +-----+-------------------------------------+
    |  id |                 vj                  |
    +-----+-------------------------------------+
    |  1  | [1,2,3]                             |
    +-----+-------------------------------------+
    |  2  | ["CP-018673", 1, false]             |
    +-----+-------------------------------------+
    |  3  | ["CP-018673", 1, false, {"a": 1}]   |
    +-----+-------------------------------------+

JSON_REMOVE

json_remove(json,json_path)
json_remove(json,array[json_path,json_path,...])

• Deskripsi: Menghapus satu atau beberapa elemen yang ditentukan oleh json_path dari dokumen json dan mengembalikan string baru. Anda dapat menentukan beberapa jalur dalam sebuah array, seperti `array[json_path, json_path, ...]` untuk menghapus beberapa elemen.

• Tipe nilai masukan: Parameter json adalah string VARCHAR dalam format JSON. Parameter json_path adalah string VARCHAR dalam format JSON.

• Tipe nilai kembali: VARCHAR.

• Contoh

  • Menghapus bagian pada jalur $.glossary.GlossDiv dan mengembalikan string yang dimodifikasi. Pernyataannya adalah sebagai berikut:

      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;

    Hasil berikut dikembalikan:

    {"glossary":{"title":"example glossary"}}

  • Menghapus bagian pada jalur $.glossary.title dan $.glossary.GlossDiv.title dan mengembalikan string yang dimodifikasi. Pernyataannya adalah sebagai berikut:

    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;

    Hasil berikut dikembalikan:

    {"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)

• Deskripsi: Mengembalikan ukuran objek JSON atau array JSON yang ditentukan oleh json_path.

  Catatan

  Fungsi ini mengembalikan 0 jika json_path tidak menunjuk ke objek JSON atau array JSON.

• Tipe nilai masukan: STRING atau JSON.

• Tipe nilai kembali: BIGINT.

• Contoh:

  • json_path menunjuk ke objek JSON. Pernyataannya adalah sebagai berikut:

    SELECT json_size('{"x":{"a":1, "b": 2}}', '$.x') as result;

    Hasil berikut dikembalikan:

    +--------+
    | result |
    +--------+
    |      2 |
    +--------+

  • json_path tidak menunjuk ke objek JSON atau array JSON. Pernyataannya adalah sebagai berikut:

    SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a') as result;

    Hasil berikut dikembalikan:

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+

JSON_SET

json_set(json, json_path, value[, json_path, value] ...)

• Deskripsi: Menyisipkan atau memperbarui data dalam dokumen json pada json_path yang ditentukan dan mengembalikan dokumen json yang diperbarui.

  • Jika parameter json atau json_path bernilai null, fungsi ini mengembalikan null.

  • Pengecualian dilemparkan jika json bukan dokumen JSON yang valid, atau jika json_path mana pun bukan ekspresi jalur yang valid.

  • Jika json_path yang ditentukan ada, nilainya ditimpa oleh value baru.

  • Jika json_path yang ditentukan tidak ada dalam json:

    • Jika json_path menunjuk ke objek JSON, value ditambahkan sebagai elemen baru ke lokasi yang ditentukan oleh json_path.

    • Jika json_path menunjuk ke array JSON, fungsi ini memeriksa apakah ada slot kosong sebelum posisi yang ditentukan oleh json_path. Jika ada slot kosong, slot tersebut diisi dengan nilai `null` sebelum value disisipkan. Jika tidak, value disisipkan secara langsung.

    • Pengecualian dilemparkan dalam kasus lainnya.

• Tipe nilai masukan:

  • json: VARCHAR atau JSON.

  • json_path: VARCHAR.

  • value: BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, VARCHAR, VARBINARY, DATE, DATETIME, TIMESTAMP, atau TIME.

• Tipe nilai kembali: JSON.

• Contoh:

  • Menyisipkan data ke dokumen json di mana json_path bernilai null.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', null, '10');

    Hasil:

    +------------------------------------------------+
    | JSON_SET('{ "a": 1, "b": [2, 3]}', NULL, '10') |
    +------------------------------------------------+
    | null                                           |
    +------------------------------------------------+

  • Menyisipkan data ke dokumen json di mana json_path bukan ekspresi jalur yang valid.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.b.c', '10');

    Hasil:

    Failed to execute json_set() for json_path: $.b.c

  • Menyisipkan data ke dokumen json. Jalur json_path1 ada, tetapi json_path2 tidak ada dan menunjuk ke objek JSON.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');

    Hasil:

    +-----------------------------------------------------------------------+
    | JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
    +-----------------------------------------------------------------------+
    | {"a":10,"b":[2,3],"c":"[true, false]"}                                |
    +-----------------------------------------------------------------------+

  • Menyisipkan data ke dokumen json. json_path yang ditentukan tidak ada dan menunjuk ke array JSON.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}',  '$.b[4]', '[true, false]');

    Hasil:

    +----------------------------------------------------------------+
    | 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)

• Deskripsi: Menghilangkan tanda kutip dari json_value dengan menghapus tanda kutip ganda luar dan menghilangkan karakter escape tertentu di dalam nilai tersebut, lalu mengembalikan hasilnya.

  AnalyticDB for MySQL tidak memvalidasi parameter json_value. Parameter json_value diproses berdasarkan logika di atas, terlepas dari apakah parameter tersebut sesuai dengan sintaks JSON atau tidak.

  Tabel berikut menjelaskan karakter escape yang didukung.

  Sebelum escaping	Setelah escaping
  \"	Tanda kutip ganda (").
  \b	Backspace.
  \f	Form feed.
  \n	Line feed.
  \r	Carriage return.
  \t	Tab.
  \\	Backslash (\).
  \uXXXX	Representasi karakter UTF-8.

• Tipe nilai masukan: VARCHAR.

• Tipe nilai kembali: VARCHAR.

• Contoh:

  • Mengembalikan string tanpa tanda kutip abc. Pernyataannya adalah sebagai berikut:

    SELECT json_unquote('"abc"');

    Hasil berikut dikembalikan:

    +-----------------------+
    | json_unquote('"abc"') |
    +-----------------------+
    |          abc          |
    +-----------------------+

  • Mengembalikan string yang tidak dikutip dan diurai. Pernyataannya adalah sebagai berikut:

    SELECT json_unquote('"\\t\\u0032"');

    Hasil berikut dikembalikan:

    +------------------------------+
    | json_unquote('"\\t\\u0032"') |
    +------------------------------+
    |               2              |
    +------------------------------+

Lampiran: Sintaks Jalur JSON

Penggunaan

• Gunakan $.keyName[.keyName]... untuk mengakses kunci tertentu dalam objek JSON.

• Gunakan $[nonNegativeInteger] untuk mengakses elemen ke-n dari array JSON, di mana n adalah bilangan bulat non-negatif.

• Gunakan $.keyName[.keyName]...[nonNegativeInteger] untuk mengakses elemen ke-n dari array JSON bersarang dalam objek JSON, di mana n adalah bilangan bulat non-negatif.

Catatan

Sintaks Jalur JSON di AnalyticDB for MySQL tidak mendukung karakter wildcard * dan **. Misalnya, ekspresi '$.*', '$.hobbies[*]', '$.address.**', dan '$.hobbies.**' tidak didukung.

Contoh

Asumsikan Anda memiliki data JSON berikut.

{
    "name": "Alice",
    "age": 25,
    "address": {
        "city": "Hangzhou",
        "zip": "10001"
    },
    "hobbies":["reading", "swimming", "cycling"]
}

FAQ

Referensi

• JSON: pelajari tentang tipe JSON.

• Indeks JSON: pelajari cara membuat indeks untuk objek JSON atau array JSON.