Querying JSONB Array with EF Core and PostgreSQL in C#

Question

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

Okan Karadag · Accepted Answer · 2022-07-29 20:21:29Z

5

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}]'

edited Jul 29, 2022 at 20:21

answered Jul 29, 2022 at 20:14

Okan Karadag

3,0951 gold badge15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying JSONB Array with EF Core and PostgreSQL in C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related