On Friday, April 24, 2020 at 3:06:44 PM UTC-7, Dennis O'Connell wrote:
>
> Hi Jeremy,
>
> I'm on PostgreSQL 12.2. See this link for a demo:
> https://dbfiddle.uk/?rdbms=postgres_12&fiddle=05f8e70165d7df9c9b19f0b5fb00f2c8
>
> Basically, this is what I'm trying to do. Searching an email in jsonb
> field. But this email value can be partial. Due to this i need to use ilike
> %query% or regex.
>
> I have one other query that I want to convert to Sequel but i fail on
> specific point. I just can't handle arrays. Is it possible to do so on
> Sequel or RAW?
>
> Here is my query:
>
> select t.*
> from test_tbl t
> where exists (select *
> from jsonb_array_elements_text(t.data -> 'alternate_emails')
> as t(email)
> where t.email like '%spence%');
>
>
> In this query, i am able to handle jsonb function but not *as t(email)*.
>
Sequel:
DB.from{test_tbl.as(:t)}.
select_all(:t).
where(
DB.from(Sequel.pg_json_op(Sequel[:t][:data])['alternate_emails'].array_elements_text.as(:t,
[:email])).
where{t[:email].like("%spence%")}.
exists
)
SQL:
SELECT "t".*
FROM "test_tbl" AS "t"
WHERE (EXISTS (SELECT *
FROM json_array_elements_text(("t"."data" ->
'alternate_emails')) AS "t"("email")
WHERE ("t"."email" LIKE '%spence%')))
BTW, your first example and this example use completely different syntax.
Next time, you may want to consider posting the actual SQL you want to
generate up front.
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/95a73330-2376-47e3-b293-bd934fa910b9%40googlegroups.com.
