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

Questions / Help
1

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 []ā€¦

2

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

3

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.

4

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

5

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

6

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

1 Like
7

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
8

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

9

I might have found a solution for your problem:

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