Query Postgres Json Field using EF Core 5

Question

I have the following table definition

  [Table("MyTable")]
  public class MyTable: BaseEntity
  {
    [Required]
    public string A{ get; set; }

    [Required]
    [Column(TypeName = "json")]
    public string B{ get; set; }
  }

Column B looks like this:

{"Data": [{"Id":"b8a3cbbc-a4d6-4697-8a0b-cb1d15be179d"}]} (aside from Id there are other properties but for brevity I removed them)

In Entity Framework I want to match all MyTable's where the Id in B is a certain value and A has a certain value. I have tried a lot of things and get numerous errors. How can I add to the following code to achieve what I want?

var results = 
   _repository.Get<MyTable>(_ => _.A == "Something" && _.B = ???);

If you need to query the data often, create a separate, indexed table. Even with JSON indexes, access will always be slower than trying to access a normal table, no matter the RDBMS. Since the field actually contains object data, it should be treated either as an entity or a value object. That means it needs its own type and to be parsed before mapping. You can map entities to views, or use FromSqlRaw/Interpolated to "unpack" the JSON data — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 19, 2021 at 11:12
What you ask is how to map data in a certain format to application objects. That's obviously the job of the mapping model, not the ORM queries that use it. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 19, 2021 at 11:13
@PanagiotisKanavos how do I query the data without bringing back all the records in the table and then mapping to objects before querying? — Sachin Kainth
– Sachin Kainth, Commented Jul 19, 2021 at 13:00

Amal K · Accepted Answer · 2023-07-25 06:46:06Z

5

You can use "EF.Functions.JsonContains" function, but the B column needs to be "jsonb" type instead of "json".

    [Required]
    [Column(TypeName = "jsonb")]
    public string B { get; set; }

Example:

var search = "[{\"Id\": \"b8a3cbbc-a4d6-4697-8a0b-cb1d15be179d\"}]";
var results = _context.MyTable2
      .Where(_ => _.A == "Something" 
                    && EF.Functions.JsonContains(_.B, search));

Similar answer HERE

Also, you can type your query and use Dapper.

Example:

 with temp AS(
  select t."Id", t."A", json_array_elements(t."B"->'Data') as B1 from "MyTable"  t
  )
  select * from temp t
  where 
  t."A"='Something' and
  t.b1->>'Id'='b9a3cbbc-a4d6-4697-8a0b-cb1d15be179a'

edited Jul 25, 2023 at 6:46

Amal K

4,9982 gold badges28 silver badges56 bronze badges

answered Jul 22, 2021 at 11:29

Mitko Keckaroski

1,0891 gold badge10 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Query Postgres Json Field using EF Core 5

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related