Блог

У меня есть три таблицы (схема ниже). Я хочу выбрать всех пользователей и отобразить их отдельные теги / количество отдельных тегов в 2d-массиве, например

 1 | ['javascript':3]
2 | ['javascript':3, 'windows':2, 'osx':17]
...
  

После рассмотрения других вопросов по stackoverflow у меня есть:

 select "Users".id, array_agg(array[q."TagName"::varchar[], q."TagCount"]::varchar[])
FROM "Users" 
LEFT JOIN (select "UserTags"."UserId" as "UserId", "Tags".name as "TagName", count("Tags".name)::varchar as "TagCount"
from "UserTags" 
LEFT JOIN "Tags" ON ("UserTags"."TagId" = "Tags".id)
GROUP BY "UserTags"."UserId", "Tags".id
 ) as q ON ("Users".id = "q"."UserId")
 group by "Users".id
  

и получить сообщение об ошибке:
could not find array type for data type character varying[]

Как я мог изменить приведенный выше запрос, чтобы решить эту проблему?

—-Схема—-

Теги:

 CREATE TABLE "Tags" (
    id integer DEFAULT nextval('"Tags_id_seq"'::regclass) PRIMARY KEY,
    name character varying(255),
    "createdAt" timestamp with time zone NOT NULL,
    "updatedAt" timestamp with time zone NOT NULL
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX "Tags_pkey" ON "Tags"(id int4_ops);
  

Пользователи

 CREATE TABLE "Users" (
    id integer DEFAULT nextval('"Users_id_seq"'::regclass) PRIMARY KEY,
    "firstName" character varying(255),
    "lastName" character varying(255),
    email character varying(255),
    manager boolean,
    "createdAt" timestamp with time zone NOT NULL,
    "updatedAt" timestamp with time zone NOT NULL,
    "AgencyId" character varying(255),
    "slackId" character varying(255),
    profile json,
    channel character varying(255),
    "scorecardCode" character varying(255),
    "imageUrl" character varying(255),
    "userName" character varying(255)
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX "Users_pkey" ON "Users"(id int4_ops);
  

Пользовательские теги FK userId = Users.id , ТагИд = Tags.id

 CREATE TABLE "UserTags" (
    id integer DEFAULT nextval('"UserTags_id_seq"'::regclass) PRIMARY KEY,
    "UserId" integer NOT NULL REFERENCES "Users"(id),
    "TagId" integer NOT NULL REFERENCES "Tags"(id),
    "createdAt" timestamp with time zone,
    "updatedAt" timestamp with time zone
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX "UserTags_pkey" ON "UserTags"(id int4_ops);

  

Вы пробовали воссоздавать таблицы без кавычек? Я попытался воспроизвести ваш сценарий и получил хороший результат, я думаю.

См.:

 CREATE SEQUENCE tags_id_seq;

CREATE TABLE Tags (
    id integer DEFAULT nextval('tags_id_seq'::regclass) PRIMARY KEY,
    name character varying(255),
    createdAt timestamp with time zone NOT NULL,
    updatedAt timestamp with time zone NOT NULL
);
        
CREATE SEQUENCE Users_id_seq;

CREATE TABLE Users (
    id integer DEFAULT nextval('Users_id_seq'::regclass) PRIMARY KEY,
    firstName character varying(255),
    lastName character varying(255),
    email character varying(255),
    manager boolean,
    createdAt timestamp with time zone NOT NULL,
    updatedAt timestamp with time zone NOT NULL,
    AgencyId character varying(255),
    slackId character varying(255),
    profile json,
    channel character varying(255),
    scorecardCode character varying(255),
    imageUrl character varying(255),
    userName character varying(255)
);
 
CREATE SEQUENCE UserTags_id_seq;

CREATE TABLE UserTags (
    id integer DEFAULT nextval('UserTags_id_seq'::regclass) PRIMARY KEY,
    UserId integer NOT NULL REFERENCES Users(id),
    TagId integer NOT NULL REFERENCES Tags(id),
    createdAt timestamp with time zone,
    updatedAt timestamp with time zone
);    


INSERT INTO USERS (firstname, createdat , updatedat )
VALUES ('john doe',current_timestamp,current_timestamp);

INSERT INTO USERS (firstname, createdat , updatedat )
VALUES ('jane doe',current_timestamp,current_timestamp);
    
INSERT INTO TAGS (name, createdat , updatedat )
VALUES ('javascript', current_timestamp, current_timestamp);

INSERT INTO TAGS (name, createdat , updatedat ) 
VALUES ('osx', current_timestamp, current_timestamp);

INSERT INTO TAGS (name, createdat , updatedat ) 
VALUES ('windows', current_timestamp, current_timestamp);
     
INSERT INTO usertags(userid, tagid, createdat, updatedat)
VALUES (1,1,current_timestamp,current_timestamp);

INSERT INTO usertags(userid, tagid, createdat, updatedat) 
VALUES (1,2,current_timestamp,current_timestamp);

INSERT INTO usertags(userid, tagid, createdat, updatedat)
VALUES (2,2,current_timestamp,current_timestamp);

INSERT INTO usertags(userid, tagid, createdat, updatedat)
VALUES (2,3,current_timestamp,current_timestamp);

INSERT INTO usertags(userid, tagid, createdat, updatedat)
VALUES (2,3,current_timestamp,current_timestamp);
  

Я немного изменил запрос:

 select Users.id
    , ARRAY_AGG(ARRAY[q.TagName, q.TagCount])
FROM Users 
LEFT JOIN
(
    select UserTags.UserId as UserId,  
    Tags.name as TagName, count(Tags.name)::varchar as TagCount
    from UserTags 
    LEFT JOIN Tags ON (UserTags.TagId = Tags.id)
    GROUP BY UserTags.UserId, Tags.id
) as q ON (Users.id = q.UserId)
group by Users.id;
  

И получил эти результаты:

 1 | {{javascript,1},{osx,1}} 
2 | {{osx,1},{windows,2}}
  

Скажите мне, работает ли это для вас.