SQL JSON

A Loja Aurora vai ganhar um aplicativo de celular. O programador do aplicativo pediu à Ana: "me mande os pedidos em JSON". JSON é o formato de texto que quase todo aplicativo e site usa para trocar dados. Nesta aula você vai fazer o próprio banco montar esse texto, ler um JSON guardado numa coluna e desmontar uma lista JSON em linhas de tabela. No fim, você monta o JSON completo de um pedido, com todos os seus itens, numa consulta só.

Todos os exemplos rodam de verdade no seu navegador, no SQLite 3.50 com o banco da Loja Aurora (aula O Banco da Loja Aurora). As funções de JSON já vêm prontas no SQLite desde a versão 3.38: não é preciso instalar nada.

A ideia: uma ficha que viaja

Uma linha de tabela só faz sentido junto do cabeçalho: o valor 12.35 sozinho não diz nada; é a coluna preco que dá o sentido. Quando o dado sai do banco e vai para um aplicativo, ele precisa levar o "cabeçalho" junto. É como trocar a planilha por uma ficha preenchida, em que cada valor vem com o nome do campo ao lado:

{"id":1,"nome":"Caderno Pautado 96 folhas","preco":12.35}

Isso é JSON (de JavaScript Object Notation, "notação de objetos do JavaScript"; pronuncia-se "jêison"). Apesar do nome, qualquer linguagem lê JSON. Ele tem só três peças:

Para o SQLite, um JSON é só um texto (tipo TEXT). O que as funções desta aula fazem é escrever esse texto sem erro (com as aspas e vírgulas no lugar) e saber andar dentro dele.

1. Montando um objeto: json_object()

Primeiro, três produtos do jeito de sempre, em tabela:

SELECT id, nome, preco
FROM produtos
WHERE id <= 3;

Agora os mesmos três, cada um virando uma ficha JSON:

SELECT json_object('id', id, 'nome', nome, 'preco', preco) AS produto
FROM produtos
WHERE id <= 3;

json_object(

"objeto JSON": monta um objeto {…}. Os argumentos vêm em pares: nome do campo, valor do campo, nome, valor…

'id', id

O primeiro par. 'id' (com aspas simples) é o nome que o campo vai ter no JSON; id (sem aspas) é a coluna de onde vem o valor.

'nome', nome

Segundo par. O SQLite põe as aspas duplas em volta do texto sozinho.

'preco', preco

Terceiro par. Número vai sem aspas no JSON: 12.35.

AS produto

O apelido da coluna (aula Apelidos (AS)). O resultado é uma coluna só, com um texto JSON por linha.

O nome do campo no JSON não precisa ser igual ao da coluna: json_object('valor', preco) gera {"valor":12.35}. Quem escolhe é você (ou o programador do aplicativo, que vai ler o JSON).

E o NULL? Ele vira o null do JSON. O cliente 7 não tem email (é um dos casos do banco):

SELECT json_object('nome', nome, 'email', email, 'vip', vip) AS cliente
FROM clientes
WHERE id IN (1, 7);

Repare também no "vip":1: no banco da Aurora o VIP é guardado como 0 ou 1, e é esse número que vai para o JSON. O SQLite não tem um tipo "verdadeiro/falso" (aula Os Tipos no SQLite), então ele não adivinha que você queria true.

SELECT nome, cidade
FROM clientes
WHERE vip = 1;

SELECT json_object('nome', nome, 'cidade', cidade) AS cliente
FROM clientes
WHERE vip = 1;

2. Montando uma lista: json_array()

O json_array() ("lista JSON") junta os valores que você passar, na ordem, entre colchetes:

SELECT json_array('Teresina', 'Fortaleza', 42, 4.9, NULL) AS lista;

Os textos ganharam aspas duplas, os números ficaram sem aspas e o NULL virou null. Uma lista pode ter valores de tipos diferentes misturados.

Um dentro do outro

O valor de um campo pode ser outra lista (ou outro objeto). Basta colocar uma função dentro da outra:

SELECT json_object('nome', 'Ana', 'gosta', json_array('livros', 'jardim')) AS ficha;

O json_object() percebeu que o valor de gosta já era JSON (veio de uma função de JSON) e o encaixou como lista. Guarde isso: em Quando dá errado você vai ver o que acontece quando o valor é um texto que só parece JSON.

3. Lendo um pedaço: json_extract()

Agora o caminho contrário: você tem um JSON e quer um valor lá de dentro. Para isso, diga o caminho (path) até ele:

SELECT json_extract('{"nome":"Ana","cidade":"Teresina","gosta":["livros","jardim"]}', '$.cidade') AS cidade;

json_extract(

"extrair do JSON". Recebe o JSON e o caminho, e devolve o valor que estiver lá.

'{"nome":"Ana",…}'

O JSON, como texto do SQL (aspas simples por fora).

'$.cidade'

O caminho. O $ é "o JSON inteiro"; .cidade é "o campo cidade dele". Leia como "do JSON, pegue a cidade".

Os caminhos podem descer quantos níveis precisar. Numa lista, a posição vai entre colchetes, e a contagem começa do zero:

SELECT
  json_extract('{"nome":"Ana","gosta":["livros","jardim"]}', '$.gosta')       AS lista,
  json_extract('{"nome":"Ana","gosta":["livros","jardim"]}', '$.gosta[0]')    AS primeiro,
  json_extract('{"nome":"Ana","gosta":["livros","jardim"]}', '$.gosta[#-1]')  AS ultimo,
  json_extract('{"nome":"Ana","gosta":["livros","jardim"]}', '$.idade')       AS idade;

Repare na última coluna do resultado: pedir um campo que não existe não é erro, dá NULL. Isso é bom (um cliente sem idade não derruba a consulta) e é perigoso (um nome de campo digitado errado também dá NULL calado).

4. Os atalhos -> e ->>

Escrever json_extract(…) o tempo todo cansa. O SQLite tem dois operadores que fazem quase a mesma coisa, com uma setinha: -> (uma seta) e ->> (seta dupla). A diferença entre eles é o que volta:

SELECT
  '{"nome":"Ana"}' -> '$.nome'   AS seta,
  '{"nome":"Ana"}' ->> '$.nome'  AS seta_dupla,
  '{"nome":"Ana"}' ->> 'nome'    AS sem_cifrao;

->

Devolve o pedaço como JSON. Um texto no JSON tem aspas duplas, então volta "Ana", com as aspas. Serve para quando o resultado vai continuar sendo JSON (para encaixar em outro JSON, por exemplo).

->>

Devolve o pedaço como valor do SQL: o texto Ana, sem aspas; um número vira número de verdade. É o que você quer para comparar, somar, ordenar.

'nome' (sem $.)

Atalho dos atalhos: com as setas, um nome simples vale como '$.nome', e um número como 2 vale como '$[2]'. (No json_extract() isso não vale; lá o $ é obrigatório.)

O tipo do que volta mostra a diferença sem sombra de dúvida. A função typeof() diz o tipo de um valor:

SELECT
  typeof(json_extract('{"n":5}', '$.n')) AS com_extract,
  typeof('{"n":5}' -> '$.n')             AS com_seta,
  typeof('{"n":5}' ->> '$.n')            AS com_seta_dupla;

O json_extract() e o ->> devolvem o número 5 (integer); o -> devolve o texto JSON 5 (text). Para o dia a dia, a regra é simples: use ->>. Deixe o -> para quando você quer um pedaço de JSON para encaixar em outro JSON.

5. JSON guardado numa coluna

Até aqui o JSON estava escrito na própria consulta. Na vida real ele fica guardado numa coluna. A Ana quer guardar os carrinhos do aplicativo, as compras que o cliente começou e ainda não fechou. Cada carrinho chega do aplicativo como um JSON, com a origem, o cupom (se houver) e a lista de itens. Ela cria uma tabela com uma coluna TEXT para o JSON:

CREATE TABLE carrinhos (
  id         INTEGER PRIMARY KEY,
  cliente_id INTEGER REFERENCES clientes(id),
  dados      TEXT CHECK (json_valid(dados))
);

INSERT INTO carrinhos (id, cliente_id, dados) VALUES
  (1, 3,  '{"origem":"app","cupom":"AURORA10","itens":[{"produto_id":1,"qtd":2},{"produto_id":5,"qtd":1}]}'),
  (2, 18, '{"origem":"site","itens":[{"produto_id":3,"qtd":10}]}'),
  (3, 27, '{"origem":"app","itens":[]}');

SELECT id,
       dados ->> '$.origem'               AS origem,
       dados ->> '$.cupom'                AS cupom,
       json_array_length(dados, '$.itens') AS n_itens
FROM carrinhos;

dados TEXT

O JSON fica numa coluna de texto comum. O SQLite não tem um tipo "JSON" separado.

CHECK (json_valid(dados))

A trava (aula CHECK): json_valid() devolve 1 se o texto é um JSON válido e 0 se não é. Com ela, o banco recusa JSON quebrado na entrada, em vez de você descobrir o problema meses depois.

dados ->> '$.origem'

Lê o campo origem de cada linha. O carrinho 2 não tem cupom, então ali vem NULL.

json_array_length(dados, '$.itens')

"Tamanho da lista": quantos itens tem a lista itens. O carrinho 3 está vazio: [] tem 0 itens.

Com o ->>, um campo do JSON entra no WHERE como qualquer coluna:

CREATE TABLE carrinhos (id INTEGER PRIMARY KEY, cliente_id INTEGER, dados TEXT CHECK (json_valid(dados)));
INSERT INTO carrinhos VALUES
  (1, 3,  '{"origem":"app","cupom":"AURORA10","itens":[{"produto_id":1,"qtd":2},{"produto_id":5,"qtd":1}]}'),
  (2, 18, '{"origem":"site","itens":[{"produto_id":3,"qtd":10}]}'),
  (3, 27, '{"origem":"app","itens":[]}');

SELECT id, cliente_id
FROM carrinhos
WHERE dados ->> '$.origem' = 'app';

Mudando um campo: json_set()

Para mudar um pedaço do JSON guardado, não é preciso reescrever o texto todo. O json_set() devolve o JSON com o campo trocado (ou acrescentado, se ele não existia), e o UPDATE grava:

CREATE TABLE carrinhos (id INTEGER PRIMARY KEY, cliente_id INTEGER, dados TEXT CHECK (json_valid(dados)));
INSERT INTO carrinhos VALUES
  (2, 18, '{"origem":"site","itens":[{"produto_id":3,"qtd":10}]}');

UPDATE carrinhos
SET dados = json_set(dados, '$.cupom', 'FRETEGRATIS')
WHERE id = 2;

SELECT dados FROM carrinhos WHERE id = 2;

O campo cupom não existia e foi acrescentado no fim. A família tem quatro irmãos, que diferem só no que fazem quando o campo já existe ou não:

SELECT
  json_set('{"a":1}', '$.b', 2)          AS set_novo,
  json_insert('{"a":1}', '$.a', 9)       AS insert_existente,
  json_replace('{"a":1}', '$.a', 9)      AS replace_existente,
  json_remove('{"a":1,"b":2}', '$.b')    AS remove;

CREATE TABLE carrinhos (id INTEGER PRIMARY KEY, cliente_id INTEGER, dados TEXT CHECK (json_valid(dados)));
INSERT INTO carrinhos VALUES
  (1, 3,  '{"origem":"app","cupom":"AURORA10","itens":[{"produto_id":1,"qtd":2},{"produto_id":5,"qtd":1}]}'),
  (2, 18, '{"origem":"site","itens":[{"produto_id":3,"qtd":10}]}'),
  (3, 27, '{"origem":"app","itens":[]}');

SELECT id
FROM carrinhos
WHERE dados -> '$.origem' = 'site';

CREATE TABLE carrinhos (id INTEGER PRIMARY KEY, cliente_id INTEGER, dados TEXT CHECK (json_valid(dados)));
INSERT INTO carrinhos VALUES
  (1, 3,  '{"origem":"app","cupom":"AURORA10","itens":[{"produto_id":1,"qtd":2},{"produto_id":5,"qtd":1}]}'),
  (2, 18, '{"origem":"site","itens":[{"produto_id":3,"qtd":10}]}'),
  (3, 27, '{"origem":"app","itens":[]}');

SELECT id
FROM carrinhos
WHERE dados ->> '$.origem' = 'site';

6. Da lista para as linhas: json_each()

Uma lista JSON numa célula é ótima para guardar, e ruim para consultar: você não consegue fazer JOIN com uma lista. O json_each() ("cada um do JSON") resolve isso: ele desmonta a lista e devolve uma linha para cada item, como se fosse uma tabela:

SELECT * FROM json_each('["livros","jardim","papelaria"]');

Ele aparece no FROM, no lugar de uma tabela: é uma função de tabela (table-valued function). As colunas que importam no começo são duas:

key

"Chave": a posição do item na lista (0, 1, 2…) ou, num objeto, o nome do campo.

value

"Valor": o próprio item, já como valor do SQL.

type, atom, id, parent, fullkey, path

Detalhes para JSON complicado: o tipo do item (text, integer, object…), o caminho completo até ele (fullkey: $[0], $[1]…). Você raramente vai precisar.

Num objeto, cada campo vira uma linha, com o nome em key:

SELECT key, value
FROM json_each('{"nome":"Ana","cidade":"Teresina","vip":1}');

Os itens de cada carrinho

Agora o uso de verdade: abrir a lista itens de cada carrinho e juntar com a tabela produtos para saber o nome e o preço. O segundo argumento do json_each() é o caminho da lista dentro do JSON:

CREATE TABLE carrinhos (id INTEGER PRIMARY KEY, cliente_id INTEGER, dados TEXT CHECK (json_valid(dados)));
INSERT INTO carrinhos VALUES
  (1, 3,  '{"origem":"app","cupom":"AURORA10","itens":[{"produto_id":1,"qtd":2},{"produto_id":5,"qtd":1}]}'),
  (2, 18, '{"origem":"site","itens":[{"produto_id":3,"qtd":10}]}'),
  (3, 27, '{"origem":"app","itens":[]}');

SELECT c.id                  AS carrinho,
       p.nome,
       i.value ->> 'qtd'     AS qtd,
       p.preco
FROM carrinhos c
JOIN json_each(c.dados, '$.itens') i
JOIN produtos p ON p.id = i.value ->> 'produto_id';

JOIN json_each(c.dados, '$.itens') i

Para cada carrinho c, desmonta a lista itens dele. Cada item vira uma linha com o apelido i. O carrinho 3 tem a lista vazia, então não gera linha nenhuma.

i.value

O item da vez, que é um objeto: {"produto_id":1,"qtd":2}.

i.value ->> 'qtd'

Dentro do item, o campo qtd, como número.

ON p.id = i.value ->> 'produto_id'

Liga o item ao produto pelo id, como um JOIN comum (aula INNER JOIN).

Virou tabela, então todo o resto do SQL funciona. O total de cada carrinho, com SUM e GROUP BY:

CREATE TABLE carrinhos (id INTEGER PRIMARY KEY, cliente_id INTEGER, dados TEXT CHECK (json_valid(dados)));
INSERT INTO carrinhos VALUES
  (1, 3,  '{"origem":"app","cupom":"AURORA10","itens":[{"produto_id":1,"qtd":2},{"produto_id":5,"qtd":1}]}'),
  (2, 18, '{"origem":"site","itens":[{"produto_id":3,"qtd":10}]}'),
  (3, 27, '{"origem":"app","itens":[]}');

SELECT c.id AS carrinho,
       ROUND(SUM(p.preco * (i.value ->> 'qtd')), 2) AS total
FROM carrinhos c
JOIN json_each(c.dados, '$.itens') i
JOIN produtos p ON p.id = i.value ->> 'produto_id'
GROUP BY c.id;

O carrinho 1 dá 2 × 12.35 + 1 × 89.99 = 114.69; o carrinho 2, 10 × 1.15 = 11.5.

SELECT key, value, type
FROM json_each('["livros", "jardim", 42, null]');

7. Das linhas para a lista: json_group_array()

O caminho de volta. O json_group_array() é uma função agregada, da mesma família do COUNT() e do SUM() (aula O que são Funções Agregadas): ela junta os valores de várias linhas numa lista JSON só.

SELECT json_group_array(nome) AS categorias
FROM categorias;

Oito linhas viraram uma lista com oito nomes. Como toda agregada, ela combina com GROUP BY (aula GROUP BY): uma lista por grupo. Os produtos de cada um dos três primeiros pedidos:

SELECT pedido_id, json_group_array(produto_id) AS produtos
FROM itens_pedido
WHERE pedido_id <= 3
GROUP BY pedido_id;

Quer a lista em ordem? Ponha um ORDER BY dentro dos parênteses da função:

SELECT json_group_array(nome ORDER BY nome) AS categorias
FROM categorias;

Um objeto em vez de lista: json_group_object()

O irmão json_group_object(nome, valor) junta as linhas num objeto: o primeiro argumento vira o nome do campo e o segundo, o valor. Os produtos da categoria Papelaria, com seus preços:

SELECT json_group_object(nome, preco) AS precos
FROM produtos
WHERE categoria_id = 1;

8. O JSON de um pedido com seus itens

Agora o pedido do programador do aplicativo: o pedido 1 inteiro, num JSON só, com o cliente e a lista de itens. Primeiro, as duas partes separadas, em tabela, para ver o que vai entrar:

SELECT p.id, p.data, p.frete, c.nome AS cliente
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
WHERE p.id = 1;

SELECT pr.nome, ip.quantidade, ip.preco_unitario
FROM itens_pedido ip
JOIN produtos pr ON pr.id = ip.produto_id
WHERE ip.pedido_id = 1;

Os itens viram uma lista de objetos com json_group_array(json_object(…)): o json_object() monta a ficha de cada item, e o json_group_array() junta as fichas numa lista:

SELECT json_group_array(
         json_object('produto', pr.nome, 'qtd', ip.quantidade, 'preco', ip.preco_unitario)
       ) AS itens
FROM itens_pedido ip
JOIN produtos pr ON pr.id = ip.produto_id
WHERE ip.pedido_id = 1;

Por fim, essa lista entra como o campo itens do objeto do pedido, por uma subconsulta (aula Subconsultas) que roda para cada pedido:

SELECT json_object(
  'pedido',  p.id,
  'data',    p.data,
  'cliente', c.nome,
  'frete',   p.frete,
  'itens',   (SELECT json_group_array(
                       json_object('produto', pr.nome,
                                   'qtd',     ip.quantidade,
                                   'preco',   ip.preco_unitario))
              FROM itens_pedido ip
              JOIN produtos pr ON pr.id = ip.produto_id
              WHERE ip.pedido_id = p.id)
) AS pedido_json
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
WHERE p.id = 1;

1. A consulta de fora pega o pedido 1 e o nome do cliente (José Ribamar Silva), como sempre.
2. Para esse pedido, a subconsulta entre parênteses monta a lista de itens. O WHERE ip.pedido_id = p.id liga a subconsulta ao pedido de fora.
3. O json_object() de fora junta tudo. Como o valor de itens veio de uma função de JSON, ele entra como lista de verdade, e não como texto entre aspas.

Esse texto pode ir direto para o aplicativo. Para ler com calma, o json_pretty() devolve o mesmo JSON com quebras de linha e recuo. Ele é recente (SQLite 3.46) e serve para gente ler, não para guardar: SELECT json_pretty(json_object('pedido', 1, 'itens', json_array(1, 2)));.

9. No terminal: o modo JSON do sqlite3

O programa sqlite3 (aula Comandos de Ponto) tem um jeito de mostrar qualquer resultado como JSON, sem função nenhuma: a opção -json (ou o comando .mode json dentro dele). Cada linha vira um objeto, e o resultado inteiro vira uma lista:

$ sqlite3 -json loja.db "SELECT id, nome, preco FROM produtos WHERE id <= 2;"
[{"id":1,"nome":"Caderno Pautado 96 folhas","preco":12.34999999999999965},
{"id":2,"nome":"Caneta Esferográfica Azul","preco":2.790000000000000035}]
$ sqlite3 -json loja.db "SELECT nome, email FROM clientes WHERE id = 7;"
[{"nome":"Luís Otávio Brito","email":null}]
$ sqlite3 loja.db "SELECT json_object('id', id, 'preco', preco) FROM produtos WHERE id <= 2;"
{"id":1,"preco":12.35}
{"id":2,"preco":2.79}

Repare no preço: o modo JSON escreveu 12.34999999999999965, enquanto o json_object() escreveu 12.35. Não é erro de nenhum dos dois. Um REAL é guardado em binário, e 12.35 não tem representação exata em binário; o modo JSON mostra todos os dígitos, e as funções de JSON escrevem o número mais curto que volta ao mesmo valor. Para dinheiro, prefira montar o JSON com as funções. O .mode json é só do programa sqlite3: no editor do guia, ele não existe.

Formas diferentes de fazer a mesma coisa

Ler o campo origem do carrinho, de três jeitos:

SELECT
  json_extract('{"origem":"app"}', '$.origem') AS com_extract,
  '{"origem":"app"}' ->> '$.origem'            AS com_seta_dupla,
  '{"origem":"app"}' ->> 'origem'              AS curto;

E a lista de produtos do pedido 1, montada com json_group_array() ou escrita à mão com json_array():

SELECT json_group_array(produto_id) AS agregada
FROM itens_pedido WHERE pedido_id = 1;

SELECT json_array(1, 2, 3) AS a_mao;

Quando dá errado

SELECT '{"nome":"Ana"}' -> '$.nome' = 'Ana'   AS com_seta,
       '{"nome":"Ana"}' ->> '$.nome' = 'Ana'  AS com_seta_dupla;

SELECT json('isso nao e json');

CREATE TABLE carrinhos (id INTEGER PRIMARY KEY, cliente_id INTEGER, dados TEXT CHECK (json_valid(dados)));
INSERT INTO carrinhos VALUES (4, 1, '{"origem":"app",');

SELECT json_extract('{"nome":"Ana"}', 'nome');

SELECT json_object('nome', nome, 'cidade') FROM clientes;

SELECT json_object("nome", 'Ana');

SELECT json_object('nome', 'Ana', 'gosta', '["livros","jardim"]') AS errado,
       json_object('nome', 'Ana', 'gosta', json('["livros","jardim"]')) AS certo;

-- PostgreSQL (ilustrativo)
SELECT id, dados ->> 'origem' AS origem
FROM carrinhos;

-- SQL Server (ilustrativo)
SELECT id, JSON_VALUE(dados, '$.origem') AS origem
FROM carrinhos;

Referência rápida

Exercícios

Missão final: o pedido 2 em JSON

SELECT p.id, c.nome, pr.nome, ip.quantidade
FROM pedidos p
JOIN clientes c      ON c.id = p.cliente_id
JOIN itens_pedido ip ON ip.pedido_id = p.id
JOIN produtos pr     ON pr.id = ip.produto_id
WHERE p.id = 2;

SELECT json_object(
  'pedido',  p.id,
  'cliente', c.nome,
  'itens',   (SELECT json_group_array(json_object('produto', pr.nome, 'qtd', ip.quantidade))
              FROM itens_pedido ip
              JOIN produtos pr ON pr.id = ip.produto_id
              WHERE ip.pedido_id = p.id)
) AS pedido_json
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
WHERE p.id = 2;

Se deu certo, a resposta tem uma linha só, com a Maria Clara Mendes e três itens: Marca-texto Amarelo, Mochila Escolar Azul (2 unidades) e Estojo Duplo. Troque o WHERE p.id = 2 por WHERE p.id <= 3 e veja três pedidos, cada um no seu JSON.

Resumo

• JSON é texto com objetos {"nome": valor} e listas […]. No SQLite, ele fica numa coluna TEXT.
• json_object('nome', valor, …) monta um objeto; json_array(…), uma lista. Uma função pode ir dentro da outra.
• Para ler: json_extract(j, '$.campo') ou j ->> 'campo', que devolvem valor do SQL. O -> devolve JSON (texto com aspas): não use para comparar.
• Campo que não existe dá NULL; caminho mal escrito dá bad JSON path; texto que não é JSON dá malformed JSON.
• CHECK (json_valid(dados)) barra JSON quebrado na entrada.
• json_each() desmonta uma lista em linhas (vai no FROM); json_group_array() e json_group_object() juntam linhas em JSON.
• O JSON de um pedido com itens: json_object(…, 'itens', (SELECT json_group_array(json_object(…)) …)).