Ecto query - order_by field in jsonb column

Question

The following code works fine when ordering an Ecto query by the created_at column.

sort_params = [asc: :created_at]
query = from user in users,
      order_by: ^sort_params,
      limit: ^page_size,
      offset: ^offset

Assuming I have a database column of type jsonb called info, and one of the fields in the JSON object is "status", is there a way to sort by that field? Does Ecto have a valid equivalent to something like

sort_params = [asc: :info.status]

Answer 1

Yes,

You can use Ecto.Query.API.fragment/1 for that.

# you can use fragment/1 only directly in the query

query = from user in users,
  order_by: fragment("info->>'status' ASC"),
  limit: ^page_size,
  offset: ^offset