PostgreSQL - ORDER BY a date in nested array-CodePudding

I've got the following data structure:

[
 {
   "postID": 1, 
   "images": [
      {"imageID": 1, "pos": 1, "uploaded": "2022-01-01", "tags": []}, 
      {"imageID": 2, "pos": 2, "uploaded": "2022-01-01", "tags": []}
    ]
 },
 {
   "postID": 2, 
   "images": [
      {"imageID": 3, "pos": 1, "uploaded": "2022-01-01", "tags": []}, 
      {"imageID": 4, "pos": 2, "uploaded": "2022-01-01", "tags": []}
    ]
 }
]

How can I ORDER BY the most recent (or oldest) dates for each post, where the date is selected to be the most recent/oldest from each of the images? Note that I still want to maintain the order of the images according to the "pos" column.

This is my query that generates this data:

WITH image_tags AS (
    SELECT images."post id", 
           json_build_object (
               'imageID', images."image id",
               'uploaded', images."uploaded",
               'tags', json_agg("tag map".tag) ) AS image
    FROM images
    JOIN "tag map" ON images."image id" = "tag map"."image id"
    GROUP BY images."post id", images."image id"
    ORDER BY images."pos" DESC
)
SELECT posts."post id" AS "postID", 
       json_agg(image_tags.image) AS images
FROM posts
JOIN image_tags ON posts."post id" = image_tags."post id"
GROUP BY posts."post id"
--ORDER BY ?

Possible alternatives: Instead I move uploaded date to the post table, but these means that I won't be able to find the individual upload dates for each of the images. So that is a last resort only if this isn't possible to do.

CodePudding user response：

You have all the necessary information in your images table. (Since you did not show the definition of that table, what follows is assuming a couple of things.) A window function should do the trick:

WITH image_tags AS (
    SELECT images."post id", 
           json_build_object (
               'imageID', images."image id",
               'uploaded', images.uploaded,
               'tags', json_agg("tag map".tag) ) AS image,
           first_value(images.uploaded) OVER w AS newest,
           last_value(images.uploaded) OVER w AS oldest
    FROM images
    JOIN "tag map" ON images."image id" = "tag map"."image id"
    WINDOW w AS (PARTITION BY images."post id" ORDER BY images.uploaded DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    GROUP BY images."post id", images."image id"
    ORDER BY images."pos" DESC
)
SELECT posts."post id" AS "postID", 
       json_agg(image_tags.image) AS images
FROM posts
JOIN image_tags USING ("post id")
GROUP BY posts."post id"
ORDER BY image_tags.newest -- or oldest

The window function takes all the rows in the images table that have the same "post id" and makes them available for analysis. You can then find the first and the last row of the window frame and store the value in new columns in the image_tags row source. After that the ordering in the main query is straightforward.