PostgreSQL get data in a json format

Question

Lets say you have tables like this:

table: Users

id
firstName
email

table: Chat

id
type

table: Messages

id
type
fromUserId
ChatId
message

table: Chat_User

ChatId
UsersId

How can I query my database to get the chats that belong to a user and all the chats and messages in that chat. So basically this data in this structure:

{
  "data": [
    {
      "id": 1,
      "type": "dual",
      "users": [
        {
          "id": 1,
          "firstName": "bob",
          "chatIds": [
            1
          ]
        }
      ],
      "messages": [
        {
          "id": 2,
          "type": "text",
          "message": "good thanks",
          "fromUserId": 1
        },
        {
          "id": 1,
          "type": "text",
          "message": "hey how are you?",
          "fromUserId": 2
        }
      ]
    }
  ]
}

I have tried doing this query:

SELECT JSON_AGG(ROW_TO_JSON(chatty)) AS users
  FROM (SELECT *
          FROM chat_user
          JOIN users
            ON users.id = chat_user."usersId"
          JOIN message
            ON users.id = message."fromUserId"
         WHERE chat_user."chatId" IN
               (SELECT chat_user."chatId" 
                  FROM chat_user 
                 WHERE "usersId" = 2)
        ) chatty

which gives :

[
  { 
    "usersId": 1, 
    "chatId": 1, 
    "id": 1, 
    "firstName": "bob", 
    "lastName": "booby",
    "email": "[email protected]", 
    "id": 1, 
    "type": "text", 
    "message": "hey how are you?", 
    "fromUserId": 1, 
    "chatId": 1 
  }, 
  { 
    "usersId": 2, 
    "chatId": 2, 
    "id": 2, 
    "firstName": "bob", 
    "lastName": "booby", 
    "email": "[email protected]", 
    "id": 2, 
    "type": "text", 
    "message": "good thanks", 
    "fromUserId": 2, 
    "chatId": 1 
  }, 
  { 
    "usersId": 2, 
    "chatId": 1, 
    "id": 2, 
    "firstName": "bob", 
    "lastName": "booby", 
    "email": "[email protected]", 
    "id": 2, 
    "type": "text", 
    "message": "good thanks", 
    "fromUserId": 2, 
    "chatId": 1 
  }
]

Which is very far off my desired data structure.

k-lusine · Accepted Answer · 2021-04-01 18:59:38Z

1

The desired result can be achieved using the combination of json_agg and json_build_object functions (see the query).

SELECT "id",
    "type",
    aggregated_users.users,
    aggregated_messages.messages 
FROM
    chat
    INNER JOIN (
    SELECT
        "chatId",
        json_agg ( json_build_object ( 'name', users."name", 'id', users."id" ) ) AS "users" 
    FROM
        chat_user
        INNER JOIN users ON users.ID = chat_user."userId" 
    GROUP BY
        "chatId" 
    ) AS aggregated_users ON chat."id" = aggregated_users."chatId"
    INNER JOIN (
    SELECT
        "chatId",
        json_agg ( json_build_object ( 'type', message."type", 'id', message."id", 'fromUser', message."fromUserId", 'type', message."type" ) ) AS "messages" 
    FROM
        chat
        INNER JOIN message ON chat."id" = message."chatId" 
    GROUP BY
    "chatId" 
    ) AS aggregated_messages ON chat.ID = aggregated_messages."chatId"

Note: It's not a good idea to group data using the database queries (especially something like messages). One definitely needs to apply pagination there.

answered Apr 1, 2021 at 18:59

k-lusine

4676 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mzingaye nkala Over a year ago

Thank you for your answer, can you elaborate on this " 's not a good idea to group data using the database queries (especially something like messages). One definitely needs to apply pagination there." Thank you

Collectives™ on Stack Overflow

PostgreSQL get data in a json format

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related