Thanks for your prompt response, Jeremy! Please forgive me if I'm missing something obvious here, I'm new to Sequel, but I don't quite understand `projects[:users]` notation. According to the docs, Sequel::Dataset#[] method "returns the first record matching the conditions", so I'm not sure what you meant here, since Sequel adds users to WHERE clause for me in this case, which of course breaks everything. As I've mentioned, projects is a dataset, not a table.
Best, Ivan On Thu, Jan 11, 2018 at 5:23 PM Jeremy Evans <[email protected]> wrote: > 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 a topic in the > Google Groups "sequel-talk" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sequel-talk/6qjrfhP1BSs/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- 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.
