Lição 3.6 · Tempo de leitura: ~8 min

As funções JSON permitem que uma consulta olhe dentro de um documento JSON guardado numa coluna: ler um valor pelo seu caminho, filtrar linhas por ele, contar os itens de um array ou transformar linhas em JSON para uma aplicação. Nesta lição você vai ler valores com JSON_VALUE, JSON_EXTRACT e o operador ->>, filtrar por eles, trabalhar com arrays e montar JSON com JSON_OBJECT e JSON_ARRAYAGG. Os exemplos rodam em vários bancos de dados do site, porque cada banco dá nomes um pouco diferentes a essas funções.

Funções JSON em SQL

JSON numa coluna da tabela

Às vezes uma linha tem atributos que mudam de linha para linha, e a tabela os guarda num único documento JSON. A tabela machine do banco SQLite guarda detalhes assim:

MySQL e MariaDB têm o tipo JSON, o PostgreSQL tem json e jsonb, SQLite e SQL Server guardam JSON como texto. Em todos eles as funções JSON tratam o documento como dados estruturados, não como uma string comum.

Lendo um valor por caminho

Um valor dentro do documento é indicado por um caminho: $ é o documento inteiro, $.acquired é a chave acquired, $[0] é o primeiro item de um array, $[0].source é a chave source desse item.

SQLite:

SELECT name, json_extract(details, '$.acquired') AS acquired
FROM machine;

Resultado: WY401 — 2023-05-01, Inphormex — 2021-07-15, AutoPlate 9000 — NULL. Uma chave ausente resulta em NULL, não em erro.

SQLite e MySQL também têm uma forma curta, o operador ->>: details ->> '$.acquired' retorna os mesmos valores.

No MySQL e no MariaDB há duas funções, e a diferença importa:

SELECT JSON_EXTRACT('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS extracted,
       JSON_VALUE('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS value;

Resultado: extracted é "ACADEMY DINOSAUR" com as aspas — continua sendo um trecho de JSON; value é simplesmente ACADEMY DINOSAUR.

JSON_EXTRACT retorna JSON, então para comparar com uma string ele é envolvido em JSON_UNQUOTE. JSON_VALUE retorna logo um valor comum; o SQL Server usa a mesma função.

Filtrando por um valor dentro do JSON

Uma função JSON pode ser usada no WHERE como qualquer outra expressão. Como uma chave ausente resulta em NULL, IS NULL e IS NOT NULL encontram os documentos sem a chave e com ela.

SQLite:

SELECT name, json_extract(details, '$.refurbished') AS refurbished
FROM machine
WHERE json_extract(details, '$.refurbished') IS NOT NULL;

Resultado: uma máquina, Inphormex, recondicionada em 2023-10-22.

Arrays JSON

No banco University (MariaDB), research_projects.funding guarda um array de financiamentos: [{ "source": "Private Foundation", "amount": 244542.96, ...}], ou [] para um projeto sem financiamento. JSON_LENGTH conta os itens, e um caminho com índice lê um deles:

SELECT project_id, JSON_LENGTH(funding) AS grants,
       JSON_UNQUOTE(JSON_EXTRACT(funding, '$[0].source')) AS first_source
FROM research_projects
ORDER BY project_id
LIMIT 3;

Resultado: o projeto 1 tem 1 financiamento da Private Foundation; os projetos 2 e 3 têm 0, e a primeira fonte deles é NULL.

Para verificar se algum item de um array tem um valor, MySQL e MariaDB têm JSON_SEARCH, que retorna o caminho do valor encontrado ou NULL:

SELECT COUNT(*) AS nasa_projects
FROM research_projects
WHERE JSON_SEARCH(funding, 'one', 'NASA', NULL, '$[*].source') IS NOT NULL;

Resultado: 30 projetos têm financiamento da NASA. `$[].source` significa "o source de cada item".*

PostgreSQL: os operadores -> e ->>

O PostgreSQL lê JSON com operadores em vez de caminhos: -> retorna um valor JSON, ->> retorna texto. No banco Bookings, os nomes dos aeroportos são guardados em vários idiomas como jsonb, por exemplo { "en": "Yakutsk Airport", "ru": "Якутск"}:

SELECT airport_code, airport_name ->> 'en' AS name, city ->> 'en' AS city
FROM airports_data
ORDER BY airport_code
LIMIT 3;

Resultado: AAQ — Anapa Vityazevo Airport, Anapa; ABA — Abakan Airport, Abakan; AER — Sochi International Airport, Sochi.

Montando JSON a partir de linhas

A tarefa oposta é retornar um resultado como JSON, por exemplo para uma aplicação web. JSON_OBJECT monta um objeto a partir de pares de chave e valor.

MySQL, Sakila:

SELECT JSON_OBJECT('id', film_id, 'title', title, 'rating', rating) AS film
FROM film
ORDER BY film_id
LIMIT 2;

Resultado: { "id": 1, "title": "ACADEMY DINOSAUR", "rating": "PG"} e { "id": 2, "title": "ACE GOLDFINGER", "rating": "G"}.

JSON_ARRAYAGG é uma função de agregação: ela junta os valores de muitas linhas num único array.

SELECT JSON_ARRAYAGG(name) AS categories
FROM category;

Resultado: uma linha com um array das 16 categorias: ["Action", "Animation", "Children", ...].

No PostgreSQL o mesmo é feito por json_build_object e json_agg:

SELECT json_build_object('code', airport_code, 'city', city ->> 'en') AS airport
FROM airports_data
ORDER BY airport_code
LIMIT 2;

Resultado: { "code" : "AAQ", "city" : "Anapa"} e { "code" : "ABA", "city" : "Abakan"}.

Funções JSON em diferentes bancos de dados

Quando guardar JSON

JSON é prático para atributos que mudam entre as linhas ou que chegam de um sistema externo como documento. Mas um valor dentro do JSON é mais difícil de validar, juntar e indexar do que uma coluna. Se a mesma chave está em todas as linhas e é usada em filtros, ela merece uma coluna própria.

Principais conclusões:

• Um caminho indica um valor dentro do JSON: $.key, $[0], $[0].key, $[*].key.
• JSON_VALUE (MySQL, MariaDB, SQL Server), json_extract e ->> (SQLite, MySQL) e ->> (PostgreSQL) leem um valor como valor comum.
• JSON_EXTRACT no MySQL e no MariaDB retorna JSON: uma string vem entre aspas e precisa de JSON_UNQUOTE.
• Uma chave ausente resulta em NULL, então IS NULL e IS NOT NULL filtram documentos pela presença de uma chave.
• JSON_OBJECT monta um objeto a partir de colunas, JSON_ARRAYAGG junta linhas num array; o PostgreSQL tem json_build_object e json_agg.

Perguntas frequentes

Qual a diferença entre JSON_EXTRACT e JSON_VALUE?

JSON_EXTRACT retorna um trecho de JSON: uma string vem com aspas, um objeto ou array como texto JSON. JSON_VALUE retorna um único valor escalar como texto comum, pronto para comparar ou exibir.

O que uma função JSON retorna quando a chave não existe?

NULL. Por isso WHERE json_extract(details, '$.refurbished') IS NULL encontra os documentos sem essa chave.

Qual a diferença entre -> e ->> no PostgreSQL?

-> retorna um valor JSON, ->> retorna texto. Use -> para entrar mais fundo num documento e ->> para obter o valor final.

Perguntas de entrevista

Quando você guardaria dados numa coluna JSON em vez de colunas separadas?

Quando os atributos mudam de linha para linha, raramente são usados em filtros ou chegam como documento pronto de outro sistema. Valores que toda linha tem e que são usados em filtros, junções ou restrições pertencem a colunas comuns.

Como encontrar linhas cujo array JSON contém um valor?

No MySQL e no MariaDB com JSON_SEARCH(doc, 'one', 'value', NULL, '$[*].key') IS NOT NULL ou JSON_CONTAINS; no PostgreSQL com o operador de contenção @> em jsonb.

Como retornar o resultado de uma consulta como JSON?

Monte objetos com JSON_OBJECT (json_build_object no PostgreSQL) e junte-os num array com JSON_ARRAYAGG (json_agg); no SQL Server acrescenta-se FOR JSON PATH à consulta.