Join support for Lateral jsonb_array_elements() in postgres

[VenkateshRathinavel]

I am dealing with jsonb column to make a join with array of values in a attribute in PostgreSQL

my need is --> LEFT JOIN LATERAL jsonb_array_elements(custom_field_base.custom_field_test1 -> 'country') Country_1 ON (true)
my output is --> LEFT JOIN "LATERAL jsonb_array_elements(custom_field_base"."custom_field_test1-> 'Country') Country_1" ON (true)

is there any way to generate raw query or to overcome is "" while compiling I am using PostgreSQL compiler

[ahmad-moussawi]

I think the closest thing you can do is to use a CTE with the combination of FromRaw

var query = new Query()
    .With("Country", q => q.FromRaw("jsonb_array_elements(custom_field_base.custom_field_test1 -> 'country')"))
    .FromRaw("[Table] LEFT JOIN LATERAL [Country] ON (true)")
    .Select("Field1")

check this [interactive example](https://sqlkata.com/playground/pgsql?code=%0D%0Avar%20query%20%3D%20new%20Query()%0D%0A%20%20%20%20.With(%22Country%22%2C%20q%20%3D%3E%20q.FromRaw(%22jsonb_array_elements(custom_field_base.custom_field_test1%20-%3E%20'country')%22))%0D%0A%20%20%20%20.FromRaw(%22%5BTable%5D%20LEFT%20JOIN%20LATERAL%20%5BCountry%5D%20ON%20(true)%22)%0D%0A%20%20%20%20.Select(%22Field1%22)

[VenkateshRathinavel]

Thanks for the lightning fast reply... I will check with the same....