JSON_SEARCH
JSON_SEARCH(json_doc, 'one' | 'all', search_str)
Returns the path to the string value that matches the searched string (with % and _ as in LIKE). With 'one' the first one found, with 'all' all of them. If it finds nothing, it returns NULL.
Parameters
- json_doc – the searched document.
- 'one' / 'all' – the first or all occurrences.
- search_str – the string; it may contain % and _.
Examples
CREATE TABLE products_json (id INT PRIMARY KEY, params JSON);
INSERT INTO products_json VALUES
(1, '{"weight": 1000, "origin": "Brazil", "tags": ["beans", "sale"]}'),
(2, '{"weight": 100, "origin": "China", "tags": ["loose leaf"]}'),
(5, '{"weight": 850, "material": "ceramic", "tags": []}');
SELECT id, JSON_SEARCH(params, 'one', 'sale') AS path, JSON_SEARCH(params, 'one', 'Br%') AS starts_with_br
FROM products_json
ORDER BY id;
+----+-------------+----------------+ | id | path | starts_with_br | +----+-------------+----------------+ | 1 | "$.tags[1]" | "$.origin" | | 2 | NULL | NULL | | 5 | NULL | NULL | +----+-------------+----------------+
Things to watch out for
- It searches only in string values; it does not find numbers.
- The result is a path in quotes (such as "$.tags[1]"), usable in other functions after JSON_UNQUOTE().