Armazenando e Consultando JSON no SQLite: Exemplos e Melhores Práticas

JSON, ou JavaScript Object Notation, é um formato para armazenar e trocar dados. É um formato leve, baseado em texto e legível por humanos que é fácil para máquinas analisar e gerar. No SQLite, JSON pode ser armazenado e consultado de algumas maneiras diferentes.

Usando a Extensão JSON1

Uma maneira de armazenar JSON no SQLite é usar a extensão JSON1, que fornece funções para codificar e decodificar JSON. Para usar a extensão JSON1, você primeiro precisa habilitá-la executando o seguinte comando:

SELECT load_extension('/path/to/json1/extension');

Uma vez que a extensão está habilitada, você pode criar uma tabela que tenha uma coluna do tipo JSON usando a seguinte declaração SQL:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data JSON
);

Inserindo Dados JSON

Para inserir um valor JSON nesta tabela, você pode usar a função json_encode fornecida pela extensão JSON1, assim:

INSERT INTO users (id, data) VALUES (1, json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
}));

Consultando Dados JSON

Para consultar esta tabela, você pode usar a função json_extract, que permite extrair valores da coluna JSON especificando um caminho JSON. Por exemplo, para obter o endereço de email do usuário com id 1, você pode usar a seguinte consulta:

SELECT json_extract(data, '$.email') AS email
FROM users
WHERE id = 1;

Armazenar JSON em BLOB

Outra maneira de armazenar e consultar JSON no SQLite é usar o tipo BLOB. Neste caso, você pode criar uma tabela com uma coluna BLOB assim:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data BLOB
);

Inserindo JSON em uma Coluna BLOB

Para inserir um valor JSON nesta tabela, você pode usar a função json_encode para codificar o valor como uma string, e então usar a função hex para convertê-lo para um BLOB, assim:

INSERT INTO users (id, data) VALUES (1, hex(json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
})));

Consultando JSON em uma Coluna BLOB

Para consultar esta tabela, você pode usar a função JSON_EXTRACT fornecida pela extensão JSON1, mas você precisa converter o valor BLOB de volta para uma string usando a função unhex, assim:

SELECT json_extract(unhex(data), '$.email') AS email
FROM users
WHERE id = 1;

Resumo

Em conclusão, SQLite oferece duas maneiras de armazenar e consultar valores JSON: usando a extensão JSON1 e usando o tipo BLOB. Ambas as abordagens têm suas próprias vantagens e desvantagens, e qual você escolher dependerá de suas necessidades específicas.

Se você precisa converter dados JSON para declarações SQL INSERT, confira nossa ferramenta gratuita Conversor JSON para SQL.