Início / dba / Perguntas / 69406
Accepted
Alan Cor
Asked: 2014-07-01 17:21:23 +0800 CST

Buscar resultados aninhados como JSON PostgreSQL 9.2

  • 772

Considere as seguintes tabelas

Device
--------
id
name
type
--------

components
--------
id INT
type VARCHAR
--------

Manufacturers
-------------
id INT
name VARCHAR
country VARCHAR
-------------

Device_components
-----------------
deviceid REFERENCES Devices(id)
componentid REFERENCES Components(id)
-----------------

Component_Manufacturers
-----------------------
componentid REFERENCES Components(id)
manufacturerid REFERENCES Manufacturers(id)
-----------------------

Eu quero consultar o banco de dados para retornar algo assim:

{
 "id": 1,
  "name": "phone",
  "components": [
    {
      "id": 1,
      "type": "screen",
      "manufacturers": [
       {
         "id": 1,
          "name": "a",
          "country": "Germany"
        }
      ]
   },
   {
     "id": 2,
      "type": "keyboard",
      "manufacturers": [
        {
         "id": 1,
          "name": "a",
          "country": "UK"
        }
     ]
   }
  ]
}

Até agora estou selecionando cada tabela individualmente e depois montando o objeto JSON na minha aplicação.

Aqui estão alguns dados de exemplo:

CREATE TABLE IF NOT EXISTS Devices
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS Components
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS Manufacturers
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL,
    COUNTRY VARCHAR(40)
);  

CREATE TABLE IF NOT EXISTS Device_components
(
DeviceID INT REFERENCES Devices(ID),
ComponentID INT REFERENCES Components(ID)
);

CREATE TABLE IF NOT EXISTS Component_manufacturers
(
ComponentID INT REFERENCES Components(ID),
ManufacturerID INT REFERENCES Manufacturers(ID)
);

INSERT INTO Devices (NAME) VALUES ('phone');
INSERT INTO Devices (NAME) VALUES ('tablet');
INSERT INTO Devices (NAME) VALUES ('pc');

INSERT INTO Components (NAME) VALUES ('mouse');
INSERT INTO Components (NAME) VALUES ('camera');
INSERT INTO Components (NAME) VALUES ('screen');

INSERT INTO Manufacturers (NAME,Country) VALUES ('foo','france');
INSERT INTO Manufacturers (NAME,Country) VALUES ('bar','spain');
INSERT INTO Manufacturers (NAME,Country) VALUES ('baz','germany');

INSERT INTO Device_components VALUES (1,2);
INSERT INTO Device_components VALUES (1,3);
INSERT INTO Device_components VALUES (2,2);
INSERT INTO Device_components VALUES (2,3);
INSERT INTO Device_components VALUES (3,1);
INSERT INTO Device_components VALUES (3,2);
INSERT INTO Device_components VALUES (3,3);

INSERT INTO Component_manufacturers VALUES (1,1);
INSERT INTO Component_manufacturers VALUES (1,2);
INSERT INTO Component_manufacturers VALUES (1,3);
INSERT INTO Component_manufacturers VALUES (2,2);
INSERT INTO Component_manufacturers VALUES (3,3);
postgresql json

1 respostas

  1. Best Answer

    Tudo bem, parece que você deseja criar um gráfico a partir de uma árvore de junção. Isso é uma coisa bastante natural de querer fazer.

    Não é tão fácil como deveria ser em Pg, principalmente por causa da falta de suporte para apelidos de coluna no recordpseudo-tipo.

    Aqui está o que eu criei:

    SELECT row_to_json(r, true)
FROM (
    SELECT
        d.id,   
        d.name, 
        json_agg(c_row) AS components
    FROM devices d
    INNER JOIN device_components dc ON (dc.deviceid = d.id)
    INNER JOIN (
        SELECT  
            c.id,       
            c.name,     
            json_agg(m) AS manufacturers
        FROM components c
        INNER JOIN component_manufacturers cm ON (cm.componentid = c.id) 
        INNER JOIN manufacturers m ON (cm.manufacturerid = m.id) 
        GROUP BY c.id 
    ) c_row ON (c_row.id = dc.componentid)
    GROUP BY d.id
) r(id, name, component);

    A ideia geral aqui é, em todos os níveis de aninhamento de objetos em que você está produzindo uma matriz de objetos, use json_aggem um arquivo group_by. A json_aggfunção chama implicitamente row_to_jsonpara transformar um tipo de linha em json. Na SELECTcláusula, especifique aliases para as colunas sintéticas para que os nomes das chaves json estejam corretos quando a linha for alimentada no nível externo.

    Como o nível externo não é agregado, use row_to_jsonsobre uma subconsulta em vez de usar json_agg. Se você deseja um único resultado json em vez de um conjunto de linhas json, pode alterar row_to_jsonpara json_aggno nível externo.

    Eu testei apenas no 9.3, pois é o que instalei e o sqlfiddle parece estar tendo alguns problemas.

    Atualização : Infelizmente json_aggnão existe no 9.2 , foi adicionado no 9.3. No entanto, este é um caso bastante simples que você pode usar array_aggem vez disso, então isso deve funcionar em 9.2 :

    SELECT row_to_json(r, true)
FROM (
    SELECT
        d.id,   
        d.name, 
        array_agg(c_row) AS components
    FROM devices d
    INNER JOIN device_components dc ON (dc.deviceid = d.id)
    INNER JOIN (
        SELECT  
            c.id,       
            c.name,     
            array_agg(m) AS manufacturers
        FROM components c
        INNER JOIN component_manufacturers cm ON (cm.componentid = c.id) 
        INNER JOIN manufacturers m ON (cm.manufacturerid = m.id) 
        GROUP BY c.id 
    ) c_row ON (c_row.id = dc.componentid)
    GROUP BY d.id
) r(id, name, component);

    Idealmente, o PostgreSQL suportaria esse tipo de consulta de forma mais natural, sem as subconsultas, mas acho que precisaríamos de um novo tipo de junção, ou pelo menos algumas funções para usar com consultas laterais. De qualquer maneira , tudo planeja um plano de consulta civilizado (embora seja melhor com índices adequados):

                                                                    QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on r  (cost=1050.26..1282.94 rows=720 width=106)
   ->  GroupAggregate  (cost=1050.26..1273.94 rows=720 width=188)
         ->  Merge Join  (cost=1050.26..1226.42 rows=7704 width=188)
               Merge Cond: (d.id = dc.deviceid)
               ->  Index Scan using devices_pkey on devices d  (cost=0.15..58.95 rows=720 width=82)
               ->  Sort  (cost=1050.11..1069.37 rows=7704 width=110)
                     Sort Key: dc.deviceid
                     ->  Merge Join  (cost=403.16..552.77 rows=7704 width=110)
                           Merge Cond: (c_row.id = dc.componentid)
                           ->  Subquery Scan on c_row  (cost=253.38..285.63 rows=720 width=110)
                                 ->  GroupAggregate  (cost=253.38..278.43 rows=720 width=286)
                                       ->  Sort  (cost=253.38..258.73 rows=2140 width=286)
                                             Sort Key: c.id
                                             ->  Hash Join  (cost=44.75..135.00 rows=2140 width=286)
                                                   Hash Cond: (cm.manufacturerid = m.id)
                                                   ->  Hash Join  (cost=26.20..87.03 rows=2140 width=86)
                                                         Hash Cond: (cm.componentid = c.id)
                                                         ->  Seq Scan on component_manufacturers cm  (cost=0.00..31.40 rows=2140 width=8)
                                                         ->  Hash  (cost=17.20..17.20 rows=720 width=82)
                                                               ->  Seq Scan on components c  (cost=0.00..17.20 rows=720 width=82)
                                                   ->  Hash  (cost=13.80..13.80 rows=380 width=208)
                                                         ->  Seq Scan on manufacturers m  (cost=0.00..13.80 rows=380 width=208)
                           ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
                                 Sort Key: dc.componentid
                                 ->  Seq Scan on device_components dc  (cost=0.00..31.40 rows=2140 width=8)
(25 rows)
    • 9

relate perguntas