G
G
Governor2020-11-02 14:32:05
PostgreSQL
Governor, 2020-11-02 14:32:05

How to wrap each json object in an array?

Example: sqlfiddle.com/#!17/000c4c/1/0

There is a People table with the following. lines:

| ID  | name  | age |
| 773 | Katya | 21  |
| 828 | Masha | 23  |
| 999 | Danya | 41  |
| 999 | Vanya | 12  |

You need to get the records as a JSON object, where the ID of the object will be the key of the object.
This can be done like this: SELECT jsonb_object_agg(ID, People) FROM People;
Result:
{
  "773": {"ID": 773, "name": "Katya", "age": "21"},
  "828": {"ID": 773, "name": "Masha", "age": "23"},
  "999": {"ID": 773, "name": "Danya", "age": "41"}
}


But the IDs in the table can be repeated (this is important), because of this, the rest of the records with duplicate IDs are discarded.
You need to get the following from this table:
{
  "773": [
    {"ID": 773, "name": "Katya", "age": "21"}
    ],
  "828": [
    {"ID": 828, "name": "Masha", "age": "23"}
    ],
  "999": [
    {"ID": 999, "name": "Danya", "age": "41"},
    {"ID": 999, "name": "Vanya", "age": "12"}
    ]
}


How can this be checked?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
G
Governor, 2020-11-02
@Mr-Governor

So far there is a solution:

SELECT jsonb_object_agg(my_table.ID, my_table)
FROM
(
    SELECT
        p.ID,
        (
            SELECT json_agg(pp) AS desc
            FROM People pp
            WHERE ID = p.ID
        )
    FROM People p
) AS my_table;

More practical solutions are welcome.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question