Thank you, Jeremy. On Saturday, April 25, 2020 at 2:40:55 AM UTC+3, Jeremy Evans wrote: > > 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/bcc92b31-47ac-43dd-bab3-2ba6bb083578%40googlegroups.com.
