从postgresql返回不同的数据类型

I have the below query in PG

我在PG中有以下查询

SELECT
project.project_id,
project.project_name,
   category.category_name,
   array_agg(row(skill.skill_name,projects_skills.projects_skills_id)) AS skills
  FROM project
    JOIN projects_skills ON project.project_id = projects_skills.project_id
    JOIN skill ON projects_skills.skill_id = skill.skill_id
    JOIN category ON project.category_id = category.category_id
 GROUP BY project.project_name,project.project_id, category.category_name;

of particular interest is the below line which seems to return a pseudo-type tuple

特别感兴趣的是下面的行似乎返回伪类型元组

array_agg(row(skill.skill_name,projects_skills.projects_skills_id)) AS skills

I'm unable to create a view of this because of the pseudo type - in addition to this, the row function seems to return a tuple set like the below:

由于伪类型,我无法创建这个视图 - 除此之外,行函数似乎返回如下所示的元组集:

skills: '{"(Python,3)","(Node,4)","(Javascript,5)"}' } 

I could painfully parse it in JavaScript by replacing '(' to '[' etc. but could I do something in postgres to return it preferably as an object?

我可以通过将'(''替换为''等来痛苦地在JavaScript中解析它,但是我可以在postgres中做一些事情来将它作为对象返回吗?

2 个解决方案

#1

As well as the excellent suggestions to use JSON in the comments, and @Erwin 's to use a registered composite type, you can use a two-dimension array, or a multivalues approach:

除了在注释中使用JSON的优秀建议,以及@Erwin使用已注册的复合类型之外,您还可以使用二维数组或多值方法:

Just replace your line

只需更换你的线

array_agg(row(skill.skill_name::text,projects_skills.projects_skills_id::text)) AS skills

with the following:

以下内容:

Two dimension array option 1

array_agg(array[skill.skill_name::text,projects_skills.projects_skills_id::text]) AS skills

-- skills will be '{{Python,3},{Node,4},{Javascript,5}}', thus
-- skills[1][1] = 'Python' and skills[1][2] = '3' -- id is text

Two dimension array option 2

array[array_agg(skill.skill_name),array_agg(projects_skills.projects_skills_id)] AS skills

-- skills will be '{{Python,Node,Javascript},{3,4,5}}', thus
-- skills[1][1] = 'Python' and skills[2][1] = '3' -- id is text

Multivalues

array_agg(skill.skill_name) AS skill_names,
array_agg(projects_skills.projects_skills_id) AS skills_ids

-- skills_names = '{Python,Node,Javascript}' and skill_ids = '{3,4,5}', thus
-- skills_names[1] = 'Python' and skills_ids[1] = 3 -- id is integer

#2

One possible solution is to register a row type (once):

一种可能的解决方案是注册行类型(一次):

CREATE TYPE my_type AS (skill_name text, projects_skills_id int);

I am guessing text and int as data types. Use the actual data types of the underlying tables.

我猜文本和int作为数据类型。使用基础表的实际数据类型。

SELECT p.project_id, p.project_name, c.category_name
     , array_agg((s.skill_name, ps.projects_skills_id)::my_type) AS skills
FROM   project          p
JOIN   projects_skills ps ON p.project_id = ps.project_id
JOIN   skill            s ON ps.skill_id = s.skill_id
JOIN   category         c ON p.category_id = c.category_id
GROUP  BY p.project_id, p.project_name, c.category_name;

There are many other options, depending on your version of Postgres and what you need exactly.

还有很多其他选项,具体取决于您的Postgres版本以及您需要的内容。