CODEWITHSUNDEEP
jsonpostgresql
Jayaram
Jayaram

Reputation: 6606

Return a different datatype from postgresql

I have the below query in 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?

Upvotes: 1

Views: 92

Answers (2)

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

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:

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

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657212

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.

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.

Upvotes: 1

Related Questions