Resumo : neste tutorial, você aprenderá como usar a JSON_SEARCH()
função MySQL para encontrar um caminho para uma determinada string em um documento JSON.
Introdução à função JSON_SEARCH() do MySQL
A JSON_SEARCH()
função é usada para encontrar um caminho para uma determinada string em um documento JSON.
Aqui está a sintaxe da JSON_SEARCH()
função:
JSON_SEARCH(json_document, one_or_all, search_str [, escape_char])
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Nesta sintaxe:
json_document
: este é o documento JSON no qual você deseja pesquisar.one_or_all
: o argumento que especifica se a primeira ocorrência ou todas as ocorrências devem ser localizadas. Seone_or_all
for ‘um’, a função interrompe a pesquisa após a primeira correspondência e retorna uma string de caminho. Se oone_or_all
argumento for ‘todos’, a função retornará um array que contém todos os caminhos correspondentes.search_str
: esta é a string ou padrão que você deseja pesquisar no documento JSON. Com osearch_str
argumento, você pode usar os caracteres curinga % e _. O % corresponde a qualquer número de caracteres e _ corresponde a exatamente um caractere.escape_char
: para usar os caracteres curinga (% e _) como a string literal dentro desearch_str
, você os precede com um caractere de escape (escape_char
). O padrãoescape_char
é \ se você omitir ou se forNULL
. Caso contrário,escape_char
deve ser uma constante vazia ou um caractere.- caminho: determina como interpretar a string de pesquisa, ‘um’ como caminho, ‘todos’ como padrão curinga.
A JSON_SEARCH()
função retorna NULL
se algum dos argumentos json_doc
, search_str
ou path_mode
for NULL
.
A função emite um erro caso uma das seguintes condições:
- O
json_doc
argumento não é um documento JSON válido. - Qualquer
path
argumento não é uma expressão de caminho válida. - O valor do
one_or_all
argumento não é ‘um’ ou ‘todos’. escape_char
não é uma expressão constante.
Exemplos de funções JSON_SEARCH() do MySQL
Vejamos alguns exemplos de uso da JSON_SEARCH()
função.
1) Procurando pela primeira ocorrência
O exemplo a seguir usa a JSON_SEARCH()
função para procurar a primeira ocorrência de um caminho que corresponda à string "John"
em um documento JSON:
SELECT
JSON_SEARCH(
'{"name": "John", "age": 25, "username": "John"}',
'one',
'John'
) path;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Ele retorna a expressão de caminho “$.name”:
+----------+
| path |
+----------+
| "$.name" |
+----------+
1 row in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
2) Buscando todas as ocorrências
O exemplo a seguir usa a JSON_SEARCH()
função para procurar todas as ocorrências de um caminho que corresponda à string "John"
em um documento JSON:
SELECT
JSON_SEARCH(
'{"name": "John", "age": 25, "username": "John"}',
'all',
'John'
) path;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+--------------------------+
| path |
+--------------------------+
| ["$.name", "$.username"] |
+--------------------------+
1 row in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
3) Pesquisando com o curinga %
O exemplo a seguir usa a JSON_SEARCH()
função para procurar todas as ocorrências de um caminho que corresponda à string "john"
e seja seguido por qualquer número de caracteres em um documento JSON:
SELECT
JSON_SEARCH(
'{"name": "john", "age": 25, "email": "[email protected]"}',
'all',
'john%'
) path;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+-----------------------+
| path |
+-----------------------+
| ["$.name", "$.email"] |
+-----------------------+
1 row in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
4) Pesquisando com o curinga _
O exemplo a seguir usa a JSON_SEARCH()
função para procurar todas as ocorrências de um caminho que corresponda a qualquer caractere seguido pela string "oe"
em um documento JSON:
SELECT
JSON_SEARCH(
'[{"name": "Joe", "age": 25, "salary":"100_000"},
{"name": "Doe", "age": 27, "salary":"120_000"}]',
'all',
'_oe'
) path;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----------------------------+
| path |
+----------------------------+
| ["$[0].name", "$[1].name"] |
+----------------------------+
1 row in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
5) Pesquisando usando um caractere de escape
Suponha que temos o seguinte documento JSON:
{"code": "S_100", "name":"Sx100"}
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para procurar o caminho que corresponde à string "S_100"
, você pode criar a seguinte instrução:
SELECT
JSON_SEARCH(
'{"code": "S_100", "name":"Sx100"}',
"all", "S_100"
) path;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Mas a string de pesquisa "S_100"
corresponde a ambas as strings "S_100"
e "Sx100"
porque o _
caractere na string de pesquisa S_100
é um caractere curinga que corresponde ao caractere literal _
in "S_100"
e x
in "Sx100"
:
+----------------------+
| path |
+----------------------+
| ["$.code", "$.name"] |
+----------------------+
1 row in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para tratar o caractere _
como uma string literal na string de pesquisa "S_100"
, você precisa escapá-lo precedendo-o com o caractere de escape padrão ( \
), assim:
SELECT
JSON_SEARCH(
'{"code": "S_100", "name":"Sx100"}',
"all", "S\_100"
) path;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----------+
| path |
+----------+
| "$.code" |
+----------+
1 row in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Funciona como esperado agora.
Se não quiser usar o caractere de escape padrão, você pode especificar um usando o escape_char
argumento. Por exemplo:
SELECT
JSON_SEARCH(
'{"code": "S_100", "name":"Sx100"}',
"all", "S$_100", '$'
) path;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----------+
| path |
+----------+
| "$.code" |
+----------+
1 row in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste exemplo, usamos o dólar ( $
) como caractere de escape e precedemos o caractere curinga ( _
) com o caractere de escape.
Resumo
- Use a
JSON_SEARCH()
função para encontrar um caminho para uma determinada string em um documento JSON.