merge queries to make json

[Ricardo Neves Correia Santos]

Hi to all

I have 2 SELECTS's. In each one i consider two colums with a json result
Now I want to aggregate the result. How do I do it

To clear it better

________________________


SELECT pa.paper_id,json_arrayAGG( json_object( "id", a.author_id, "name", a.name) ) AS authors

FROM paper as p

JOIN paper_author as pa On p.paper_id=pa.paper_id
JOIN author as a On pa.author_id=a.author_id
Group by p.paper_id;
_______________________

results:

#paper_id, authors
1 [{"id": 1, "name": "Ric"}, {"id": 2, "name": "Joan"}]
2 [{"id": 2, "name": "Joan"}, {"id": 3, "name": "Ruth"}]
3 [{"id": 4, "name": "Ivan"}]


and

__________________

SELECT pt.paper_id,json_arrayAGG( json_object( "id", t.topic_id, "tag", t.tag) ) AS topics

FROM paper as p

JOIN paper_topic as pt On p.paper_id=pt.paper_id
JOIN topic as t On pt.topic_id=t.topic_id
Group by p.paper_id;
__________________

results:


# paper_id, topics
1, [{"id": 1, "tag": "tag1"}, {"id": 2, "tag": "tag2"}]
2, [{"id": 2, "tag": "tag2"}]
3, [{"id": 1, "tag": "tag1"}]


Now I want to do something like


SELECT json_object ("authors": column of first selection, "topics": column of second selection)


...

to get for each paper (3 rows)

{"authors":[column of first selection], "topics":[column of second selection]}


help is appreciated