Tim's questions

Versões do PostgreSQL : minha instalação local 11.3 e o violino abaixo está em 10.0. Ambos se comportam da mesma forma.

Eu tenho um esquema de páginas, cada página tem seções e cada seção pode ter um tipo diferente de conteúdo. Quando consulto uma página, desejo exibir tudo o que há sobre essa página em um documento JSON.

Estou usando CTE's para json_agg()vários conteúdos por seção. Finalmente, junto as seções json_object_agg()para mapear os títulos das seções para o conteúdo das seções.

O problema: json_object_agg() gera um erro quando uma página não possui nenhuma seção. Eu verifiquei a culpa usando um regular json_agg()sem títulos de seção. O erro exato:

error: field name must not be null

O que eu quero: Em qualquer caso, não é um erro. Eu não quero fazer tratamento de erros personalizado no lado do receptor. Seria ainda melhor se a consulta pudesse retornar um JSON Nullno lugar do json_object_agg()caso não houvesse seções, mas isso é opcional. (Outras soluções elegantes são bem-vindas)

Os documentos

Provavelmente a documentação está incompleta ou eu perdi alguma coisa. Somente para referência.

Em expressões agregadas diz (ênfase minha):

A maioria das funções de agregação ignora entradas nulas, de modo que as linhas nas quais uma ou mais das expressões produzem nulo são descartadas. Isso pode ser considerado verdadeiro, a menos que especificado de outra forma , para todos os agregados internos.

E em funções agregadas , json_object_agg()sem comentários sobre não manipular null:

agrega pares de nome/valor como um objeto JSON

Fiddle com argumento de domínio defeituoso. Alterar o domínio para as outras opções faz com que funcione bem. Também usar um domínio não existente funciona bem e retorna 0 linhas.

Consulta

with secs as (
    select p.page_id, p.domain, s.section_id as sid, s.title as title
    from pages p
    left join sections s on p.page_id = s.page_id
    where p.domain = 'bar.com'
),
txt as (
    select
        sid,
        json_agg(
            json_build_object(
                'Pos', pos,
                'Text', content
            )
            order by pos asc
        ) as txts
    from texts
    join secs on sid = section_id
    group by sid
),
img as (
    select
        sid,
        json_agg(
            json_build_object(
                'Pos', pos,
                'Image', image
            )
            order by pos asc
        ) as imgs
    from images
    join secs on sid = section_id
    group by sid
)
select
    json_build_object(
        'ID', s.page_id,
        'Domain', domain,
        'Sections', json_object_agg ( -- Error occurs here
            s.title,
            json_build_object(
                'ID', s.sid,
                'Texts', t.txts,
                'Images', i.imgs
            )
            order by s.sid asc
        )
    )
from secs s
left join txt t on s.sid = t.sid
left join img i on s.sid = i.sid
group by s.page_id, domain;

Esquema

create table pages (
    page_id serial primary key,
    domain text unique not null
);

create table sections (
    section_id serial primary key,
    title text not null,
    page_id int references pages
);

create table texts (
    section_id int references sections,
    pos int not null,
    content text not null,
    primary key (section_id, pos)
);

create table images (
    section_id int references sections,
    pos int not null,
    image text not null,
    primary key (section_id, pos)
);

-- spanac.com will have 3 sections with texts and images in each, various amounts
insert into pages (domain) values ('spanac.com');
-- foo.com has 1 empty section
insert into pages (domain) values ('foo.com');
-- bar.com has no sections
insert into pages (domain) values ('bar.com');

-- spanac.com

with s as (
    insert into sections (page_id, title) select page_id, 'first' from pages where domain = 'spanac.com' returning section_id
),
t1 as (
    insert into texts (section_id, pos, content) select section_id, 1, 'spanac one.one' from s
),
t2 as (
    insert into texts (section_id, pos, content) select section_id, 2, 'spanac one.two' from s
),
i1 as (
    insert into images (section_id, pos, image) select section_id, 1, 's11.jpg' from s
)
insert into images (section_id, pos, image) select section_id, 2, 's12.jpg' from s;

with s as (
    insert into sections (page_id, title) select page_id, 'second' from pages where domain = 'spanac.com' returning section_id
),
t1 as (
    insert into texts (section_id, pos, content) select section_id, 1, 'spanac two.one' from s
),
t2 as (
    insert into texts (section_id, pos, content) select section_id, 2, 'spanac two.two' from s
),
i1 as (
    insert into images (section_id, pos, image) select section_id, 1, 's21.jpg' from s
)
insert into images (section_id, pos, image) select section_id, 2, 's22.jpg' from s;

with s as (
    insert into sections (page_id, title) select page_id, 'third' from pages where domain = 'spanac.com' returning section_id
),
t1 as (
    insert into texts (section_id, pos, content) select section_id, 1, 'Spanac three.one' from s
)
insert into images (section_id, pos, image) select section_id, 1, 's31.jpg' from s;

-- foo.com

insert into sections (page_id, title) select page_id, 'empty' from pages where domain = 'foo.com';

Usando o postgreSQL 10 ou 11, seria seguro criar tabelas temporárias com o mesmo nome, cada uma carregando dados diferentes? Não pretendo compartilhar os dados entre nenhuma sessão/conexão/transação. O nome em si não é importante, mas gostaria de me proteger do problema de gerar nomes aleatórios para cada consulta simultânea que precisa dessa tabela intermediária.

A tabela temporária é usada apenas em SELECTconsultas diferentes. Ele será descartado no final de cada transação com a ON COMMIT DROPcláusula.

A documentação do PostgreSQL afirma:

As tabelas permanentes existentes com o mesmo nome não são visíveis para a sessão atual enquanto a tabela temporária existir, a menos que sejam referenciadas com nomes qualificados pelo esquema.

Não está claro para mim se isso também se aplica a tabelas temporárias.

Versão: PostgreSQL 10.3

O PostgreSQL suporta a saída de resultados JSON. Esta parece ser uma combinação de ouro para minhas demandas de aplicativos.

Agora, antes de virar o código da minha aplicação de cabeça para baixo para benchmarks e testes, fiz algumas consultas com alguns EXPLAIN ANALYZEe quase não custou nada a mais.

Exemplo simples:

explain analyze
    select id, name, phonenumber, email 
    from contacts
        order by name
        limit 27
        offset 2;
                       QUERY PLAN
══════════════════════════════════════════════════════════════
 Limit  (cost=62.61..62.68 rows=27 width=61) \
        (actual time=0.223..0.226 rows=27 loops=1)...

Explicação completa para a consulta simples aqui .

explain analyze
    select json_agg(contacts) as contacts
    from(
        select id, name, phonenumber, email 
            from contacts
                order by name
                limit 27
                offset 2
        ) as contacts;
                       QUERY PLAN
══════════════════════════════════════════════════════════════
 Aggregate  (cost=63.02..63.03 rows=1 width=32) \
            (actual time=0.272..0.272 rows=1 loops=1)...

Explicação completa para a consulta JSON aqui .

Eu sou muito novo nisso (desempenho do SQL) e parece bom demais para ser verdade. As seguintes conclusões estão corretas?:

1. Olhando para o tempo total de execução, o impacto na saída JSON é relativamente baixo para o exemplo fornecido
2. Descobri que o tempo "excluído" para agregação JSON é diretamente proporcional ao tamanho do conjunto de resultados. (Ao aumentar a opção de limite). No entanto, a mesma consequência proporcional estará no lado do cliente se o conjunto de resultados for analisado lá.
3. Com consultas mais complexas, como junções, a sobrecarga relativa (%) da agregação JSON será menor.