Trabalhando com json no MySQL

Estou trabalhando num projeto em que gostaria de utilizar um banco de dados NoSql de documentos, mas infelizmente só tinha disponível o MySql. Para manter a arquitetura, resolvi utilizar a coluna do tipo Json para armazenar os dados.

Criando a tabela.

Vamos criar uma tabela que armazena um json com as informações do cnpj das empresas .

CREATE TABLE empresas (
id INT NOT NULL AUTO_INCREMENT,
cnpj json,
   PRIMARY KEY ( id )
);

Inserindo registros na tabela

Agora que já temos nossa tabela criada, vamos inserir os dados.

Há duas formas de inserir registros numa coluna json, sendo array com JSON_ARRAY() e objeto com o JSON_OBJECT().

Trabalharemos aqui nos exemplos com objetos.

JSON_OBJECT([key, val[, key, val] …])
Exemplo: JSON_OBJECT(‘chave’, ‘valor’, ‘outra_chave’, ‘outro valor’)

INSERT INTO empresas (cnpj, situacao) values (
JSON_OBJECT(
'numero', '99999999999999', 
'razao_social', 'Teste de Empresa', 
'nome_fantasia', 'Teste', 
'data_abertura', '2022-01-01')
);

Recuperando registros

Obtendo o objeto json completo.

SELECT
	cnpj
FROM
	empresas
WHERE
	id = 1;

{"numero": "99999999999999", "razao_social": "Teste de Empresa", "data_abertura": "2022-01-01", "nome_fantasia": "Teste"}

Obtendo o objeto json completo formatado.

SELECT
	JSON_PRETTY(cnpj)
FROM
	empresas
WHERE
	id = 1;

{
  "numero": "99999999999999",
  "razao_social": "Teste de Empresa",
  "data_abertura": "2022-01-01",
  "nome_fantasia": "Teste"
}

Obtendo um array com a lista de chaves do objeto.

SELECT
	JSON_KEYS(cnpj)
FROM
	empresas
WHERE
	id = 1;

["numero", "razao_social", "data_abertura", "nome_fantasia"]

Obtendo um array com a lista de valores do objeto.

SELECT 
	JSON_EXTRACT(cnpj,
	'$.*')
FROM
	empresas
WHERE
	id = 1;

["99999999999999", "Teste de Empresa", "2022-01-01", "Teste"]

Obtendo o valor para uma chave do objeto.

SELECT 
	JSON_EXTRACT(cnpj,
	'$.*')
FROM
	empresas
WHERE
	id = 1;

"Teste de Empresa"

Buscando um registro utilizando filtrando pelo valor de uma chave.

SELECT
	id
FROM
	empresas
WHERE 
	JSON_EXTRACT(cnpj,
	'$.razao_social') LIKE '%Teste%';

1

Editando registros

Inserindo uma nova chave / valor no registro.

SELECT
	id
FROM
	empresas
WHERE 
	JSON_EXTRACT(cnpj,
	'$.razao_social') LIKE '%Teste%';

{
  "numero": "99999999999999",
  "novo_valor": "Novo Valor",
  "razao_social": "Teste de Empresa",
  "data_abertura": "2022-01-01",
  "nome_fantasia": "Teste"
}

Removendo uma chave do registro.

UPDATE
	empresas
SET
	cnpj = JSON_REMOVE(cnpj,
	'$.novo_valor')
WHERE
	id = 1;

{
  "numero": "99999999999999",
  "razao_social": "Teste de Empresa",
  "data_abertura": "2022-01-01",
  "nome_fantasia": "Teste"
}

Alterando o valor de uma chave no registro.

UPDATE
	empresas
SET
	cnpj = JSON_REPLACE(cnpj,
	'$.numero',
	'88888888888888')
WHERE
	id = 1;

{
  "numero": "88888888888888",
  "razao_social": "Teste de Empresa",
  "data_abertura": "2022-01-01",
  "nome_fantasia": "Teste"
}

Conclusão

Essas são as principais funções para trabalhar com json no MySQL e também no MariaDB.

Alguma dúvida? Posta ai nos comentários.