PostgreSQL Question posted in
The official documentation can be found here.

Query jsonb array in postgresql table with EF Core

I would like to return all the rows that contains a price higher than 123:

// Entity Table1
[Column(TypeName = "jsonb")]
public List<NewPrice> HistoryPrices { get; set; }

public class NewPrice
{
    public int Price { get; set; }
    public DateTimeOffset Date { get; set; }
}

This fails as it can’t translate the linq query to sql query:

_dbContext.Table1
          .Where(p => p.HistoryPrices[0].Price > 123)

postgresql query that works fine would be something like this:

SELECT history_prices, *
FROM Table1
WHERE jsonb_path_exists(
    history_prices,
    '$[0] ? (@.Price > 123)'
);

Can’t see which EF.Functions would fit the case.

1/ I don’t want to run the query locally using AsEnumerable().

2/ I don’t want to change the table structure

Any idea?
Thank you

Tags:

2

Answers


  1. 0

    As documented, mapping directly to jsonb POCO columns is deprecated, and does not support many types of queries.

    You need to set up your model properly with Owned JSON properties.

    modelBuilder
    .Entity<Table1>().OwnsMany(
        t1 => t1.HistoryPrices,
        hp => hp.ToJson()
    );
    Login or Signup to reply.
  2. 0

    Your mapping aside, it looks like functions using PostgreSQL JSONPath aren’t supported in npgsql yet. In the meanwhile, you can use raw SQL if you really need them.

    Instead of jsonb_path_exists() it might make more sense to use @@ to let it use an index:

    SELECT history_prices, *
FROM Table1
WHERE history_prices @@ '$[0].Price > 123';

    _dbContext.Table1
    .FromSql(@"SELECT *
                     ,(history_prices#>>'{0,Price}')::int as initprice 
               FROM Table1 
               WHERE history_prices @@ '$[0].Price > 123'")
    .Where(p => p.initprice > 123) //alternative to WHERE above but won't use the index
    .OrderByDescending(p => p.Something)
    .ToList();

    The .Where above suggests how you could also try to do that without JSONPath (won’t use an index unless it’s an expression index on exactly that):

    SELECT (history_prices #>> '{0,Price}')::int as initprice, *
FROM Table1
WHERE (history_prices #>> '{0,Price}')::int > 123;

    And that’s fairly simple to translate to a LinQ against a JSON column as long as you can change the way you’re mapping your entities. Npgsql shows an example of pretty much exactly that:

    customer.RootElement.GetProperty("Orders")[1].GetProperty("Price").GetInt32()
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search