Função MySQL JSON_SEARCH()

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. Se one_or_allfor ‘um’, a função interrompe a pesquisa após a primeira correspondência e retorna uma string de caminho. Se o one_or_allargumento 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 o search_strargumento, 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 de search_str, você os precede com um caractere de escape ( escape_char). O padrão escape_charé \ se você omitir ou se for NULL. 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 NULLse algum dos argumentos json_doc, search_strou path_modefor NULL.

A função emite um erro caso uma das seguintes condições:

• O json_docargumento não é um documento JSON válido.
• Qualquer pathargumento não é uma expressão de caminho válida.
• O valor do one_or_allargumento não é ‘um’ ou ‘todos’.
• escape_charnã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 xin "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_charargumento. 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.