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 sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
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/47577a5b-6bc4-4454-8560-8a63a72c5158%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
