Hi!
I'd like express the following query using Sequel:
SELECT (SELECT array_agg(user->'id') FROM
json_array_elements(projects.users) as user) as users_ids FROM projects
Projects is a dataset that contains column "users", which is a JSON array
of objects, each of those objects have an "id" key, the above query
attempts to select an array of user IDs as a JSON array.
Now, my issue is that I don't know a way to create a dataset that would
represent `json_array_elements(project.users)` bit of SQL query above. If I
could somehow create such dataset (let's refer to it as json_array_dataset),
I imagine the following would work:
projects.select { json_array_dataset.from_self(alias: :user).select {
array_agg(Sequel.pg_json(Sequel[:user])["id"]) }.as(:users_ids) }
(although I'm not sure that aliasing the users_ids subselected column would
work the way I expect it to)
After googling for quite some time, I haven't stumbled upon any reference
on how to handle subqueries for selected columns, but rather only for FROM
and WHERE statements. Could you help me out with this one, please?
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
