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
\u
.
Na função de entrada para o tipo de dados XXXX
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.
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 JSON | Tipo PostgreSQL | Notas |
---|---|---|
string | text |
\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
|
number | numeric |
Os valores NaN e
infinity não são permitidos
|
boolean | boolean |
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
|
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.
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.
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.
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.
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
fora do padrão cadeia_de_operadores_de_acesso
= constante
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
[
.
A classe de operadores índice
]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.
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';
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.
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
\u
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: NNNN
\x
para código
de caractere escrito com apenas dois dígitos hexadecimais, e
NN
\u{
para código de caractere escrito com 1 a 6 dígitos hexadecimais.
N...
}
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ável | Descriçã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 acesso | Descrição |
---|---|
|
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 |
|
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. |
|
Como |
|
Acessador de elemento de matriz.
O
O |
|
Acessador curinga de elemento de matriz que retorna todos os elementos da matriz. |
[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.
[56] IBM Db2 documentation (N. T.)