从SQL查询Postgres 9.4创建嵌套的JSON [英] Create nested json from sql query postgres 9.4
问题描述
我需要从查询完全结构化的JSON中获取结果. 我可以在postgres中看到一些内置函数可能有用.
I need to get as a result from query fully structured JSON. I can see in postgres that there are some built in functions that may be useful.
作为示例，我创建了如下结构:
As an example I created a structure as follows:
-- 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对象，其中包含人员列表，每个人将具有汽车列表和每个汽车车轮列表.
As a result I would like to have one JSON object which would contain list of person, each person will have list of cars and each car list of wheels.
我尝试过类似的操作，但这不是我想要的:
I tried something like that but it isnt something that I want:
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，但是我不确定如何做到这一点.
I suppose that I'm missing some group by and json_agg but I'm not sure how to do this.
我希望得到这样的结果:
I would like to have as a result something like this:
{ "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
推荐答案
您应该构建层次结构查询以获取层次结构.
You should build a hierarchical query to get a hierarchical structure as a result.
您希望在一个json对象中有很多人，因此请使用 json_agg() 将人员聚集在json数组中.
类似地，一个人可以拥有多辆汽车，您应该将属于一个人的汽车放置在json数组中.同样适用于汽车和车轮.
You want to have many persons in a single json object, so use json_agg() to gather persons in a json array.
Analogically, a person can have multiple cars and you should place cars belonging to a single person in a json array. The same applies to cars and wheels.
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
}
]
}
]
}
]
}
如果您不熟悉嵌套派生表，则可以使用公用表表达式. 此变体说明应从嵌套最多的对象开始到最高级别的查询:
If you are not familiar with nested derived tables you may use common table expressions. This variant illustrates that the query should be built starting from the most nested object toward the highest level:
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;
这篇关于从SQL查询Postgres 9.4创建嵌套的JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
