SQL Where Clause

[Rob Walker]

I am trying to use NHibernate to query some data that has JSONB columns in Postgresql and want to filter on a value that is stored in an array inside the JSONB column.

I have the working SQL query:

select distinct id from 

     axcontact

     left outer join jsonb_array_elements(axemails#>'{Values}') rows(object) on true

   where 

rows.object->>'Value' ilike '%sample%' 

   OR 

     axfirstname ilike '%sample%'

But I can't find a good way to convert this to an NHibernate query.

Any pointers on the best options?

I can generate the SQL if necessary for this fragment, but  it is part of a bigger function and what would be ideal if I could do:

query.Add(Subqueries.PropertyIn("Id", custom_sql_string))

But I can't find a mechanism to allow this either?

Thanks.