Como usar JSON do SQLite com velocidade total de índice: consultas rápidas com colunas geradas

(dbpro.app)

11 pontos por GN⁺ 2025-12-13 | 2 comentários | Compartilhar no WhatsApp

Apresenta uma abordagem para aproveitar os recursos de JSON do SQLite armazenando o documento JSON original como está e extraindo os campos necessários como colunas geradas virtuais (virtual generated columns) para indexação
Com a função json_extract, é possível tratar dados internos do JSON como colunas e executar consultas com velocidade de índice B-tree
Sempre que surgir um novo padrão de consulta, é possível expandir adicionando colunas e índices sem migração de dados
Essa abordagem garante ao mesmo tempo a flexibilidade de dados sem esquema e o desempenho de um banco de dados relacional
É destacada como um padrão prático que oferece estrutura simples e alto desempenho para desenvolvedores que usam SQLite

Combinação do SQLite com recursos de JSON

O SQLite oferece suporte a funções e operadores JSON, permitindo armazenar e manipular dados JSON diretamente
- É possível guardar o documento JSON inteiro em uma coluna e extrair apenas as informações necessárias em colunas virtuais
- Essa abordagem permite lidar com os dados de forma flexível, sem definir um esquema antecipadamente
A equipe do DB Pro vem usando SQLite intensamente nos últimos meses e validou esse recurso na prática
- Quando configurado corretamente, o SQLite também pode ser usado com estabilidade em ambiente de produção

Colunas geradas virtuais (Generated Columns)

Com json_extract, é possível definir valores específicos dentro do JSON como colunas geradas virtuais
- Essas colunas não armazenam os dados fisicamente; elas são calculadas no momento da consulta e podem ser usadas imediatamente
- Não é necessário processo separado de backfill nem duplicação de dados
Por exemplo, é possível extrair um campo específico de um JSON e tratá-lo como se fosse uma coluna comum

Adição de índices e ganho de desempenho

Ao adicionar índices às colunas virtuais, os dados JSON também podem ser consultados com velocidade de índice B-tree, como colunas normais
- Uma coluna virtual indexada oferece o mesmo nível de desempenho que uma coluna de um banco de dados relacional
Essa abordagem permite buscas rápidas mesmo quando o volume de dados JSON cresce

Inclusão de novos padrões de consulta

Se depois surgir a necessidade de buscar por um novo campo, basta adicionar uma nova coluna virtual e um índice
- Exemplo: extrair o campo user_id e criar um índice sobre ele
- Não é preciso modificar as linhas existentes nem fazer migração dos dados
Assim, é possível obter escalabilidade imediata das consultas sem alterar a estrutura dos dados

Vantagens e significado do padrão

Esse padrão combina a flexibilidade do armazenamento JSON sem esquema com o desempenho de indexação de um banco relacional
- Não é necessário decidir a estratégia de indexação logo na fase inicial de projeto
- É possível otimizar adicionando colunas e índices no momento em que isso se tornar necessário
É apresentado aos desenvolvedores que usam SQLite como uma forma de processamento de dados simples e poderosa
O DB Pro também adianta que publicará mais textos abordando diversos recursos do SQLite

2 comentários

GN⁺ 2025-12-13

Comentários no Hacker News

É possível codificar diretamente documentos JSON como uma árvore B serializada
Assim, dá para consultar campos internos diretamente na velocidade de índice, e como o próprio documento já fica indexado, não é necessário fazer parsing
Esse formato se chama Lite³. É um projeto em que estou trabalhando
Link do GitHub
- Muito legal! Eu gosto de Rkyv, mas ele exige Rust, então acabava sendo pesado demais para projetos pequenos
  Gostei especialmente do fato de o Lite³ oferecer suporte a dados binários com lite3_val_bytes
- Fiquei curioso sobre como o Lite³ difere do JSONB do PostgreSQL
  O JSONB codifica o tamanho dos arrays e os offsets juntos, o que permite equilibrar eficiência de compressão e velocidade
  O Lite³ permite atualizações in-place, mas exige um “vacuum” periódico para evitar que dados sensíveis permaneçam no arquivo
  O JSONB é difícil de atualizar sem recodificação, mas o Lite³ pode ser reorganizado de forma simples só percorrendo a estrutura
  Em compressão, o JSONB provavelmente leva vantagem, mas acho o design do Lite³ uma abordagem muito inteligente
  Também mantenho um compilador ASN.1, então tenho bastante interesse nesse tipo de formato de serialização. O Lite³ me deu novas ideias
- Seria ótimo se existisse uma implementação em Rust
Eu realmente gosto muito do SQLite, mas para análise tenho usado mais o DuckDB
Assim como o SQLite, o DuckDB usa um único arquivo, mas consegue processar datasets grandes de forma extremamente rápida
No meu MacBook M2, ele é muito rápido mesmo lidando com 20 milhões de registros
Por exemplo, dá para ler arquivos JSON diretamente com uma consulta como esta
```
SELECT avg(sale_price), count(DISTINCT customer_id)
FROM '/my-data-lake/sales/2024/*.json';
```
Também é possível carregar colunas do tipo JSON e usar a sintaxe estilo Postgres col->>'$.key'
- Fiquei pensando se essa primeira consulta indexa os arquivos JSON do sistema de arquivos em tempo real
- O DuckDB, junto com a ferramenta de visualização pygwalker, consegue analisar milhões de registros em poucos segundos
  Ainda assim, comparar com SQLite é um pouco injusto. SQLite é mais para construir sistemas; DuckDB, para análise
  Distribuir DuckDB em várias plataformas pode ser um pouco mais complicado
- Você nunca deveria armazenar datasets sem compressão. O DuckDB suporta vários formatos de compressão
Eu achava que usar Generated Column para desempenho com JSON era uma abordagem comum
Já fiz isso no Postgres para manter chaves dentro de colunas JSON como chaves estrangeiras. É meio “amaldiçoado”, mas funcionou bem
- No Postgres, não dá para criar índice diretamente sobre campos internos do JSONB?
  Por exemplo
```
CREATE INDEX idx_status_gin
ON my_table
USING gin ((data->'status'));
```
  Post de referência
- Mas, no fim das contas, muitas vezes é melhor separar isso em uma tabela key/value
  Se o schema do JSON mudar, parsing ou migração podem falhar
- Na verdade, nem é uma abordagem tão “amaldiçoada” assim. Você pode usar estrutura relacional normalizada onde precisa e jsonb para o resto
- Eu estava me perguntando se daria para usar coluna VIRTUAL em vez de STORED, e este exemplo usa VIRTUAL
Conheci essa técnica recentemente por causa de um exemplo de otimização sugerido pelo Claude Code
Era um recurso novo do SQLite que eu não conhecia, então tinha deixado passar, e o ganho de desempenho foi bem grande
A lição é que vale a pena reler periodicamente a documentação até das ferramentas com que você já está acostumado
- Reler o manual às vezes traz insights surpreendentes
Acabei escrevendo um post no blog depois de ver o comentário de bambax no HN em 2023
Link para o comentário original
Dá para criar índices sem projetar o JSON diretamente, mas a coluna calculada (computed column) deixa a consulta mais simples
Antes do MS-SQL 2025(v17), o suporte a JSON era limitado, então essa abordagem era indispensável
- Se você usar apenas colunas calculadas em vez de consultar JSON diretamente, evita escrever por engano uma consulta sem índice
- Ouvi falar desse recurso em uma conferência local de DBAs, mas na época não pareceu uma mudança tão grande
Abri a matéria no HN e vi que meu comentário tinha sido citado, e ainda por cima justamente sobre o tema do texto; foi uma experiência curiosa
Ver a frase “Obrigado, bambax!” me fez sorrir. SQLite é realmente uma ferramenta incrível
- O comentário que realmente inspirou o texto está neste link
Interessante, mas não daria para usar o "Index On Expression" do SQLite?
Por exemplo, CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))
Só que, se a sintaxe do JSON path variar um pouco que seja, o índice pode deixar de ser usado. Já a Virtual Generated Column garante sempre o índice
- Dá para usar índice por expressão junto com views para garantir correspondência da expressão
  Exemplo: projeto recordlite
- Se a sintaxe do JSON path mudar só um pouco, o índice pode quebrar
  Por exemplo, json_extract(data, "$.type") e data -> '$.type' são reconhecidos de forma diferente
  Ou seja, se a expressão no WHERE mudar, o índice pode se tornar inútil
- É uma solução simples e rápida. A exigência de que consulta e índice coincidam sempre foi verdadeira
- O recurso de índice por expressão é relativamente novo no SQLite, tendo sido adicionado no SQLite 3.9.0 (2015-10-14)
Gostaria que os desenvolvedores evitassem colocar todos os dados em colunas JSON(B) quando já existe um schema consistente
Isso dificulta configurar índices, gerenciar restrições e ainda adiciona overhead no uso real
- Colunas JSON brilham quando lidam com dados em árvore, difíceis de representar em tabelas
  Por exemplo, em um ambiente Haskell + TypeScript, serializar estruturas aninhadas complexas em JSON é muito mais prático
- JSON(B) é útil quando você quer armazenar dados de tipos diferentes na mesma coleção
  Ex.: salvar resultados de vários processadores de pagamento em uma única tabela ou lidar com atributos diferentes por item em um site de classificados
  Em C# ou JS/TS, também fica fácil de gerenciar usando ferramentas de validação de tipos como Zod, OpenAPI etc.
- Para JSON simples, normalizar costuma ser melhor, mas respostas complexas de API viram um inferno de JOINs se forem desmembradas em tabelas
  No fim, é uma questão de equilíbrio entre manutenção e desempenho. O ponto central do texto é que também dá para indexar JSON com facilidade
- Quando se trata de dados como os de sensores, em que se trabalha com a estrutura em árvore inteira de uma vez, colunas JSON são muito mais simples
  O desempenho de leitura pode ser compensado com índices sem problema
- Normalização completa muitas vezes é ineficiente
  Por exemplo, em um sistema de precificação de produtos, representar regras incomuns de desconto por cliente em JSON oferece muito mais flexibilidade
Se você usar XML em vez de JSON, isso vira o mesmo modelo dos bancos de dados orientados a documentos (document store) dos anos 90 e 2000
A estrutura era fazer parsing na inserção/atualização e acessar só os índices na consulta
É muito interessante que o SQLite ofereça esse tipo de recurso nativamente

iolothebard 2025-12-14

No fim do século 20… havia algo chamado banco de dados universal… (agora está certo, naquela época estava errado.)