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.

Andomar · Accepted Answer · 2018-05-01 20:41:55Z

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.

answered May 1, 2018 at 20:41

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

Collectives™ on Stack Overflow

Joined records as array of json in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related