Gerir colunas e índices JSON

  • {númeroDeMinutos} minutos

As bases de dados relacionais funcionam melhor quando todas as linhas de uma tabela têm as mesmas colunas. Defines a estrutura uma vez, e todos os registos seguem-na. Este design funciona bem para dados como clientes, encomendas ou faturas, onde os campos são previsíveis. Mas alguns dados variam de registo para registo. Os atributos que precisa de armazenar dependem do tipo de item, da origem dos dados ou das escolhas feitas pelos utilizadores. O design tradicional de tabelas obriga-te a criar muitas colunas vazias na maioria das linhas, ou a dividir os dados por várias tabelas. As colunas JSON oferecem outra opção: armazenar as partes variáveis como JSON, mantendo as partes previsíveis em colunas regulares.

Por exemplo, um catálogo de produtos de comércio eletrónico tem campos comuns como nome do produto, preço e categoria que se aplicam a todos os artigos. Mas uma t-shirt precisa de tamanho e cor, um portátil precisa de velocidade de processador e tamanho de ecrã, e um livro precisa de autor e outros atributos. Com JSON, armazenas os campos comuns como colunas e colocas os atributos específicos da categoria numa coluna JSON. Pode adicionar novos tipos de produtos sem alterar a estrutura da tabela.

Compreenda quando usar colunas JSON

As colunas JSON permitem consultar e indexar dados semi-estruturados usando sintaxe SQL familiar. Não precisas de uma base de dados NoSQL separada para gerir dados flexíveis. Considere o JSON nestes cenários:

  • Preferências do utilizador - Definições como tema, idioma e opções de notificação variam de utilizador para utilizador e mudam à medida que adicionas funcionalidades.
  • Respostas API - Dados de serviços externos têm estruturas aninhadas que podem mudar quando o fornecedor atualiza a sua API.
  • Registos de auditoria - Os registos que capturam estados de antes e depois precisam de se adaptar à medida que os esquemas das tabelas evoluem.
  • Aplicações multi-inquilino - Diferentes clientes requerem campos personalizados distintos.
  • Metadados flexíveis - Etiquetas, etiquetas e propriedades que variam consoante o registo e não se encaixam num esquema fixo.

Criar e consultar colunas JSON

O SQL Server 2025 introduz um tipo de dados nativo json que armazena documentos JSON num formato binário otimizado para consulta e manipulação. O tipo nativo proporciona leituras mais eficientes (o documento já está analisado), escritas mais eficientes (as atualizações podem modificar valores individuais sem reescrever todo o documento) e melhor compressão de armazenamento em comparação com o armazenamento JSON como NVARCHAR(MAX).

Para versões anteriores do SQL Server, armazena-se JSON numa NVARCHAR(MAX) coluna.

Para ler valores do JSON, usas funções JSON como JSON_VALUE extrair um único valor ou JSON_QUERY devolver um objeto ou array. Se consultares frequentemente uma propriedade JSON, podes criar um índice numa coluna calculada que extrai essa propriedade.

O exemplo seguinte cria uma tabela com uma coluna JSON, insere documentos, consulta propriedades específicas, atualiza valores e cria um índice num campo frequentemente acedido:

-- Create table with native JSON type (SQL Server 2025+)
CREATE TABLE ConfigurationData (
    ConfigID INT PRIMARY KEY,
    ConfigSettings JSON NOT NULL
);

-- Insert JSON documents
INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (1, '{"theme":"dark","language":"en","notifications":true}');

INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (2, '{"theme":"light","language":"fr","notifications":false}');

-- Query JSON properties
SELECT ConfigID,
       JSON_VALUE(ConfigSettings, '$.theme') AS Theme,
       JSON_VALUE(ConfigSettings, '$.language') AS Language,
       JSON_QUERY(ConfigSettings, '$') AS FullConfig
FROM ConfigurationData;

-- Update a single property using the modify method (SQL Server 2025+ preview)
UPDATE ConfigurationData
SET ConfigSettings.modify('$.theme', 'light')
WHERE ConfigID = 1;

-- Alternative: JSON_MODIFY works with both JSON and NVARCHAR(MAX) columns
UPDATE ConfigurationData
SET ConfigSettings = JSON_MODIFY(CAST(ConfigSettings AS NVARCHAR(MAX)), '$.notifications', CAST(0 AS BIT))
WHERE ConfigID = 1;

-- Create index on frequently queried JSON property
ALTER TABLE ConfigurationData
ADD ThemeValue AS JSON_VALUE(ConfigSettings, '$.theme');

CREATE INDEX IX_Theme ON ConfigurationData(ThemeValue);

Este exemplo cria uma tabela com uma JSON coluna que armazena as definições de configuração do utilizador. As INSERT instruções adicionam documentos JSON como literais de cadeia. Para ler valores específicos, JSON_VALUE extrai valores escalares como o tema e a linguagem, enquanto JSON_QUERY devolve todo o objeto JSON. O .modify() método (atualmente em pré-visualização) atualiza uma única propriedade sem reescrever todo o documento. Como o json tipo não pode ser usado como uma coluna chave de índice, o exemplo cria uma coluna computada que extrai o valor do tema e depois indexa essa coluna calculada.

Combinar estrutura relacional e JSON

As colunas JSON funcionam melhor para dados que variam consoante o registo. Se todas as linhas tiverem os mesmos campos com tipos de dados consistentes, colunas regulares encaixam melhor. Obtém-se validação nativa de tipos de dados, consultas mais simples sem sintaxe de caminho JSON e indexação direta nas colunas. Use JSON para as partes dos seus dados que precisam de flexibilidade e mantenha as partes previsíveis em colunas digitadas.

Pode combinar estrutura relacional com flexibilidade JSON para produtos que requerem metadados variáveis. Eis um exemplo:

-- Product with flexible metadata (SQL Server 2025+)
CREATE TABLE ProductMetadata (
    ProductID INT PRIMARY KEY,
    AdditionalAttributes JSON NOT NULL
        CHECK (JSON_PATH_EXISTS(AdditionalAttributes, '$.weight') = 1),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Store flexible product attributes
INSERT INTO ProductMetadata (ProductID, AdditionalAttributes) 
VALUES (1, '{"dimensions":{"length":10,"width":5,"height":8},"weight":2.5,"color":"blue"}');

-- Query nested JSON properties
SELECT ProductID,
       JSON_VALUE(AdditionalAttributes, '$.weight') AS Weight,
       JSON_VALUE(AdditionalAttributes, '$.dimensions.length') AS Length
FROM ProductMetadata;

Considere os princípios de design JSON

Aplique estes princípios ao implementar colunas JSON:

  • Use JSON para dados semi-estruturados - Armazene estruturas de dados flexíveis que variam por registo, não dados com esquemas consistentes.
  • Indexar caminhos frequentemente consultados - Crie colunas computadas com índices em propriedades JSON que consulta frequentemente.
  • Validar as propriedades necessárias - Utilize CHECK restrições com JSON_PATH_EXISTS para assegurar que os campos obrigatórios estejam presentes.
  • Equilibrar flexibilidade com estrutura - Mantenha dados previsíveis em colunas regulares e use JSON apenas para as partes variáveis.

As colunas JSON proporcionam flexibilidade de esquema para dados variáveis, mantendo as capacidades de consulta SQL, mas devem complementar, em vez de substituir, o design relacional para dados estruturados.