Using ORDER_BY clause when using Ecto embeds_many selecting a jsonb array column

This is my first take at embedded_schema utilization so pardon my misteps :slight_smile:

I have this as my query

query =
 from m in Users,
 select: m.settings,
 where: m.id == ^user_id,
 order_by: fragment("settings::jsonb[]"), *incomplete*
 limit: 1

The embedded schema is as follows:


embedded_schema do
field :status, :integer, default: 0
field :user_settings, :map
timestamps()
end

I would like the order_by: to use inserted_at from the embedded schema in settings.
How do I drop down to that level.

My migration file denotes the field as `{:array, :jsonb}, default []…

You can use the JSON operators in the fragment: https://www.postgresql.org/docs/current/functions-json.html

That’s what I am struggling with, which operator to use in the fragment to get the inserted_at from the jsonb array in settings.

What have you tried so far? I would guess that the ?->? operator would work though?

I tried order_by: fragment("settings::jsonb[]->inserted_at")
Wasn’t able to derive or cast that.

Since you have an array of jsonb in the settings field, which of them do you want to use for ordering?

1 Like

I think you almost got it. Can you try this

fragment(“settings ->> ‘inserted_at’ ASC”)

Edit: Oh wait this is an array, i guess you have to select which json object to use. Are you trying to sort the objects in the array or sort the parent of the jsonb objects?

4 Likes

Hello. I just happened to bump into something exactly like this. Have you found any progress? I’m actually just stuck with picking the right arrow because ->> doesn’t work as well

I might have found a solution for your problem:

fragment("unnest(?) ->> 'YOUROBJECTFIELD' DESC", YOURJSONBFIELD)
2 Likes