How to Use the Postgres Jsonb Type in EFCore (2)

At design time you should avoid queries that filter on Jsonb data. During development the requirement still comes up anyway. Postgres offers many efficient APIs for Jsonb queries. To use them with EFCore, you must define the type in advance or work with JsonDocument.

---

Prepare the data

First, start a local database with docker.

docker run --name my-postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres

Next, prepare a few classes.

// Prepare some animal data
public class Animal : IDisposable
{
    public Guid Id { get; set; }
    public JsonDocument JsonbData { get; set; }

    public void Dispose() => JsonbData.Dispose();
}

// Each kind of animal has different data
public record Dog(string Name, bool IsHealthy);
public record Cat(string Name, Guid CategoryId);
public record Bird(string Name);

// The cat categories go into a separate table
public class CatCategory
{
    public Guid Id { get; set; }
    public string CategoryName { get; set; }
}

The C# code in the gist below prepares the test data. Remember to replace the connection string with your own:
https://gist.github.com/marvin-hsu/a7ea9bd6d514ccddb094144b298e39b3

A Jsonb field as a Join condition

The prepared data keeps the cat category inside JsonbData. Suppose that I want the first ten Persian cats. The query then looks like this:

(await (from animal in context.Animal
            join category in context.CatCategory
                on animal.JsonbData.RootElement.GetProperty(nameof(Cat.CategoryId)).GetGuid() equals category.Id
            where category.CategoryName == "Persian"
            select animal)
        .Take(10)
        .ToListAsync())
    .Select(x=> x.JsonbData.Deserialize<Cat>().Name)
    .ToList()
    .ForEach(Console.WriteLine);

Amazing, right? A field inside Jsonb can Join against an ordinary column. Now look at the SQL that this query translates into:

SELECT animal.Id AS animal_id, animal.JsonbData AS animal_data
FROM Animal AS animal
INNER JOIN CatCategory AS category ON CAST(animal.JsonbData->>'CategoryId' AS uuid) = category.Id
WHERE category.CategoryName = 'Persian'
LIMIT 10

Now the execution plan:

Limit  (cost=23.44..73.87 rows=10 width=55) (actual time=0.039..0.076 rows=10 loops=1)
  ->  Hash Join  (cost=23.44..2378.79 rows=467 width=55) (actual time=0.038..0.075 rows=10 loops=1)
"        Hash Cond: (((animal.""JsonbData"" ->> 'CategoryId'::text))::uuid = category.""Id"")"
"        ->  Seq Scan on ""Animal"" animal  (cost=0.00..2054.00 rows=100000 width=55) (actual time=0.006..0.014 rows=146 loops=1)"
        ->  Hash  (cost=23.38..23.38 rows=5 width=16) (actual time=0.007..0.008 rows=1 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
"              ->  Seq Scan on ""CatCategory"" category  (cost=0.00..23.38 rows=5 width=16) (actual time=0.005..0.006 rows=1 loops=1)"
"                    Filter: (""CategoryName"" = 'Persian'::text)"
                    Rows Removed by Filter: 3
Planning Time: 0.107 ms
Execution Time: 0.093 ms

The data set is simple. Even so, the plan shows something useful. The query starts from the cat category and then looks up the animal name, so it does not use an index.

Queries with Where

Start with a simple query. I want the first healthy dog:

await context.Animal
    .FirstOrDefaultAsync(x => x.JsonbData
        .RootElement
        .GetProperty(nameof(Dog.IsHealthy))
        .GetBoolean());

// SELECT a."Id", a."JsonbData"
// FROM "Animal" AS a
// WHERE CAST(a."JsonbData"->>'IsHealthy' AS boolean)
// LIMIT 1

Now let me introduce several functions that npgsql provides:

EF.Functions	SQL
EF.Functions.JsonContains(customer, @"{""Name"": ""Joe"", ""Age"": 25}")	customer @> '{"Name": "Joe", "Age": 25}'
EF.Functions.JsonContained(@"{""Name"": ""Joe"", ""Age"": 25}", customer)	'{"Name": "Joe", "Age": 25}' <@ customer
EF.Functions.JsonExists(customer, "Age")	customer ? 'Age'
EF.Functions.JsonExistsAny(customer, "Age", "Address")	customer ? | ARRAY['Age','Address']
EF.Functions.JsonExistsAll(customer, "Age", "Address")	customer ?& ARRAY['Age','Address']
EF.Functions.JsonTypeof(customer.GetProperty("Age")) == "number"	jsonb_typeof(customer->'Age') = 'number'

All of these functions return a bool. JsonContains and JsonContained look for a matching object in Json form. The Exist family searches for a key in the Json structure. The last one, JsonTypeof, filters on the type of a value.
Now query the first ten Persian cats with EF.Function. This time the query uses CategoryId directly.

await context.Animal
    .Where(x => EF.Functions.JsonContains(x.JsonbData, """{"CategoryId": "b9daf2a3-d022-4414-b83b-2719afb98a85"}"""))
    .Take(10).ToListAsync();

// SELECT a."Id", a."JsonbData"
// FROM "Animal" AS a
// WHERE a."JsonbData" @> '{"CategoryId": "b9daf2a3-d022-4414-b83b-2719afb98a85"}'
// LIMIT @__p_1

Compare the two queries. GetProperty translates into SQL that adds one cast. EF.Function compares the data directly by its own type.

In everyday queries, a cast on a column often makes the index useless. The same risk applies to Jsonb queries, so avoid the cast where you can. Use syntax such as ->> only after the query returns the rows, when you shape the data into the format that you need. A structural comparison with @> is usually more efficient. When you must extract a field, as in a Join, make sure that the jsonb side constrains the index of the other table.

For more about these EF.Function methods, see here.

Jsonb indexes

Postgres lets you index a Jsonb column to improve query performance. Here is a short comparison of Btree and Gin.

• Btree
  This index targets one specific field inside the Jsonb value. Add an index on the Name field:

  CREATE INDEX animal_name_btree ON "Animal" USING BTREE (("JsonbData"->>'Name'));

  Next, compare the two query styles:

  explain analyse
  select * from "Animal"
  where "JsonbData" ->> 'Name' = 'de7e7';
  
  -- "Bitmap Heap Scan on ""Animal""  (cost=12.17..886.51 rows=500 width=55) (actual time=0.023..0.024 rows=1 loops=1)"
  -- "  Recheck Cond: ((""JsonbData"" ->> 'Name'::text) = 'de7e7'::text)"
  --   Heap Blocks: exact=1
  --   ->  Bitmap Index Scan on animal_name_btree  (cost=0.00..12.04 rows=500 width=0) (actual time=0.017..0.018 rows=1 loops=1)
  -- "        Index Cond: ((""JsonbData"" ->> 'Name'::text) = 'de7e7'::text)"
  -- Planning Time: 0.064 ms
  -- Execution Time: 0.041 ms
  
  explain analyse
  select * from "Animal"
  where "JsonbData" @> '{"Name":"de7e7"}';
  
  -- "Seq Scan on ""Animal""  (cost=0.00..2304.00 rows=10 width=55) (actual time=0.011..20.234 rows=1 loops=1)"
  -- "  Filter: (""JsonbData"" @> '{""Name"": ""de7e7""}'::jsonb)"
  --   Rows Removed by Filter: 99999
  -- Planning Time: 0.053 ms
  -- Execution Time: 20.247 ms

  A btree index improves the performance of a ->> query. It is far less flexible, because it binds to one field. The execution plan also shows that @> does not use the btree index.
• Gin
  A Gin index covers the whole column. It suits broader queries better.

  CREATE INDEX animal_name_gin ON "Animal" USING gin ("JsonbData");
  
  explain analyse
  select * from "Animal"
  where "JsonbData" ->> 'Name' = 'de7e7';
  
  -- "Seq Scan on ""Animal""  (cost=0.00..2554.00 rows=500 width=55) (actual time=0.012..16.196 rows=1 loops=1)"
  -- "  Filter: ((""JsonbData"" ->> 'Name'::text) = 'de7e7'::text)"
  --   Rows Removed by Filter: 99999
  -- Planning Time: 0.110 ms
  -- Execution Time: 16.211 ms
  
  explain analyse
  select * from "Animal"
  where "JsonbData" @> '{"Name":"de7e7"}';
  
  -- "Bitmap Heap Scan on ""Animal""  (cost=28.08..65.28 rows=10 width=55) (actual time=0.043..0.043 rows=1 loops=1)"
  -- "  Recheck Cond: (""JsonbData"" @> '{""Name"": ""de7e7""}'::jsonb)"
  --   Heap Blocks: exact=1
  --   ->  Bitmap Index Scan on animal_name_btree  (cost=0.00..28.07 rows=10 width=0) (actual time=0.038..0.038 rows=1 loops=1)
  -- "        Index Cond: (""JsonbData"" @> '{""Name"": ""de7e7""}'::jsonb)"
  -- Planning Time: 0.114 ms
  -- Execution Time: 0.057 ms

  This time @> is the query that uses the index. Gin works well for broad queries, and you do not have to name the structure.

Now look at the size of the two indexes.

SELECT
    indexname AS index_name,
    pg_size_pretty(pg_total_relation_size(indexname::regclass)) AS total_size
FROM pg_indexes
WHERE tablename = 'Animal' AND indexname in ('animal_name_btree','animal_name_gin');

+-----------------+----------+
|index_name       |total_size|
+-----------------+----------+
|animal_name_btree|2184 kB   |
|animal_name_gin  |5608 kB   |
+-----------------+----------+

As you would expect, Gin is the larger one.

The test data here is simple, so the difference is hard to see. In a real project, however, the query without an index took about 12s. After I optimized it with btree or with gin, the query became much faster (around 1~2ms). Gin really does take up much more space!