从SQL查询Postgres 9.4创建嵌套的JSON

一尘不染

从SQL查询Postgres 9.4创建嵌套的JSON

sql

我需要从查询完全结构化的JSON中获取结果。我可以在postgres中看到一些内置函数可能有用。

作为示例，我创建了如下结构：

    -- Table: person

-- DROP TABLE person;

CREATE TABLE person
(
  id integer NOT NULL,
  name character varying(30),
  CONSTRAINT person_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE person
  OWNER TO postgres;

  -- Table: car

-- DROP TABLE car;

CREATE TABLE car
(
  id integer NOT NULL,
  type character varying(30),
  personid integer,
  CONSTRAINT car_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE car
  OWNER TO postgres;

  -- Table: wheel

-- DROP TABLE wheel;

CREATE TABLE wheel
(
  id integer NOT NULL,
  whichone character varying(30),
  serialnumber integer,
  carid integer,
  CONSTRAINT "Wheel_PK" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE wheel
  OWNER TO postgres;

和一些数据：

INSERT INTO person(id, name)
VALUES (1, 'Johny'),
       (2, 'Freddy');

INSERT INTO car(id, type, personid)
VALUES (1, 'Toyota', 1),
       (2, 'Fiat', 1),    
       (3, 'Opel', 2);

INSERT INTO wheel(id, whichone, serialnumber, carid)
VALUES (1, 'front', '11', 1),
       (2, 'back', '12', 1),
       (3, 'front', '21', 2),
       (4, 'back', '22', 2),
       (5, 'front', '3', 3);

结果，我希望有一个JSON对象，其中包含人员列表，每个人将具有汽车列表和每个汽车车轮列表。

我尝试了类似的方法，但我不想这样做：

select json_build_object(
    'Persons', json_build_object(
    'person_name', person.name,
    'cars', json_build_object(
        'carid', car.id,    
        'type', car.type,
        'comment', 'nice car', -- this is constant
        'wheels', json_build_object(
            'which', wheel.whichone,
            'serial number', wheel.serialnumber
        )

    ))
)

from
person 
left join car on car.personid = person.id
left join wheel on wheel.carid = car.id

我想我缺少一些group by和json_agg，但是我不确定如何做到这一点。

结果，我想得到这样的东西：

{ "persons": [   
    {
      "person_name": "Johny",
      "cars": [
          {
            "carid": 1,
            "type": "Toyota",
            "comment": "nice car",
            "wheels": [{
              "which": "Front",
              "serial number": 11
            },
            {
              "which": "Back",
              "serial number": 12
            }]
          },
          {
            "carid": 2,
            "type": "Fiat",
            "comment": "nice car",
            "wheels": [{
              "which": "Front",
              "serial number": 21
            },{
              "which": "Back",
              "serial number": 22
            }]
          }
        ]
    },
    {
      "person_name": "Freddy",
      "cars": [
          {
            "carid": 3,
            "type": "Opel",
            "comment": "nice car",
            "wheels": [{
              "which": "Front",
              "serial number": 33
            }]
          }]
    }]
}

http://www.jsoneditoronline.org/?id=7792a0a2bf11be724c29bb86c4b14577

阅读 124

2021-03-17

共1个答案

一尘不染

您应该构建一个层次结构查询，以得到层次结构。

您希望在一个json对象中有很多人，因此可用于json_agg()在json数组中收集人。类似地，一个人可以拥有多辆汽车，您应该将属于一个人的汽车放置在json数组中。同样适用于汽车和车轮。

select
    json_build_object(
        'persons', json_agg(
            json_build_object(
                'person_name', p.name,
                'cars', cars
            )
        )
    ) persons
from person p
left join (
    select 
        personid,
        json_agg(
            json_build_object(
                'carid', c.id,    
                'type', c.type,
                'comment', 'nice car', -- this is constant
                'wheels', wheels
                )
            ) cars
    from
        car c
        left join (
            select 
                carid, 
                json_agg(
                    json_build_object(
                        'which', w.whichone,
                        'serial number', w.serialnumber
                    )
                ) wheels
            from wheel w
            group by 1
        ) w on c.id = w.carid
    group by personid
) c on p.id = c.personid;

（格式化的）结果：

{
    "persons": [
        {
            "person_name": "Johny",
            "cars": [
                {
                    "carid": 1,
                    "type": "Toyota",
                    "comment": "nice car",
                    "wheels": [
                        {
                            "which": "front",
                            "serial number": 11
                        },
                        {
                            "which": "back",
                            "serial number": 12
                        }
                    ]
                },
                {
                    "carid": 2,
                    "type": "Fiat",
                    "comment": "nice car",
                    "wheels": [
                        {
                            "which": "front",
                            "serial number": 21
                        },
                        {
                            "which": "back",
                            "serial number": 22
                        }
                    ]
                }
            ]
        },
        {
            "person_name": "Freddy",
            "cars": [
                {
                    "carid": 3,
                    "type": "Opel",
                    "comment": "nice car",
                    "wheels": [
                        {
                            "which": "front",
                            "serial number": 3
                        }
                    ]
                }
            ]
        }
    ]
}

如果您不熟悉嵌套派生表，则可以使用公用表表达式。此变体说明应从嵌套最多的对象到最高级别的对象构建查询：

with wheels as (
    select 
        carid, 
        json_agg(
            json_build_object(
                'which', w.whichone,
                'serial number', w.serialnumber
            )
        ) wheels
    from wheel w
    group by 1
),
cars as (
    select 
        personid,
        json_agg(
            json_build_object(
                'carid', c.id,    
                'type', c.type,
                'comment', 'nice car', -- this is constant
                'wheels', wheels
                )
            ) cars
    from car c
    left join wheels w on c.id = w.carid
    group by c.personid
)
select
    json_build_object(
        'persons', json_agg(
            json_build_object(
                'person_name', p.name,
                'cars', cars
            )
        )
    ) persons
from person p
left join cars c on p.id = c.personid;

2021-03-17