Selecting all records from table, but return key-value object for easier mapping on the frontend-CodePudding

I have a table similar to this:

id       device_id       text 
 1       1234            test1
 2       1234            test2
 3       1236            test3
 4       1236            test4
 5       1236            test5

I'd like to query this table and return an object with all device_ids and the messages that belong to them. So the result would be something like,

{
    1234: [
        {
            id: 1,
            text: 'test1'
        },
        {
            id: 2,
            text: 'test2'
        },
    ],
    1236: [
        {
            id: 3,
            text: 'test3'
        },
        {
            id: 4,
            text: 'test4'
        },
        {
            id: 5,
            text: 'test5'
        }
    ]
}

Obviously, I can query for all records and then afterwards create this object and then return it, but is it possible to do this on a database level?

CodePudding user response：

You can use jsonb_agg(jsonb_build_object('id', id, 'text', text)) to build the aggregated objects in postgresql, then create the final one from your application:

WITH data(id, device_id, text) AS (
    VALUES (1, 1234, 'test1')
         , (2, 1234, 'test2')
         , (3, 1236, 'test3')
         , (4, 1236, 'test4')
         , (5, 1236, 'test5')
)
SELECT device_id, jsonb_agg(jsonb_build_object('id', id, 'text', text))
FROM data
GROUP BY device_id

returns

 --------- ------------------------------------------------------------------------------------ 
|device_id|jsonb_agg                                                                           |
 --------- ------------------------------------------------------------------------------------ 
|1234     |[{"id": 1, "text": "test1"}, {"id": 2, "text": "test2"}]                            |
|1236     |[{"id": 3, "text": "test3"}, {"id": 4, "text": "test4"}, {"id": 5, "text": "test5"}]|
 --------- ------------------------------------------------------------------------------------

You could also build the whole result from within postgresql (though it might not be the best idea, especially if this is the only place in the app where you do so):

WITH data(id, device_id, text) AS (
    VALUES (1, 1234, 'test1')
         , (2, 1234, 'test2')
         , (3, 1236, 'test3')
         , (4, 1236, 'test4')
         , (5, 1236, 'test5')
)
SELECT jsonb_object_agg(device_id, elements)
FROM (
    SELECT device_id, JSONB_AGG(JSONB_BUILD_OBJECT('id', id, 'text', text))
    FROM data
    GROUP BY device_id
) AS f(device_id, elements)

returns

 ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 
|jsonb_object_agg                                                                                                                                                |
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 
|{"1234": [{"id": 1, "text": "test1"}, {"id": 2, "text": "test2"}], "1236": [{"id": 3, "text": "test3"}, {"id": 4, "text": "test4"}, {"id": 5, "text": "test5"}]}|
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------

Edit: you might also consider replacing the jsonb_agg with an array_agg((id, text)) and do the Array[(int, text)] -> json transformation in your application so that the query itself does not know/care about the final text representation of the data. Though not all drivers support arrays of composite types.