如何在PostgreSQL中按数组中的值数量进行分组

huangapple go评论83阅读模式
英文:

How to group by the amount of values in an array in postgresql

问题

我有一个帖子表,其中包含几个列,包括一个liked_by列,其类型为int数组。
由于我无法在这里发布表格,我将发布一个单个帖子的JSON结构,如下所示:

 "post": {
    "ID": 1,
    "CreatedAt": "2022-08-15T11:06:44.386954+05:30",
    "UpdatedAt": "2022-08-15T11:06:44.386954+05:30",
    "DeletedAt": null,
    "title": "Pofst1131",
    "postText": "yyhfgwegfewgewwegwegwegweg",
    "img": "fegjegwegwg.com",
    "userName": "AthfanFasee",
    "likedBy": [
        3,
        1,
        4
    ],
    "createdBy": 1,
}

我正在尝试按照喜欢的顺序发送帖子(最受欢迎的帖子)。这将根据liked_by数组中的值的数量对帖子进行排序。我如何在Postgres中实现这一点?

顺便说一下,我正在使用Go语言和GORM ORM,但我正在使用原始的SQL构建器而不是ORM工具。我也可以使用Go语言解决这个问题。在MongoDB和NodeJS中,我实现这一点的方式是按liked by数组的大小进行分组,并添加一个总喜欢计数字段,并使用该字段进行排序,如下所示:

if(sort === 'likesCount') {
    data = Post.aggregate([         
        {
          $addFields: {
            totalLikesCount: { $size: "$likedBy" } 
          }
        }    
     ])
     data = data.sort('-totalLikesCount');

} else {
    data = data.sort('-createdAt') ; 
}
英文:

I have a posts table with few columns including a liked_by column which's type is an int array.
As I can't post the table here I'll post a single post's JSON structure which comes as below

 "post": {
    "ID": 1,
    "CreatedAt": "2022-08-15T11:06:44.386954+05:30",
    "UpdatedAt": "2022-08-15T11:06:44.386954+05:30",
    "DeletedAt": null,
    "title": "Pofst1131",
    "postText": "yyhfgwegfewgewwegwegwegweg",
    "img": "fegjegwegwg.com",
    "userName": "AthfanFasee",
    "likedBy": [
        3,
        1,
        4
    ],
    "createdBy": 1,
}

I'm trying to send posts in the order they are liked (Most Liked Posts). Which should order the posts according to the number of values inside the liked_by array. How can I achieve this in Postgres?

For a side note, I'm using Go lang with GORM ORM but I'm using raw SQL builder instead of ORM tools. I'll be fine with solving this problem using go lang as well. The way I achieved this in MongoDB and NodeJS is to group by the size of liked by array and add a total like count field and sort using that field as below

if(sort === 'likesCount') {
    data = Post.aggregate([         
        {
          $addFields: {
            totalLikesCount: { $size: "$likedBy" } 
          }
        }    
     ])
     data = data.sort('-totalLikesCount');

} else {
    data = data.sort('-createdAt') ; 
}

答案1

得分: 2

使用本地查询。
假设包含示例数据的表列名为 post,那么查询语句如下:

select <表达式列表> from the_table 
 order by json_array_length(post->'likedBy') desc;

无关的问题,为什么不尝试使用规范化的数据设计?

编辑

现在我知道你的表结构了,以下是更新后的查询语句。使用 array_length

select <表达式列表> from public.posts
 order by array_length(liked_by, 1) desc nulls last;

你也可以添加一个 where 子句。

英文:

Use a native query.
Provided that the table column that contains the sample data is called post, then

select &lt;list of expressions&gt; from the_table 
 order by json_array_length(post-&gt;&#39;likedBy&#39;) desc;

Unrelated but why don't you try a normalized data design?

Edit

Now that I know your table structure here is the updated query. Use array_length.

select &lt;list of expressions&gt; from public.posts
 order by array_length(liked_by, 1) desc nulls last;

You may also wish to add a where clause too.

huangapple
  • 本文由 发表于 2022年8月15日 14:30:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/73357552.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定