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

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') ; 
}

CodePudding user response：

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

select <list of expressions> from the_table 
 order by json_array_length(post->'likedBy') 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 <list of expressions> from public.posts
 order by array_length(liked_by, 1) desc nulls last;

You may also wish to add a where clause too.