Querying JSONB Array with EF Core and PostgreSQL in C#-CodePudding

I have a complex array and need to query in EF Core and can't seem to find the right combination:

 public class A
 {
     public string Name { get; set; }

     [Column(TypeName = "jsonb")] 
     public List<B> Tags { get; set; }
 }

 public class B
 {
     public Guid Id { get; set; }
     public string Name { get; set; }
 }

Using these model classes, I'm trying to find every A that has a B equal to a certain value and/or a B that is in a list. For the first scenario, I've tried the following with no success:

 var query = "{\"Id\": \"7aecbd2c-ae2e-4362-9bd6-1d569eb94849\"}";

 var test1 = await context.A
                          .Where(a => EF.Functions.JsonExistAny(a.B, query))
                          .ToListAsync(context.CancellationToken);

 var test2 = await context.A
                          .Where(a => EF.Functions.JsonContains(a.B, query))
                          .ToListAsync(context.CancellationToken);

 var test3 = await context.A
                          .Where(a => EF.Functions.JsonContained(query, a.B))
                          .ToListAsync(context.CancellationToken);

 var test4 = await context.A
                          .Where(a => a.B.Any(b => b.Id == Guid.Parse("7aecbd2c-ae2e-4362-9bd6-1d569eb94849")))
                          .ToListAsync(context.CancellationToken);

Any direction would be appreciated

CodePudding user response：

you should use square brackets [] for jsonb array query.

solution:

var query = "[{\"Id\": \"7aecbd2c-ae2e-4362-9bd6-1d569eb94849\"}]";

var test2 = await context.A
                          .Where(a => EF.Functions.JsonContains(a.B, query))
                          .ToListAsync(context.CancellationToken);

Example query for jsonb array:

SELECT u."Id", u."Addresses"
FROM "Users" AS u
WHERE u."Addresses" @> '[{"Code": 10}]'