How to query PostgrSQL JSONB array with Any with Dapper

I'm trying to get a query like the following done with Dapper. Documentation around json is a bit slim though and I couldn't figure it out so far.

Basically I need to run a query similar to this one.

select * from table
WHERE  jsonbfield @> ANY (ARRAY ['[{"property1": "value1", "property2":"value2"}]', '[{"property1": "value3", "property2": "value4"}]']::jsonb[]);

How can I pass this through a parameter?

Here some more details what I've tried so far. The field contains aggregated data like this as jsonb.

Similar to this
[{
    "property1": "value1",
    "property2": "value2,
},
{
    "property1": "value3",
    "property2": "value4,
}]

As mentioned in comments, the sql query is actually working and returning what I need. However, I'm not able so far to pass the data as a parameter. If I include it directly in the query it's working fine. But I would prefer to use parameters instead.

So this is working.

"select * from table where jsonbfield @> ANY (ARRAY['[{\"property1\": \"value1\"}]']::jsonb[])"

This ain't

var parameter = "'[{\"property1\": \"value1\"}]'";
"select * from table where jsonbfield @> ANY (ARRAY[@parameter]::jsonb[])"

Solution

I'm not familiar with Dapper, but the problem is likely the inclusion of quotes around the parameter. Most ORMs will do their own quoting of bind parameters. The extra quotes would be taken literally.

var parameter = "[{\"property1\": \"value1\"}]";

Given you have an ORM with a query builder, it might be simpler to use .OrWhere than to build a Postgres array of jsonb objects. They should perform equivalently.

var builder = new SqlBuilder();
var sql = builder.AddTemplate("select * from foo");
builder.OrWhere("jsonb @> @a::jsonb", new { a = '[{"property1": "value1", "property2": "value2"}]' });
builder.OrWhere("jsonb @> @a::jsonb", new { a = '[{"property1": "value3", "property2": "value4"}]' });

And, of course, you can do that in a loop.