Querying embeds_many field

Questions / Help
1

Hey there,

pretty much SQL noob(and rather new to Elixir, too), I cant seem to find a way to query a Schema(Room) for its embeds_many field(participants).

I’m using Postgres and created the field as {:array, :map} in the migration as per the docs.
Ecto turns this into an array column, where entries get the jsonb type, the column is jsonb[].

The embedded schema RoomParticipant has a field role that for instance takes the value "agent".
Now, for the life of me, I cant find a way to eg. query for all rooms that have at least one participant of role “agent”

Could somebody shed some light?
I guess, I’ll have to use fragments but I’m even unable to create get a raw sql query doing what I want : )

Cheers & thanks a lot for any pointer!
Carsten

2 Likes
2

Would something like this work?

SELECT * FROM rooms WHERE room_participant -> 'role' = 'agent';

http://schinckel.net/2014/05/25/querying-json-in-postgres/

1 Like
3

While it’s possible to query embeds with fragments and direct SQL syntax, I’d say that using embeds when you need to query on the fields is a wrong choice, and regular association is the way to go. It’s harder to properly index and that will cause queries to be extremely slow on bigger data sets.
Embeds are great for some use cases, but they shouldn’t be abused.

3 Likes
4

Hey guys,

thanks for your replies! And sorry I’m getting back this late, have been a bit loaded.

@wfgilman Yep, I saw that (pretty great) post before - also the Django implementation looks really expressive btw. However, I wasnt able to use the info for my exact problem.

I was able to get my case working by first changing the migration to not read:
add :participants{:array, :map} # -> jsonb[]
but instead just straight
add :participants, :jsonb # -> jsonb

Queries then work like:
SELECT * FROM rooms WHERE participants @> '[{"role":"agent"}]'

Using Ecto.Query.from that gets a lil cumbersome:
from r in Room, where: fragment("? @> ?", r.participants, "[{\"role\":\"agent\"}]")
…but it would be rather easy to abstract the escaping away a bit, I guess.

I’ll fiddle around some more(also with {:array, :map}) and report back here.

@michalmuskala again, thanks for the recommendation! I think I have to disagree, though.
At least when using Postgres: json(b) support there, including indexes seems pretty great, seemingly not only for dumping stuff in/out but also for querying. In the hopes I’m not totally guilty of abuse just yet : )

Cheers