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 esta 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 a Seção 9.16.

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. ^[56]

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 esta 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:

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

-- Esta 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.

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

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

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

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.48.) Um exemplo de criação de índice com esta 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 esta 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 na Seção 11.7.)

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 esta consulta. Mas observe que este í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. ^[57] 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 esta 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 é:

Object > Array > Boolean > Number > String > null

Objeto com n pares > objeto com n -1 pares

Matriz com n elementos > matriz com n - 1 elementos

com a exceção de que (por razões históricas) uma matriz vazia de nível superior tem prioridade menor que null. 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';

-- Este 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.

A extensão para PL/Python se chama jsonb_plpython3u. Se for usada, os valores jsonb serão mapeados para dicionários, listas e escalares do Python, conforme apropriado.

Destas 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. Este 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:

Os literais numéricos em expressões de caminho SQL/JSON seguem as regras do JavaScript, que diferem das regras do SQL e do JSON em alguns detalhes menores. Por exemplo, o caminho SQL/JSON permite .1 e 1., que são inválidos no JSON. São aceitos números inteiros não decimais e sublinhados, como, por exemplo, 1_000_000, 0x1EEE_FFFF, 0o273, 0b100101. Em caminhos SQL/JSON (e em JavaScript, mas não no SQL) não deve haver um separador de sublinhado após o prefixo.

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 Seção 4.1.2.1). 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 contra-barra 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 contra-barra, deve ser escrito \\. Outras sequências especiais de contra-barra incluem aquelas reconhecidas em cadeias de caracteres JavaScript: \b, \f, \n, \r, \t, \v para vários caracteres de controle ASCII, \xNN para um código de caractere escrito com apenas dois dígitos hexadecimais, \uNNNN para um caractere Unicode identificado por seu ponto de código hexadecimal de 4 dígitos, e \u{N...} para um ponto de código de caractere Unicode 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:

Para mais detalhes sobre o uso de expressões jsonpath com funções de consulta SQL/JSON, veja a Seção 9.16.2.