Thanks Jeremy. It works!
On Friday, May 24, 2019 at 5:28:28 PM UTC+3, Jeremy Evans wrote:
>
> On Friday, May 24, 2019 at 6:22:53 AM UTC-7, xura wrote:
>>
>> In fact, query is now like this:
>>
>> SELECT DISTINCT ON (jj->>'email') jj->>'email', jj->>'name' , count(*)
>> over(partition by jj->>'email' )
>> from emails
>> join lateral jsonb_array_elements(send_to) j(jj) on true
>> ORDER BY jj->>'email', id desc
>>
>>
> Here's how you can do it in Sequel:
>
> Sequel.extension :pg_json_ops
>
> jj = Sequel.pg_json_op(:jj)
> DB[:emails].
> distinct(jj.get_text('email')).
> select(jj.get_text('email'), jj.get_text('name')).
> select_append{count.function.*.over(:partition=>jj.get_text('email'))}.
> join(Sequel.function(:jsonb_array_elements, :send_to).lateral.as(:j,
> [:jj]), true).
> order(jj.get_text('email'), Sequel[:id].desc)
>
> Here's the resulting SQL:
>
> SELECT DISTINCT ON (("jj" ->> 'email')) ("jj" ->> 'email'), ("jj" ->>
> 'name'), count(*) OVER (PARTITION BY ("jj" ->> 'email'))
> FROM "emails"
> INNER JOIN LATERAL jsonb_array_elements("send_to") AS "j"("jj") ON true
> ORDER BY ("jj" ->> 'email'), "id" DESC
>
> 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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/7a0b57c9-68ca-43cc-8471-98aa2b607c3a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
