CODEWITHSUNDEEP
sqljsonpostgresql
pietrovismara
pietrovismara

Reputation: 6291

Postgres - Querying table with many to many relationship

My schema is composed like this:

channel -> channels_categories -> category

A channel can have many categories, and categories can belong to many channels.

When i query for channels, i would like to get the channels categories with them.

Anyway if i use JOIN i will simply get the channel duplicated for each category.

[
  {
    "channel_name": "Channel1",
    "category_name": "Category1",
    "category_id": "1"
  },
  {
    "channel_name": "Channel1",
    "category_name": "Category2"
    "category_id": "2"
  }
]

The ideal result format (JSON) would be something like:

{
  channel_name: 'Channel1',
  categories: [{/**category**/}, ....]
}

Is there a way i can achieve this result format just with SQL?

Upvotes: 0

Views: 114

Answers (2)

RonaldFindling
RonaldFindling

Reputation: 332

I just noticed that you really want the result in the JSON format, this answer is about combining and merging attributes into JSON lists.

You can use string_agg and concat to combine the category_name and category_id to lists like this:

select channel_name, 
  concat('[', string_agg(category_name, ','), ']') as category_names, 
  concat('[', string_agg(cast(category.id as text), ','), ']') as category_ids 
from 
  channel inner join channel_category on channel.id = channel_category.channel_id 
  inner join category on channel_category.category_id = category.id
group by channel_name;

Which produces results like that:

| channel_name |    category_names    | category_ids |
| channel1     | [category1,category2]| [1,2]        |

For a complete JSON result melpomene's answer seems to fit your needs way better than this.

Upvotes: 0

melpomene
melpomene

Reputation: 85767

This is completely untested but I've looked at the manual for JSON functions and it looks like the following might work:

select jsonb_build_object(
    'channel_name', channel.name,
    'categories',   jsonb_agg(
        jsonb_build_object(
            'category_id',   category.id,
            'category_name', category.name
        )
    )
)
from channel
join channels_categories on channel.id = channel_id
join category on category.id = category_id
group by channel.id

This assumes that channel has a primary key called id.

Upvotes: 3

Related Questions