Postgres结果来自查询的多维



如何从单个postgres查询中创建一个多维数组。

我有3个表格具有以下列:

  • tb_school(id,school_name( 例如:

    [ {id:"1", school_name:"School1"}, {id:"2", school_name:"School2"} ]

  • tb_profile(id,profile_name,school_id( 例如:

    [ {id:"1", profile_name:"John", school_id:"1"}, {id:"2", profile_name:"Peter", school_id:"1"}, {id:"3", profile_name:"Sam", school_id:"1"}, {id:"4", profile_name:"Susan", school_id:"2"}, {id:"5", profile_name:"Jude", school_id:"2"}, {id:"6", profile_name:"Kim", school_id:"2"} ]

  • tb_Article(ID,aptif_name,profile_id(例如:

    [ {id:"1", article_name:"Headline News", profile_id:"1"}, {id:"2", article_name:"Sports Recap", profile_id:"2"}, {id:"3", article_name:"Weather", profile_id:"3"}, {id:"4", article_name:"Arts", profile_id:"4"}, {id:"5", article_name:"Other", profile_id:"5"}, {id:"6", article_name:"Example", profile_id:"6"} ]

我希望查询返回结果lat看起来像这样:

[
    {school_name:"School1", people:[
        {profile_name:"John", articles:[
            article_name:"Headline News"
        ]},
        {profile_name:"Peter", articles:[
            article_name:"Sports Recap"
        ]},
        {profile_name:"Sam", articles:[
            article_name:"Weather"
        ]},
    ]},
    {school_name:"School2", people:[
        {profile_name:"Susan", articles:[
            article_name:"Arts"
        ]},
        {profile_name:"Jude", articles:[
            article_name:"Other"
        ]},
        {profile_name:"Kim", articles:[
            article_name:"Example"
        ]},
    ]}
]

我知道可以通过3个单独的选择查询来实现此目标。我想知道的是,这可以通过一个Postgres查询来实现吗?

注意:我对PostgreSQL不好。可能做出了错误的假设,并且代码可能不打算用于生产。

我几乎确定您不能仅仅导致一个嵌套对象。但是您可以收到JSON。所以

SELECT 
	json_build_object(
	  'school_name', school_name,
	  'people', (
		  SELECT 
		  	json_agg(json_build_object(
		  		'profile_name', profile_name,
		  		'articles', (
		  			SELECT 
		  				json_agg(json_build_object(
		  					'article_name', article_name
		  				))
		  			FROM tb_article
		  			WHERE profile_id = tb_profile.facid
		  		) 
		  	))
		  FROM tb_profile
		  WHERE school_id = tb_school.id
		 )
	) AS result
FROM tb_school

它将为您提供JSON,您可以JSON.parse

如果要测试,则可以使用此学习服务。我用此代码使用了它。

SELECT 
	json_build_object(
	  'name', firstname,
	  'slots', (
		  SELECT 
		  	json_agg(json_build_object(
		  		'number', slots,
		  		'facility', (
		  			SELECT 
		  				json_agg(json_build_object(
		  					'name', name
		  				))
		  			FROM cd.facilities
		  			WHERE facid = cd.bookings.facid
		  		) 
		  	))
		  FROM cd.bookings
		  WHERE memid = cd.members.memid
		 )
	) AS result
FROM cd.members

它可能会有所帮助或提供更多想法。