How can I select datas to array in postgresql dbms?

Question

Query result:

|  student_id  |  name  |  age  |  group  | 
---------------+--------+-------+----------  
| 1413001      | Ann    | 14    | Tennis  |
| 1413001      | Ann    | 14    | Choir   |

If I select records like this, I want to make them to JSON Object like

{student_id: "1413001", name: "Ann", Age: "14", group:["Tennis", "Choir"]}

How can I make them like that in Java or SQL?

this link should be useful .

Morteza Jalambadani
– Morteza Jalambadani

2018-10-24 09:14:08 +00:00
Commented Oct 24, 2018 at 9:14 — Morteza Jalambadani
– Morteza Jalambadani, Commented Oct 24, 2018 at 9:14

S-Man · Accepted Answer · 2018-10-24 09:14:06Z

1

demo: db<>fiddle

SELECT 
    row_to_json(s) 
FROM (
    SELECT 
        student_id, 
        name, 
        age, 
        json_agg("group") as "group"
    FROM students 
    GROUP BY student_id, name, age
) s

Group the "groups" into a json array with json_agg
The whole subquery rows can be converted into json objects with row_to_json

https://www.postgresql.org/docs/current/static/functions-json.html

Notice that "group" is a reserved word in Postgres. I recommend to rename this column.

answered Oct 24, 2018 at 9:14

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

How can I select datas to array in postgresql dbms?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related