Postgresql query - nested json many to many (parent, child)-CodePudding

I have two tables articles and related_articles

table: articles

\ ---- ------------   
| id | title      |
\ ---- ------------ 
|  1 | Products   |
|  2 | Info       |
|  3 | Product#1  |
|  4 | Product#2  |
|  5 | Document   |
\ ---- ------------ 

table: related_articles
\ ----------- ---------------   
| article_id|   related_id  |  
\ ----------- ---------------   
|    1      |       3       |
|    1      |       4       |
|    3      |       5       |
|    4      |       5       |
|    2      |       5       |
\ ----------- ---------------

article_id is foreign key for articles.id

related_id is foreign key for articles.id

Every article can have many parents and many children

I need nested json of all articles with related_articles, something like that:

[{
   "id": 1,
   "title": "Products", 
   "related": [
               { "id" : 3,
                 "title": "Product#1",
                 "related":[
                            {
                             "id" : 5,
                             "title": "Document",
                             "related":[]
                             }  
                  ]
               },      
               {
                "id" : 4,
                "title": "Product#2",
                "related":[
                           {
                             "id" : 5,
                             "title": "Document",
                             "related":[]
                           }    
                 ]
               }
             ]
},
{
   "id": 1,
   "title": "Info", 
   "related": [
               { 
                "id" : 5,
                "title": "Document",
                "related":[]
               }    
              ]
}]

I tried 'with recursive' but with no luck

CodePudding user response：

You can use a recursive cte:

with recursive cte (id, js) as (
   select a.related_article, 
          json_build_object('id', a.related_article, 'title', a1.title, 'related', '[]'::json) 
   from (select distinct a3.related_article from related_articles a3) a 
   join articles a1 on a1.id = a.related_article 
   where not exists (select 1 from related_articles a2 where a2.article_id = a.related_article)
   union all
   select a.article_id, 
          json_build_object('id', a.article_id, 'title', a1.title, 'related', ('['||c.js::text||']')::json) js 
   from cte c join related_articles a on a.related_article = c.id join articles a1 on a1.id = a.article_id
)
select json_agg(c1.js) from (select c.id, json_agg(c.js) js from cte c where not exists (select 1 from related_articles a where a.related_article = c.id) group by c.id order by c.id) c1