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

user_18710913 0
Viewed 189

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

postgresql sql json
1 Answers

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(json_build_object('id', c1.id, 'title', a1.title, 'related', c1.js)) from (select c.id, json_agg(v.value) js from cte c cross join json_array_elements(c.js -> 'related') v where not exists (select 1 from related_articles a where a.related_article = c.id) group by c.id order by c.id ) c1 join articles a1 on a1.id = c1.id
user_73267380
Related