Resumo : neste tutorial, você aprenderá como usar a JSON_EXTRACT()função MySQL para recuperar dados de um documento JSON.
Introdução à função JSON_EXTRACT() do MySQL
A JSON_EXTRACT()função permite extrair dados de um documento JSON com base nos caminhos JSON especificados .
Aqui está a sintaxe da JSON_EXTRACT()função:
JSON_EXTRACT(json_doc, path[, path] …)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Nesta sintaxe:
json_doc: este é o documento JSON do qual você deseja extrair dados.path: esta é a expressão de caminho que especifica o local dos dados no documento JSON que você deseja extrair.
A JSON_EXTRACT()função retorna os valores que correspondem aos caminhos. Se vários caminhos corresponderem, ele retornará uma matriz que agrupa os valores correspondentes.
Se a expressão de caminho não localizar nenhum elemento em json_doc, a função retornará NULL. Se qualquer argumento argument for NULL, a função também retornará NULL.
Exemplos de funções JSON_EXTRACT() do MySQL
Vejamos alguns exemplos de uso da JSON_EXTRACT()função.
1) Extraindo valores únicos
O exemplo a seguir usa a JSON_EXTRACT()função para extrair o valor associado à chave de nome do documento JSON:
SELECT
JSON_EXTRACT(
'{"name": "John", "age": 25}',
'$.name'
) name;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+--------+
| name |
+--------+
| "John" |
+--------+
1 row in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )2) Extraindo elementos do array
O exemplo a seguir usa a JSON_EXTRACT()função para extrair um elemento da matriz no índice 1:
SELECT
JSON_EXTRACT('[10, 20, 30]', '$[1]') result;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )O $[1] especifica o elemento no índice 1 que é o segundo elemento. Portanto, a JSON_EXTRACT()função retorna 20:
+--------------------------------------+
| JSON_EXTRACT('[10, 20, 30]', '$[1]') |
+--------------------------------------+
| 20 |
+--------------------------------------+
1 row in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )3) Extraindo valores aninhados
O exemplo a seguir usa a JSON_EXTRACT()função para extrair valores aninhados especificando vários níveis de chaves no caminho:
SELECT
JSON_EXTRACT(
'{"person": {"name": "Jane", "age": 25}}',
'$.person.name'
) name;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+---------+
| name |
+---------+
| "Jane" |
+---------+
1 row in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )4) Lidando com caminhos inexistentes
O exemplo a seguir usa a JSON_EXTRACT()função para extrair dados de um caminho que não localiza nenhum elemento no documento JSON:
SELECT
JSON_EXTRACT(
'{"name": "John", "age": 28}', '$.address.city'
) result;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Ele retorna NULL:
+--------+
| result |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )5) Usando a função MySQL JSON_EXTRACT() com dados da tabela
Usaremos a productstabela do banco de dados de exemplo para a demonstração:
Primeiro, crie uma nova tabela chamada product_pricescom duas colunas ide data:
CREATE TABLE product_prices(
id INT AUTO_INCREMENT PRIMARY KEY,
data JSON
);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, crie documentos JSON a partir dos dados da productstabela e insira -os na product_pricestabela:
INSERT INTO product_prices(data)
SELECT
JSON_OBJECT(
'productName', productName,
'buyPrice', buyPrice,
'msrp', msrp
)
FROM products;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Terceiro, selecione os dados da product_pricestabela:
SELECT * FROM product_prices;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída parcial:
+-----+--------------------------------------------------------------------------------------------------+
| id | data |
+-----+--------------------------------------------------------------------------------------------------+
| 1 | {"msrp": 95.70, "buyPrice": 48.81, "productName": "1969 Harley Davidson Ultimate Chopper"} |
| 2 | {"msrp": 214.30, "buyPrice": 98.58, "productName": "1952 Alpine Renault 1300"} |
| 3 | {"msrp": 118.94, "buyPrice": 68.99, "productName": "1996 Moto Guzzi 1100i"} |
...Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Por fim, extraia o nome do produto, o preço de compra e o MSRP dos documentos JSON na datacoluna da product_pricestabela:
SELECT
JSON_EXTRACT(
data,
'$.productName',
'$.buyPrice',
'$.msrp'
) data
FROM
product_prices;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+---------------------------------------------------------------+
| data |
+---------------------------------------------------------------+
| ["1969 Harley Davidson Ultimate Chopper", 48.81, 95.70] |
| ["1952 Alpine Renault 1300", 98.58, 214.30] |
| ["1996 Moto Guzzi 1100i", 68.99, 118.94] |
...Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Como usamos vários caminhos, a função retorna os valores correspondentes agrupados em um array.
O -> operador
O ->operador é um atalho para a JSON_EXTRACT()função quando usado com dois argumentos: um identificador de coluna à esquerda e um caminho JSON à direita:
column_name -> pathLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Por exemplo, podemos extrair o nome do produto da coluna de dados da product_pricestabela da seguinte forma:
SELECT
data -> '$.productName'
FROM
product_prices;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+-----------------------------------------------+
| data -> '$.productName' |
+-----------------------------------------------+
| "1969 Harley Davidson Ultimate Chopper" |
| "1952 Alpine Renault 1300" |
| "1996 Moto Guzzi 1100i" |
...Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Para remover as aspas do resultado, você usa a JSON_UNQUOTE()função:
SELECT
JSON_UNQUOTE(data -> '$.productName') productName
FROM
product_prices;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+---------------------------------------------+
| productName |
+---------------------------------------------+
| 1969 Harley Davidson Ultimate Chopper |
| 1952 Alpine Renault 1300 |
| 1996 Moto Guzzi 1100i |
...Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )O ->> operador
O ->>operador é equivalente a uma das seguintes chamadas de função:
JSON_UNQUOTE(column->path)
JSON_UNQUOTE(JSON_EXTRACT(column, path))Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Por exemplo:
SELECT
data ->> '$.productName' productName
FROM
product_prices;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+---------------------------------------------+
| productName |
+---------------------------------------------+
| 1969 Harley Davidson Ultimate Chopper |
| 1952 Alpine Renault 1300 |
| 1996 Moto Guzzi 1100i |
...Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Resumo
- Use a
JSON_EXTRACT()função para extrair valores de um documento JSON e retornar os valores que correspondem aos caminhos especificados. - Use
column -> patho operador sempre que usar aJSON_EXTRACT()função com dois argumentos. - Use
column ->> patho operador se desejar extrair e tirar aspas dos valores extraídos.
