CODEWITHSUNDEEP
sqljsonpostgresqljoin
mzingaye nkala
mzingaye nkala

Reputation: 27

PostgreSQL get data in a json format

Lets say you have tables like this:

table: Users

table: Chat

table: Messages

table: Chat_User

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.

Upvotes: 0

Views: 658

Answers (1)

k-lusine
k-lusine

Reputation: 467

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.

Upvotes: 1

Related Questions