8.14. Tipos de dados JSON

8.14.1. Sintaxe de entrada e saída do JSON
8.14.2. Projeto de documentos JSON
8.14.3. jsonb - Contém (⊃) e Existe (∃)
8.14.4. jsonb - Indexação
8.14.5. jsonb - Índice dos elementos
8.14.6. Transformações
8.14.7. Tipo de dados jsonpath
8.14.8. Exemplo

Os tipos de dados JSON são usados para armazenar dados JSON (JavaScript Object Notation), conforme especificado no RFC 7159 [51]. Esses dados também podem ser armazenados como text, mas os tipos de dados JSON têm a vantagem de garantir que cada valor armazenado seja válido segundo as regras do JSON. Também existem várias funções e operadores específicos de JSON disponíveis para dados armazenados nesses tipos de dados; veja Funções e operadores para JSON.

O PostgreSQL oferece dois tipos de dados para armazenamento de dados JSON: json e jsonb. Para implementar mecanismos de consultas eficientes para esses tipos de dados, o PostgreSQL também oferece o tipo de dados jsonpath descrito em Tipo de dados jsonpath.

Os tipos de dados json e jsonb aceitam como entrada conjuntos de dados praticamente idênticos. A principal diferença real é a eficiência. O tipo de dados json armazena uma cópia exata do texto da entrada, que as funções de processamento devem analisar novamente em cada execução; enquanto os dados jsonb são armazenados em um formato binário decomposto que o torna um pouco mais lento para entrada, devido à sobrecarga de conversão adicionada, mas muito mais rápido para processar, já que nenhuma nova análise é necessária. jsonb também permite indexação, o que pode ser uma vantagem significativa.

Como o tipo de dados json armazena uma cópia exata do texto de entrada, é preservado o espaço em branco semanticamente irrelevante entre os tokens [52], assim como a ordem das chaves nos objetos JSON. Além disso, se um objeto JSON contiver a mesma chave mais de uma vez dentro do valor, todos os pares chave/valor são mantidos. (As funções de processamento consideram o último valor como sendo o ativo.) Por outro lado, jsonb não preserva espaço em branco, não preserva a ordem das chaves de objeto, e não preserva chaves de objeto duplicadas. Se forem especificadas chaves duplicadas na entrada, apenas o último valor será mantido.

Em geral, para a maioria das aplicações deve-se preferir armazenar dados JSON como jsonb, a menos que existam necessidades muito especiais, tais como suposições legadas sobre a ordenação de chaves de objeto.

O RFC 7159 especifica que as cadeias JSON devem ser codificadas em UTF-8. Portanto, não é possível que os tipos de dados JSON estejam em rígida conformidade com a especificação JSON, a menos que a codificação do banco de dados seja UTF-8. As tentativas de incluir diretamente caracteres que não podem ser representados na codificação do banco de dados falham; por outro lado, caracteres que podem ser representados na codificação do banco de dados, mas não em UTF-8, são permitidos.

O RFC 7159 permite que as cadeias JSON contenham sequências de escape Unicode, representadas por \uXXXX. Na função de entrada para o tipo de dados json, os escapes Unicode são permitidos independentemente da codificação do banco de dados, sendo verificados apenas quanto à correção sintática (ou seja, que quatro dígitos hexadecimais venham após o \u). No entanto, a função de entrada para o tipo de dados jsonb é mais rigorosa: não permite escapes Unicode para caracteres que não podem ser representados na codificação do banco de dados. O tipo de dados jsonb também rejeita \u0000 (porque isso não pode ser representado no tipo de dados text do PostgreSQL), e requer que qualquer uso de pares substitutos [53] Unicode para designar caracteres fora do Plano Multilíngue Básico Unicode deva estar correto. Os escapes Unicode válidos são convertidos no caractere único equivalente para armazenamento; isso inclui dobrar pares substitutos em um único caractere.

Nota

Muitas funções de processamento JSON descritas em Funções e operadores para JSON convertem escapes Unicode em caracteres regulares, portanto lançam os mesmos tipos de erros descritos anteriormente, mesmo que sua entrada seja do tipo de dados json e não jsonb. O fato da função de entrada json não fazer essas verificações pode ser considerada uma peça histórica, embora permita armazenamento simples (sem processamento) de escapes Unicode JSON em uma codificação de banco de dados que não inclui o caractere representado.

Ao converter a entrada textual JSON em jsonb, os tipos de dados primitivos descritos pelo RFC 7159 são de fato transcritos em tipos de dados nativos do PostgreSQL, conforme mostrado na Tabela 8.23. Portanto, existem algumas pequenas restrições adicionais sobre o que constitui dados jsonb válidos, que não se aplicam nem ao tipo de dados json, nem ao JSON, em última análise, correspondendo aos limites sobre o que pode ser representado pelo tipo de dados subjacente. Em particular, o tipo de dados jsonb rejeita números que estão fora do intervalo do tipo de dados numeric do PostgreSQL, enquanto o tipo de dados json não rejeita. Tais restrições definidas pela implementação são permitidas pelo RFC 7159. No entanto, na prática esses problemas são muito mais prováveis de ocorrer em outras implementações, porque é comum representar o tipo de dados primitivo number do JSON como ponto flutuante de precisão dupla padrão IEEE 754 (que o RFC 7159 explicitamente antecipa e permite). Ao se usar JSON como formato de intercâmbio com esses sistemas, deve-se considerar o perigo de perder a precisão numérica em comparação com os dados originalmente armazenados pelo PostgreSQL.

Por outro lado, conforme observado na tabela, existem algumas pequenas restrições no formato de entrada dos tipos de dados primitivos JSON que não se aplicam aos tipos de dados do PostgreSQL correspondentes.

Tabela 8.23. Tipos de dados primitivos JSON e tipos de dados correspondentes no PostgreSQL

Tipo primitivo JSONTipo PostgreSQLNotas
stringtext \u0000 não é permitido, como também não são permitidos os escapes Unicode representando caracteres não disponíveis na codificação do banco de dados
numbernumeric Os valores NaN e infinity não são permitidos
booleanboolean São somente permitidas as grafias em letras minúsculas para true e false
null(nenhum) O NULL do padrão SQL tem um conceito diferente

8.14.1. Sintaxe de entrada e saída do JSON

A sintaxe de entrada e saída para os tipos de dados JSON é idêntica à especificada no RFC 7159.

Todos os exemplos a seguir contém expressões json (ou jsonb) válidas:

-- Valor escalar/primitivo simples
-- Os valores primitivos podem ser números,
-- cadeias de caracteres entre aspas,
-- true, false ou null
SELECT '5'::json;

-- Matriz de zero ou mais elementos
-- (os elementos não precisam ser do mesmo tipo de dados)
SELECT '[1, 2, "foo", null]'::json;

-- Objeto contendo pares de chaves e valores
-- Note que as chaves de objeto devem ser sempre
-- cadeias de caracteres entre aspas
SELECT '{"bar": "baz", "saldo": 7.77, "ativo": false}'::json;

-- Matrizes e objetos podem ser aninhados arbitrariamente
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

Como foi dito anteriormente, quando um valor JSON é inserido e mostrado sem nenhum processamento adicional, o tipo de dados json reproduz o mesmo texto inserido, enquanto o tipo de dados jsonb não preserva detalhes semanticamente irrelevantes, como espaço em branco. Por exemplo, observe essas diferenças:

SELECT '{"bar": "baz", "saldo": 7.77, "ativo":false}'::json;

                     json
----------------------------------------------
 {"bar": "baz", "saldo": 7.77, "ativo":false}
(1 linha)

SELECT '{"bar": "baz", "saldo": 7.77, "ativo":false}'::jsonb;

                     jsonb
-----------------------------------------------
 {"bar": "baz", "ativo": false, "saldo": 7.77}
(1 linha)

Um detalhe semanticamente irrelevante, que vale a pena ser notado, é que no tipo de dados jsonb os números são mostrados segundo o comportamento do tipo de dados numeric subjacente. Na prática, isso significa que os números digitados com a notação exponencial E são mostrados sem essa notação, por exemplo:

SELECT '{"leitura": 1.230e-5}'::json, '{"leitura": 1.230e-5}'::jsonb;

         json          |          jsonb
-----------------------+-------------------------
 {"leitura": 1.230e-5} | {"leitura": 0.00001230}
(1 linha)

No entanto, o tipo de dados jsonb preserva os zeros fracionários à direita, como visto nesse exemplo, mesmo que sejam semanticamente irrelevantes para finalidades como a verificação de igualdade.

Para obter a lista de funções e operadores nativos disponíveis para construir e processar valores JSON, veja Funções e operadores para JSON.

8.14.2. Projeto de documentos JSON

A representação de dados como JSON pode ser consideravelmente mais flexível do que o modelo de dados relacional tradicional, sendo atraente em ambientes onde os requisitos são fluidos. É bem possível que as duas abordagens coexistam e se complementem dentro da mesma aplicação. No entanto, mesmo para aplicações em que se deseja a máxima flexibilidade, ainda é recomendável que os documentos JSON tenham uma estrutura um tanto fixa. Normalmente a estrutura não é imposta (embora seja possível impor algumas regras de negócios declarativamente), mas ter uma estrutura previsível facilita escrever consultas que resumem de maneira útil um conjunto de documentos (datums) em uma tabela. [54]

Os dados JSON estão sujeitos às mesmas considerações de controle de simultaneidade que qualquer outro tipo de dados quando armazenados em uma tabela. Embora o armazenamento de documentos grandes seja factível, lembre-se de que qualquer atualização adquire um bloqueio no nível de linha em toda a linha. Considere limitar os documentos JSON a um tamanho gerenciável para diminuir a contenção de bloqueio entre as transações de atualização. Idealmente, os documentos JSON devem representar um dado atômico que as regras de negócios ditam que não podem ser subdivididos em dados menores que podem ser modificados independentemente.

8.14.3. jsonb - Contém (⊃) e Existe (∃)

Testar se contém (⊃) é uma capacidade importante do tipo de dados jsonb. Não há um conjunto análogo a essa facilidade para o tipo de dados json. Contém (@>) testa se um documento do tipo de dados jsonb está contido em outro. Esses exemplos retornam verdade, exceto quando indicado o contrário:

-- Valores escalares/primitivos simples
-- contendo apenas um valor idêntico:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- A matriz do lado direito está contida
-- na matriz à esquerda:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- A ordem dos elementos da matriz não é significativa,
-- portanto isso também é verdade:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- Elementos de matriz duplicados também não importam:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- O objeto com um único par no lado direito
-- está contido no objeto no lado esquerdo:
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb
 @> '{"version": 9.4}'::jsonb;

-- A matriz do lado direito não é considerada
-- contida na matriz à esquerda, mesmo que uma
-- matriz similar esteja aninhada dentro dela:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- retorna falso

-- Mas com uma camada de aninhamento, está contida:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- Da mesma forma, a estar contida não é relatada aqui:
SELECT '{"foo": {"bar": "baz"}}'::jsonb
 @> '{"bar": "baz"}'::jsonb;  -- retorna falso

-- Uma chave de nível superior e um objeto vazio estão contidos:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

O princípio geral é que o objeto contido deve corresponder ao objeto que o contém quanto à estrutura e ao conteúdo dos dados, possivelmente após descartar alguns elementos de matriz ou pares chave/valor não correspondentes do objeto contenedor. Mas deve ser lembrado que a ordem dos elementos da matriz não é significativa ao se fazer uma correspondência de estar contido, e os elementos duplicados da matriz são de fato considerados apenas uma vez.

Como uma exceção especial ao princípio geral de que as estruturas devem se corresponder, uma matriz pode conter um valor primitivo:

-- Essa matriz contém um valor cadeia de caracteres primitivo
-- ou seja, ["foo", "bar"] ⊃ "bar"
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- Essa exceção não é recíproca -- aqui é relatado que não contém
-- ou seja, "bar" ⊅ ["foo", "bar"]
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- retorna falso

O tipo de dados jsonb também possui um operador existe, que é uma variação do tema contém: testa se uma cadeia de caracteres (fornecida como um valor do tipo de dados text) aparece como uma chave de objeto, ou elemento de matriz, no nível superior do valor jsonb. Esses exemplos retornam verdade, exceto quando indicado o contrário:

-- Cadeias de caracteres existem como elemento de uma matriz
-- ou seja, 'bar' ∃ ["foo", "bar", "baz"]
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- Cadeias de caracteres existem como chave de objeto
-- ou seja, 'foo' ∃ {"foo": "bar"}
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- Os valores do objeto não são considerados
-- ou seja, 'bar' ∄ {"foo": "bar"}
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- retorna falso

-- Da mesma forma que contém, existe
-- deve corresponder ao nível superior
-- ou seja, 'bar' ∄ {"foo": {"bar": "baz"}}
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- retorna falso

-- Uma cadeia de caracteres é considerada existente se
-- corresponder a uma cadeia de caracteres JSON primitiva
-- ou seja, 'foo' ∃ "foo"
SELECT '"foo"'::jsonb ? 'foo';

Os objetos JSON são mais adequados que as matrizes para testar se contém ou existe quando há muitas chaves ou elementos envolvidos, porque, ao contrário das matrizes, são otimizados internamente para procura e não precisam ser procurados linearmente.

Dica

Como contém no JSON é aninhado, uma consulta apropriada pode ignorar a seleção explícita de subobjetos. Como exemplo, suponha que temos a coluna doc contendo objetos no nível superior, com a maioria dos objetos contendo campos tags que contêm matrizes de subobjetos. Essa consulta encontra as entradas nas quais aparecem subobjetos contendo tanto "term":"paris" quanto "term":"food", enquanto ignora qualquer chave fora da matriz tags:

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

Pode-se realizar a mesma consulta com, digamos,

SELECT doc->'site_name' FROM websites
  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

mas essa abordagem é menos flexível e muitas vezes menos eficiente também.

Por outro lado, o operador existe JSON não é aninhado: apenas procura a chave especificada, ou o elemento de matriz, no nível superior do valor JSON.

Os vários operadores de contém e existe, junto com todos os outros operadores e funções JSON, são documentados em Funções e operadores para JSON.

8.14.4. jsonb - Indexação

Podem ser usados índices GIN para procurar com eficiência chaves, ou pares chave/valor, que ocorrem em um grande número de documentos jsonb (datums). Estão disponíveis duas classes de operador GIN, que oferecem diferentes compromissos de desempenho e flexibilidade.

A classe de operadores GIN padrão para jsonb aceita consultas com os operadores chave-existe ?, ?| e ?&, o operador contém @>, e os operadores de correspondência jsonpath @? e @@. (Para detalhes da semântica que esses operadores implementam, veja a Tabela 9.46.) Um exemplo de criação de índice com essa classe de operadores é:

CREATE INDEX idxgin ON api USING GIN (jdoc);

A classe de operadores GIN não padrão jsonb_path_ops não aceita os operadores chave-existe, mas aceita @>, @? e @@. Um exemplo de criação de índice com essa classe de operadores é:

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);

Considere o exemplo de uma tabela que armazena documentos JSON obtidos de um serviço Web de terceiros, com uma definição de esquema documentada. Um documento típico é:

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

Armazenamos esses documentos em uma tabela chamada api, numa coluna do tipo de dados jsonb chamada jdoc. Se um índice GIN for criado nessa coluna, consultas como a que se segue podem fazer uso desse índice:

-- Encontrar documentos nos quais a chave
-- "company" têm o valor "Magnafone"
SELECT jdoc->'guid', jdoc->'name'
    FROM api
    WHERE jdoc @> '{"company": "Magnafone"}';

No entanto, o índice não pode ser usado para consultas como a que se segue, porque embora o operador ? seja indexável, não está aplicado diretamente à coluna indexada jdoc:

-- Encontrar documentos nos quais a chave "tags"
-- contém a chave ou o elemento de matriz "qui"
SELECT jdoc->'guid', jdoc->'name'
    FROM api
    WHERE jdoc -> 'tags' ? 'qui';

Mesmo assim, com o uso apropriado de índices baseados em expressões a consulta acima pode usar um índice. Se for comum consultar itens específicos dentro da chave "tags", definir um índice como esse pode valer a pena:

CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

Agora, a cláusula WHERE jdoc -> 'tags' ? 'qui' será reconhecida como uma aplicação do operador indexável ? para a expressão indexada jdoc -> 'tags'. (Podem ser encontradas mais informações sobre índices baseados em expressões em Índices em expressões.)

Outra abordagem para consultar é explorar o contém, por exemplo:

-- Encontrar documentos nos quais a chave "tags" contém o elemento de matriz "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

Um índice GIN simples na coluna jdoc pode aceitar essa consulta. Mas observe que esse índice vai armazenar cópias de cada chave e valor na coluna jdoc, enquanto o índice de expressão do exemplo anterior armazena apenas os dados encontrados na chave tags. Embora a abordagem de índice simples seja muito mais flexível (já que aceita consultas sobre qualquer chave), é provável que os índices sobre expressões específicas sejam menores e mais rápidos de procurar do que um índice simples.

Os índices GIN também aceitam os operadores @? e @@, que realizam a correspondência jsonpath. Como exemplos

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';

Para esses operadores, um índice GIN extrai cláusulas da forma cadeia_de_operadores_de_acesso = constante fora do padrão jsonpath, e faz a procura do índice com base nas chaves e valores mencionados nessas cláusulas. A cadeia de operadores de acesso pode incluir os operadores de acesso .chave, [*], e [índice]. A classe de operadores jsonb_ops também aceita os operadores de acesso .* e .**, mas a classe de operadores jsonb_path_ops não aceita.

Embora a classe de operadores jsonb_path_ops só permita consultas com os operadores @>, @? e @@, ela possui vantagens de desempenho notáveis sobre a classe de operador padrão jsonb_ops. Um índice jsonb_path_ops é geralmente muito menor que um índice jsonb_ops sobre os mesmos dados, e a especificidade das procuras é melhor, principalmente quando as consultas contêm chaves que aparecem com frequência nos dados. Portanto, as operações de procura têm geralmente um desempenho melhor do que com a classe de operadores padrão.

A diferença técnica entre os índices GIN jsonb_ops e jsonb_path_ops, é que o primeiro cria itens de índice independentes para cada chave e valor nos dados, enquanto o último cria itens de índice apenas para cada valor nos dados. [55] Basicamente, cada item do índice jsonb_path_ops é um hash do valor e da(s) chave(s) que levam a ele; por exemplo, para indexar {"foo": {"bar": "baz"}}, seria criado um único item de índice incorporando todos os três foo, bar e baz em um valor de hash. Assim, uma consulta de contém procurando por essa estrutura resultaria em uma procura de índice extremamente específica; mas não há como saber se foo aparece como uma chave. Por outro lado, um índice jsonb_ops criaria três itens de índice representando foo, bar e baz separadamente; então, para fazer a consulta de contém, seriam procuradas linhas contendo todos esses três itens. Embora os índices GIN possam realizar uma procura AND com bastante eficiência, ainda será menos específico e mais lento que a procura jsonb_path_ops equivalente, principalmente se houver um número muito grande de linhas contendo qualquer um dos três itens de índice.

Uma desvantagem do enfoque jsonb_path_ops é não produzir entradas de índice para estruturas JSON que não contenham valores, como {"a": {}}. Se for solicitada uma procura de documentos que contenham tal estrutura, será necessária a varredura de índice completa, que é bastante lenta. O jsonb_path_ops é, portanto, inadequado para aplicações que realizam essas procuras com frequência.

O tipo de dados jsonb também aceita índices btree e hash. Geralmente só são úteis se for importante verificar a igualdade de documentos completos JSON. A ordenação btree para dados jsonb é raramente de grande interesse, mas para estar completo é:

Objeto > Matriz > Booleano > Número > Cadeia > Null

Objeto com n pares > objeto com n -1 pares

Matriz com n elementos > matriz com n - 1 elementos

Objetos com números iguais de pares são comparados na ordem:

chave-1, valor-1, chave-2 ...

Note que as chaves de objeto são comparadas na sua ordem de armazenamento; em particular, como as chaves mais curtas são armazenadas antes das chaves mais longas, isso pode levar a resultados que podem não ser intuitivos, como:

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

Da mesma forma, matrizes com números iguais de elementos são comparadas na ordem:

elemento-1, elemento-2 ...

Os valores JSON primitivos são comparados usando as mesmas regras de comparação do tipo de dados subjacente do PostgreSQL. As cadeias de caracteres são comparadas usando a ordenação padrão do banco de dados.

8.14.5. jsonb - Índice dos elementos

O tipo de dados jsonb aceita expressões com índice, no estilo matriz, para extrair e modificar elementos. Os valores aninhados podem ser apontados encadeando expressões de índice, seguindo as mesmas regras do argumento caminho na função jsonb_set. Se um valor jsonb for uma matriz, os índices numéricos começam em zero, e os números inteiros negativos contam para trás a partir do último elemento da matriz. Expressões de intervalo não são aceitas. O resultado de uma expressão de índice é sempre do tipo de dados jsonb.

As instruções UPDATE podem usar índices na cláusula SET para modificar valores jsonb. Os caminhos de índice devem ser percorríveis para todos os valores atribuídos. Por exemplo, o caminho val['a']['b']['c'] pode ser percorrido até c, se val, val['a'] e val['a']['b'] forem todos objetos. Se algum val['a'] ou val['a']['b'] não estiver definido, será criado como um objeto vazio e preenchido conforme necessário. Entretanto, se o próprio val, ou um de seus valores intermediários, estiverem definidos como não-objeto, como uma cadeia de caracteres, número, ou jsonb null, a travessia não pode continuar e, então, um erro é gerado e a transação é interrompida.

Exemplo da sintaxe de índice:

-- Extrair o valor do objeto pela chave
SELECT ('{"a": 1}'::jsonb)['a'];

-- Extrair o valor do objeto aninhado pelo caminho da chave
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];

-- Extrair o elemento da matriz pelo índice
SELECT ('[1, "2", null]'::jsonb)[1];

-- Atualizar o valor do objeto pela chave.
-- Note os apóstrofos em torno de '1':
-- o valor atribuído deve ser do tipo de dados jsonb também
UPDATE nome_da_tabela SET campo_jsonb['key'] = '1';

-- Esse comando vai gerar um erro se o campo_jsonb['a']['b']
-- de algum registro não for um objeto. Por exemplo, o valor
-- {"a": 1} tem um valor numérico para a chave 'a'.
UPDATE nome_da_tabela SET campo_jsonb['a']['b']['c'] = '1';

-- Filtrar registros usando a cláusula WHERE com índices.
-- Uma vez que o resultado da indexação é jsonb,
-- o valor a ser comparado também deve ser jsonb.
-- As aspas tornam "valor" uma cadeia de caracteres jsonb válida.
SELECT * FROM nome_da_tabela WHERE campo_jsonb['chave'] = '"valor"';

A atribuição de jsonb via índice trata alguns casos especiais de forma diferente de jsonb_set. Quando um valor jsonb de origem é NULL, a atribuição via índice continua como se fosse um valor JSON vazio do tipo (objeto ou matriz) implícito pela chave do índice:

-- Onde campo_jsonb era NULL, agora é {"a": 1}
UPDATE nome_da_tabela SET campo_jsonb['a'] = '1';

-- Onde campo_jsonb era NULL, agora é [1]
UPDATE nome_da_tabela SET campo_jsonb[0] = '1';

Se for especificado um índice para uma matriz contendo menos elementos, são acrescentados elementos NULL até que o índice seja alcançável e o valor possa ser definido.

-- Onde campo_jsonb era [], agora é [null, null, 2];
-- Onde campo_jsonb era [0], agora é [0, null, 2]
UPDATE nome_da_tabela SET campo_jsonb[2] = '2';

Um valor jsonb aceita atribuições para caminhos de índices inexistentes, desde que o último elemento existente a ser percorrido seja um objeto ou matriz, como indicado pelo índice correspondente (o elemento indicado pelo último índice no caminho não é atravessado e pode ser qualquer coisa). Matrizes aninhadas e estruturas de objetos são criadas e, no primeiro caso, preenchidas com null, conforme especificado pelo caminho do índice até que o valor atribuído possa ser colocado.

-- Onde campo_jsonb era {}, agora é {"a": [{"b": 1}]}
UPDATE nome_da_tabela SET campo_jsonb['a'][0]['b'] = '1';

-- Onde campo_jsonb era [], agora é [null, {'a': 1}]
UPDATE nome_da_tabela SET campo_jsonb[1]['a'] = '1';

8.14.6. Transformações

Estão disponíveis extensões adicionais que implementam transformações para o tipo de dados jsonb para diferentes linguagens procedurais.

As extensões para PL/Perl se chamam jsonb_plperl e jsonb_plperlu. Se forem usadas, os valores jsonb são transformados em matrizes Perl, hashes e escalares, conforme apropriado.

As extensões para PL/Python se chamam jsonb_plpythonu, jsonb_plpython2u e jsonb_plpython3u (veja a Python 2 vs. Python 3 para a convenção de nomenclatura PL/Python). Se forem usadas, os valores jsonb são transformados em dicionários Python, listas e escalares, conforme apropriado.

Dessas extensões, jsonb_plperl é considerada trusted, ou seja, pode ser instalada por não-superusuários que tenham o privilégio CREATE no banco de dados corrente. As demais requerem o privilégio de superusuário para serem instaladas.

8.14.7. Tipo de dados jsonpath

O tipo de dados jsonpath implementa suporte à linguagem de caminho SQL/JSON no PostgreSQL, para permitir a consulta de dados JSON com eficiência. Esse tipo de dados fornece uma representação binária da expressão de caminho SQL/JSON analisada, que especifica os itens a serem recuperados pelo mecanismo de caminho dos dados JSON, para processamento adicional com as funções de consulta SQL/JSON.

A semântica dos predicados e operadores de caminho SQL/JSON segue geralmente o SQL. Ao mesmo tempo, para fornecer uma maneira natural de trabalhar com dados JSON, a sintaxe de caminho SQL/JSON usa algumas convenções do JavaScript:

  • Ponto (.) é usado para acessar membro.

  • Colchetes ([]) são usados para acessar matriz.

  • As matrizes SQL/JSON são numeradas a partir de 0, enquanto as matrizes SQL regulares são numeradas a partir de 1.

Uma expressão de caminho SQL/JSON é normalmente escrita em uma consulta SQL como um literal cadeia de caracteres SQL, portanto deve ser colocada entre apóstrofos ('), e quaisquer apóstrofos desejados dentro do valor devem ser duplicados (veja Constantes do tipo cadeia de caracteres). Algumas formas de expressões de caminho requerem literais cadeia de caracteres dentro delas. Esses literais cadeia de caracteres incorporados seguem as convenções JavaScript/ECMAScript: devem estar entre aspas ("), e escapes de contrabarra podem ser usados dentro deles para representar caracteres difíceis de digitar. Em particular, a maneira de escrever aspas em um literal cadeia de caracteres incorporado é \", e para escrever uma contrabarra, deve ser escrito \\. Outras sequências especiais de contrabarra incluem aqueles reconhecidos em cadeias JSON: \b, \f, \n, \r, \t, \v para vários caracteres de controle ASCII, e \uNNNN para caractere Unicode identificado por seu ponto de código de 4 dígitos hexadecimais. A sintaxe de contrabarra também inclui dois casos não permitidos pelo JSON: \xNN para código de caractere escrito com apenas dois dígitos hexadecimais, e \u{N...} para código de caractere escrito com 1 a 6 dígitos hexadecimais.

Uma expressão de caminho consiste em uma sequência de elementos de caminho, que pode ser qualquer um dos seguintes:

  • Literais de caminho de tipos de dados primitivos JSON: texto Unicode, numeric, true, false, ou null.

  • Variáveis de caminho listadas na Tabela 8.24.

  • Operadores de acesso listados na Tabela 8.25.

  • Operadores e métodos jsonpath listados em Operadores e métodos de caminho SQL/JSON.

  • Parênteses, que podem ser usados para fornecer expressões de filtro, ou definir a ordem de avaliação do caminho.

Para mais detalhes sobre o uso de expressões jsonpath com funções de consulta SQL/JSON, veja Linguagem de caminho SQL/JSON.

Tabela 8.24. Variáveis jsonpath

VariávelDescrição
$Uma variável representando o valor JSON que está sendo consultado (o elemento de contexto).
$varname Uma variável com nome. Seu valor pode ser definido pelo parâmetro vars de várias funções de processamento JSON; veja a Tabela 9.48 para obter detalhes.
@ Uma variável representando o resultado da avaliação do caminho em expressões de filtro.

Tabela 8.25. Operadores de acesso jsonpath

Operador de acessoDescrição

.chave

."$nome_da_varável"

Operador de acesso de membro, que retorna o membro do objeto com a chave especificada. Se o nome da chave corresponder a alguma variável com o nome começando por $, ou não atender às regras do JavaScript para identificador, deverá ser colocado entre aspas para torná-lo um literal cadeia de caracteres.

.*

Acessador de membro curinga que retorna os valores de todos os membros localizados no nível superior do objeto corrente.

.**

Acessador de membro curinga recursivo que processa todos os níveis da hierarquia JSON do objeto corrente e retorna todos os valores do membro, independentemente de seu nível de aninhamento. Essa é uma extensão do PostgreSQL ao padrão SQL/JSON.

.**{nível}

.**{nível_inicial a nível_final}

Como .**, mas seleciona apenas os níveis especificados da hierarquia JSON. Os níveis de aninhamento são especificados como números inteiros. O nível zero corresponde ao objeto corrente. Para acessar o nível de aninhamento mais baixo, pode ser usada a palavra-chave last. Essa é uma extensão do PostgreSQL ao padrão SQL/JSON.

[índice, ...]

Acessador de elemento de matriz. O índice pode ser especificado de duas formas: índice ou índice_inicial a índice_final. A primeira forma retorna um único elemento de matriz pelo seu índice. A segunda forma retorna uma fatia de matriz no intervalo dos índices, incluindo os elementos que correspondem ao índice_inicial e índice_final especificados.

O índice especificado pode ser um valor inteiro, assim como uma expressão que retorna um único valor numérico, sendo automaticamente convertido em inteiro. O índice zero corresponde ao primeiro elemento da matriz. Também pode ser usada a palavra-chave last para denotar o último elemento da matriz, sendo útil para lidar com matrizes de comprimento desconhecido.

[*]

Acessador curinga de elemento de matriz que retorna todos os elementos da matriz.


8.14.8. Exemplo

[Exemplo escrito pelo tradutor, não fazendo parte da documentação original]

Exemplo 8.16. Consultar objetos JSON

Esse exemplo mostra consultas efetuadas em objetos JSON usando os sistemas gerenciadores de banco de dados PostgreSQL e IBM DB2 [56] para efeitos de comparação.

-- PostgreSQL

-- Matriz

SELECT '{
    "presidente":
    ["Deodoro da Fonseca",
     "Floriano Peixoto",
     "Prudente de Morais"]}'::jsonb AS "Presidentes";

                                   Presidentes
----------------------------------------------------------------------------------
 {"presidente": ["Deodoro da Fonseca", "Floriano Peixoto", "Prudente de Morais"]}

WITH j(jdoc) AS (VALUES (
    '{"presidente":
     ["Deodoro da Fonseca",
      "Floriano Peixoto",
      "Prudente de Morais"]}'::jsonb))
SELECT jdoc['presidente'][0] AS "Primeiro Presidente"
FROM j;

 Primeiro Presidente
----------------------
 "Deodoro da Fonseca"

-- Tipo json com chave repetida
-- pares chave/valor mantidos
-- preserva a entrada integralmente

WITH j(jdoc) AS (VALUES (
    '{"nome": "John", "sobrenome": "Doe",
      "nome": "Jane", "sobrenome": "Roe"}'::json))
SELECT *
FROM j;

                   jdoc
-------------------------------------------
 {"nome": "John", "sobrenome": "Doe",     +
       "nome": "Jane", "sobrenome": "Roe"}

-- Tipo jsonb com chave repetida
-- último par chave/valor ativo
-- não preserva o texto da entrada

WITH j(jdoc) AS (VALUES (
    '{"nome": "John", "sobrenome": "Doe",
      "nome": "Jane", "sobrenome": "Roe"}'::jsonb))
SELECT *
FROM j;

                 jdoc
--------------------------------------
 {"nome": "Jane", "sobrenome": "Roe"}

-- Objeto aninhado

WITH j(jdoc) AS (
    VALUES ('{"id":"701",
              "nome":{"primeiro":"John", "último":"Doe"}}'::jsonb))
SELECT jdoc['nome']['último']
FROM j;

 jdoc
-------
 "Doe"


-- IBM DB2

-- Matriz

-- https://www.ibm.com/docs/en/db2/11.5?topic=functions-json2bson
WITH j(jdoc) AS (VALUES (SYSTOOLS.JSON2BSON(
    '{"presidente":
     ["Deodoro da Fonseca", "Floriano Peixoto", "Prudente de Morais"]}'
)))
SELECT SYSTOOLS.BSON2JSON(jdoc)
FROM j

|1                                                                            |
|-----------------------------------------------------------------------------|
|{"presidente":["Deodoro da Fonseca","Floriano Peixoto","Prudente de Morais"]}|

-- https://www.ibm.com/docs/en/db2/11.5?topic=functions-json-bson
WITH j(jdoc) AS (VALUES (JSON_TO_BSON(
    '{"presidente":
     ["Deodoro da Fonseca", "Floriano Peixoto", "Prudente de Morais"]}'
)))
SELECT JSON_VALUE(jdoc, '$.presidente[0]')  AS "Primeiro Presidente"
FROM j;

|Primeiro Presidente|
|-------------------|
|Deodoro da Fonseca |

-- Objeto JSON com chave repetida

-- https://www.ibm.com/docs/en/db2/11.5?topic=functions-json-object
WITH j(jdoc) AS (VALUES JSON_OBJECT(
    KEY 'nome' VALUE 'John', KEY 'sobrenome' VALUE 'Doe',
    KEY 'nome' VALUE 'Jane', KEY 'sobrenome' VALUE 'Roe'
    RETURNING CHARACTER VARYING(128) FORMAT JSON))
SELECT *
FROM j

|JDOC                                                             |
|-----------------------------------------------------------------|
|{"nome":"John","sobrenome":"Doe","nome":"Jane","sobrenome":"Roe"}|

-- Objeto no formato binário com chave repetida

-- https://www.ibm.com/docs/en/db2/11.5?topic=functions-json-query
WITH j(jdoc) AS (VALUES JSON_TO_BSON(
    '{"nome": "John", "sobrenome": "Doe",
      "nome": "Jane", "sobrenome": "Roe"}'))
SELECT JSON_QUERY(jdoc,'$.sobrenome')
FROM j

|1    |
|-----|
|"Doe"|

-- Objeto aninhado

WITH j(jdoc) AS (
VALUES JSON_TO_BSON(
    '{"id":"701",
      "nome":{"primeiro":"John", "último":"Doe"}}'
))
SELECT JSON_QUERY(jdoc, '$.nome.último')
FROM j

|1    |
|-----|
|"Doe"|



[51] Tornado obsoleto pelo RFC 8259, The JavaScript Object Notation (JSON) Data Interchange Format, de 12/2017. (N. T.)

[52] Token: Uma unidade básica e gramaticalmente indivisível de uma linguagem, como uma palavra-chave, operador ou identificador. FOLDOC - Free On-Line Dictionary Of Computing (N. T.)

[53] Par substituto (Surrogate pair): Uma representação para um único caractere abstrato que consiste em uma sequência de duas unidades de código de 16 bits, onde o primeiro valor do par é uma unidade de código substituto alto e o segundo valor é uma unidade de código substituto baixo. Surrogates (N. T.)

[54] Uma coleção é um grupo de documentos JSON existentes em um banco de dados. Os documentos de uma coleção podem ter campos diferentes, embora, geralmente, todos os documentos de uma coleção tenham uma finalidade semelhante ou relacionada. IBM DB2 JSON Collections (N. T.)

[55] Para esse propósito, o termo valor inclui elementos de matriz, embora a terminologia JSON às vezes considere elementos de matriz distintos de valores dentro de objetos.