Postgres JSON array with join?

Question

I have a few tables with a foreign key relationship.

books

Column	Type
id	int4
title	varchar(200)
...	...

book_authors

Column	Type
id	int4
name	varchar(50)
url	varchar(200)

book_authors_lookup

Column	Type
book_id	int4
author_id	int4

book_tags

Column	Type
id	int4
book_id	int4
name	varchar(50)

I want JSON output something like this:

{
  "books": [
    {
      "book_id": 1,
      "title": "Book 1 Title",
      "authors": [
        {"name": "John Doe", "url": "http://twitter/@johndoe"},
        {"name": "Jane Doe", "url": "http://twitter/@janedoe"}
      ],
      "tags": ["tag1", "tag2"]
    },
    {
      // Next book
    }
  ]
}

I don't necessarily need that books root-level object if it's too difficult to include. I can probably just deal with the array itself.

I have no clue how to do this. I've started out with something like this:

select json_build_object(
  'book_id', b.id,
  'title', b.title,
  'authors', json_build_array(??)
  'tags', json_build_array(??)
)
from books b
join book_tags bt on bt.book_id = b.id
join book_authors_lookup bal on bal.book_id = b.id
join book_authors ba on ba.id = bal.author_id

I think you need subselects for authors and tags. Can you prepare a db-fiddle with example of data? You will get an answer much faster if you do that. — Alex Yu
– Alex Yu, Commented Jul 30, 2021 at 5:58

Stefanov.sm · Accepted Answer · 2021-07-31 06:24:33Z

3

I would suggest "knitting" the JSON structure like this.

with t as
(
 select 
    b.id as book_id, 
    b.title, 
    (
        select jsonb_agg(jsonb_build_object('name', ba.name, 'url', ba.url)) 
        from book_authors ba 
        inner join book_authors_lookup bal on ba.id = bal.author_id 
        where bal.book_id = b.id
    ) as authors,
    (
        select array_agg(bt.name)
        from book_tags bt
        where bt.book_id = b.id
    ) as tags
 from books b    
)
select jsonb_build_object('books', jsonb_agg(to_json(t.*))) from t;

Pls. note that the above script has not been tested so there might be trivial errors.

edited Jul 31, 2021 at 6:24

answered Jul 30, 2021 at 6:46

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

2 Comments

Edward J. Stembler Over a year ago

Wow, thanks! Only thing I had to change was to remove the quotes for : ba.'url' to ba.url. Worked perfectly!

Stefanov.sm Over a year ago

Copy/paste :). Fixed it too.

Collectives™ on Stack Overflow

Postgres JSON array with join?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related