Sort by field from jsonb column

Question

I'm using npgsql to store data about shipments and part of table is jsonb column storing some details about shipment including details about customer who made shipment.

Table for displaying data about shipments is displaying only Customer Name and if get that record via

 CustomerName = shipment.Metadata.RootElement.GetProperty("customer").GetProperty("customerName").ToString(),

Request is that I make this column sortable so I would need to sort by this property while accessing database.

Is it even possible to do it in NpgSql?

Answer 1

You can easily sort by a property inside a JSON document - just specify that property in your OrderBy clause:

_ = await ctx.Shipments
    .OrderBy(s => s.Metadata.RootElement.GetProperty("customer").GetProperty("customerName").GetString())
    .ToListAsync();

This produces the following:

SELECT s."Id", s."Metadata"
FROM "Shipments" AS s
ORDER BY s."Metadata"#>>'{customer,customerName}'

You should probably be able to make this use an index as well.