JSON

JSON_SEARCH

MySQL

Mencari string value di JSON document dan mengembalikan path ke value tersebut.

Tipe hasil: JSON (path or array of paths)Diperbarui: 7 Jan 2026

Syntax

SQL

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

json_docJSONwajib

JSON document untuk pencarian

one_or_allVARCHARwajib

'one' = return path pertama, 'all' = return semua path

search_strVARCHARwajib

String yang dicari (support wildcard % dan _)

escape_charCHARopsional

Karakter escape (default NULL)

pathVARCHARopsional

Path scope untuk pencarian

SQL

1 SELECT JSON_SEARCH('{"name": "John", "city": "New York"}', 'one', 'John') AS path;

Mencari 'John' dan mendapatkan path-nya.

Hasil

path: "$.name"

SQL

1 SELECT JSON_SEARCH('{"items": ["apple", "apricot", "banana"]}', 'all', 'ap%') AS paths;

Mencari semua value yang dimulai dengan 'ap'.

Hasil

paths: ["$.items[0]", "$.items[1]"]

SQL

1 SELECT id, JSON_SEARCH(data, 'one', '%@gmail.com') AS gmail_path
2 FROM users
3 WHERE JSON_SEARCH(data, 'one', '%@gmail.com') IS NOT NULL;

Mencari user dengan email Gmail.

Hasil

(users with Gmail)

1	SELECT id, JSON_SEARCH(data, 'one', '%@gmail.com') AS gmail_path
2	FROM users
3	WHERE JSON_SEARCH(data, 'one', '%@gmail.com') IS NOT NULL;