On Thursday, January 11, 2018 at 5:28:55 AM UTC-8,
[email protected] wrote:
>
> 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?
>
DB[:projects].select(DB.from{json_array_elements(projects[:users]).as(:user)}.select{array_agg(Sequel.pg_json_op(:user)['id'])}.as(:users_ids))
Thanks,
Jeremy
--
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.
