postgresql - How to cross join into jsonb columns with different id's - Stack Overflow

admin管理员组

文章数量:1393167

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.

本文标签： postgresqlHow to cross join into jsonb columns with different id39sStack Overflow