Postgres加入阵列

发布于 2025-02-12 18:32:59 字数 437 浏览 1 评论 0原文

我有几行的列。

id,       bookName, authors

12323345, FatBook,  {1,3}

我试图通过上一列中的值加入另一个表。最后一列的类型是文本。

加入将无法正常工作
像这样的

join users u on u."Id" IN books.authors
join users u on u."Id" IN books.authors::text[]

预期结果

12323345, FatBook, 1,3, John, Mark

，还是

12323345, FatBook, (1)John, (3)Mark

我该怎么做？我稍后会加入。

原文

I have a few rows with a column like this.

id,       bookName, authors

12323345, FatBook,  {1,3}

I am trying to join another table by values from the last column.
The type of the last column is text.

Joins won't work
like this

join users u on u."Id" IN books.authors
join users u on u."Id" IN books.authors::text[]

Expected results

12323345, FatBook, 1,3, John, Mark

12323345, FatBook, (1)John, (3)Mark

How do I do this? I will concatenate it later.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤独难免 2025-02-19 18:32:59

某人可能很有用

我想这对于基于String_agg和子查询的

SELECT
b."Id",
b."Name",
(
    SELECT 
        STRING_AGG(CONCAT(u."Id", '(',u."Name", ')') , ',')
    FROM "Users" u 
    WHERE u."Id" = ANY(b."Authors"::text[])
) AS 'Authors'
FROM "Books" b

I guess it might be useful for someone

My solution based on string_agg and subquery

SELECT
b."Id",
b."Name",
(
    SELECT 
        STRING_AGG(CONCAT(u."Id", '(',u."Name", ')') , ',')
    FROM "Users" u 
    WHERE u."Id" = ANY(b."Authors"::text[])
) AS 'Authors'
FROM "Books" b

回复收藏 0 原文

~没有更多了~