Joined records as array of json in postgres

Question

I'd like to return records in a table I'm joining to as an array of jsons inside a single field.

I have a working-ish solution but there is an undesired f1 insdie it.

Current query:

select 
    grouped_by_table.json_array as my_col
from profiles
left join (
    select 
        p.profile_id,
        array_to_json(array_agg(row(p))) as json_array
    from (select * from positions) p
    group by profile_id
) as grouped_by_table on grouped_by_table.profile_id::int = profiles.id

Current result: (This is the data in my_col column for a single record)

[{
    "f1": {
        "id": 153,
        "start_year": 2014,
        "end_year": 2016,
        "profile_id": "100"
    }
}, {
    "f1": {
        "id": 151,
        "start_year": 2016,
        "end_year": null,
        "profile_id": "100"
    }
}]

Desired result: (I'd like to remove the f1 layer)

[{
    "id": 153,
    "start_year": 2014,
    "end_year": 2016,
    "profile_id": "100"
}, {
    "id": 151,
    "start_year": 2016,
    "end_year": null,
    "profile_id": "100"
}]

Thank you kindly.

Answer 1

Use json_agg instead of array_agg:

select  profile_id
,       json_agg(p)
from    positions p
group by
        profile_id

Example at SQL Fiddle.