Recursice JSONB join in Postgres-CodePudding

I am trying to expand existing system to allow self-referencing "foreighn key" relationships based on values in JSONB. I'll give an example for beter understanding

|  ID  |            DATA                                        |
|---------------------------------------------------------------|
|  1   |{building_id: 1, building_name: 'Office 1'}             |
|---------------------------------------------------------------|
|  2   |{building_id: 2, building_name: 'Office 2'}             |
|---------------------------------------------------------------|
|  3   |{building_id: 1, full_name: 'John Doe', calary: 3000}   |
|---------------------------------------------------------------|
|  4   |{building_id: 1, full_name: 'Alex Smit', calary: 2000}  |
|---------------------------------------------------------------|
|  5   |{building_id: 1, full_name: 'Anna Birkin', calary: 2500}|

I tried using jsonb_array_elements_text but i need new data to be combined in single JSON field like this

|  ID  |            DATA                                                                     |
|--------------------------------------------------------------------------------------------|
|  1   |{building_id: 1, building_name: 'Office 1', full_name: 'John Doe', calary: 3000}     |
|--------------------------------------------------------------------------------------------|
|  2   |{building_id: 1, building_name: 'Office 1', full_name: 'Alex Smit', calary: 2000}    |
|--------------------------------------------------------------------------------------------|
|  3   |{building_id: 2, building_name: 'Office 2', , full_name: 'Anna Birkin', calary: 2500}|

I am wondering whether it is even possible

CodePudding user response：

Assuming that Anna Birkin is in building_id 2 and these different object types are consistent enough to determine types by the presence of keys, try something like this:

select b.data || p.data as result
  from really_bad_idea b
       join really_bad_idea p on p.data->'building_id' = b.data->'building_id'
 where b.data ? 'building_name'
   and p.data ? 'full_name';

db<>fiddle here