postgresql - 在 PostgreSQL 查询中添加条件语句

Question

我有这个问题:

WITH grouped_messages AS
(SELECT
   um.conversation_id,
   array_agg(json_build_object('message', um.message, 'sent_at', um.created_at))
 FROM user_messages um
   INNER JOIN users_conversations c ON c.conversation_id = um.conversation_id
 WHERE c.user_id = '#{user_id}' AND um.user_id != '#{user_id}' #{ "and um.created_at >= '#{timestamp}'" IF TIMESTAMP}
 GROUP BY um.conversation_id
),
    senders AS
  (SELECT
     gm.conversation_id,
     json_build_object('id', u.id, 'user_name', u.user_name, 'avatar',
                       ('https://my-staging.s3.amazonaws.com/public/uploads/user/' || u.id :: TEXT ||
                        '/avatar.jpg')) AS sender
   FROM grouped_messages AS gm
     INNER JOIN users_conversations c ON c.conversation_id = gm.conversation_id
     INNER JOIN users u ON u.id = c.user_id
   WHERE u.id != '#{user_id}'
  )
SELECT json_object_agg(grouped_messages.conversation_id,
                       json_build_object('new_messages', grouped_messages.array_agg, 'sender', senders.sender))
FROM grouped_messages
  INNER JOIN senders ON senders.conversation_id = grouped_messages.conversation_id

输出，例如:

{
    "62": {
        "new_messages": [
            {
                "message": "some",
                "sent_at": "2016-05-30T20:19:53.786024"
            },
            {
                "message": null,
                "sent_at": "2016-05-30T20:19:26.408814"
            }
        ],
        "sender": {
            "id": "e4ba308b-a5cf-47ad-b8d6-d774eb325411",
            "user_name": null,
            "avatar": "https://my-staging.s3.amazonaws.com/public/uploads/user/e4ba308b-a5cf-47ad-b8d6-d774eb325411/avatar.jpg"
        }
    }
}

现在，我需要的是在这个查询中有一个条件语句。

如果您(用户)头像不为 NULL，则应用此 'https://my-staging.s3.amazonaws.com/public/uploads/user/' || u.id::文本|| '/avatar.jpg'，否则只需插入 ':null' 字符串(以便任何 JSON 解析器将其识别为空对象)。

不确定如何将此 if-else 放在 select 语句中以及如何确保有关 JSON 解析器的部分。

Answer 1

对我有用:

        (SELECT gm.conversation_id, json_build_object('id', u.id, 'user_name', u.user_name,
        'avatar', (
         CASE WHEN u.avatar IS NULL THEN ':null'
         ELSE ('https://my-staging.s3.amazonaws.com/public/uploads/user/' || u.id::text || '/avatar.jpg')
         END)) 

您可以在 SELECT block 中提供 case 语句，就好像它是值一样(带有可选的 AS)。