How it is possible to get a resultset with datas from the same jsonb colonne but from two different recordlines?
I have two tables and in both tables I have at least 1 column with the data type jsonb. In one of them (ref_tbl) i have 2 columns and 2 lines with referential datas and the jsonb datatype, so i try to use this table like a reference via a CROSS JOIN query, but it simply doesn't works.
Here my db<>fiddle with an working example where i get this resultset:
^ id_evt ^ evt_status ^ level ^ icon ^ statut ^
| 5 | 1 |Specialiste | Rainy | ToDo |
| 4 | 2 |Expert | Cloudy | In Progress |
| 3 | 2 |Pro | Cloudy | In Progress |
| 2 | 3 |Amateur | Sunny | Realised |
| 1 | 3 |Novice | Sunny | Realised |
This works, because i use the referent id 1 of the ref_tbl. But is it possible, if i add also the second id to the query?
to get following resultset :
^ id_evt ^ evt_status ^ level ^ icon ^ statut ^ lb_event_sa ^ lb_prd ^
| 5 | 1 |Specialiste | Rainy | ToDo | Create | VISA |
| 4 | 2 |Expert | Cloudy | In Progress | Update | Mastercard |
| 3 | 2 |Pro | Cloudy | In Progress | Update | Mastercard |
| 2 | 3 |Amateur | Sunny | Realised | Remove | American Express |
| 1 | 3 |Novice | Sunny | Realised | Remove | Maestro |
Here is the db<>fiddle with my non working query
I use Postgresql 14 in my environment.