Thanks Jeremy.
Sorry for the confusion. Yes they are different but they are doing the same
thing.
XQuery is faster than jsonb_array_elements_text. Thats why im trying to use
it. I can see huge differences in ANALYZE.
What im trying to do is search emails based on given query parameter by
user. So:
{
"emails": ["[email protected]", "[email protected]"]
}
As you can see emails key contains array of emails. So, If user give me a
parameter for search like 'jeremy', i want to search this word in those
emails. jeremy word can be placed in start, middle or end. So i need either
%jeremy% for ILIKE or ~* 'jeremy'. here. By saying this, i have two
options. JSON Paths and jsonb functions.
I asked this question on StackOverflow and someone replied me with this two
examples. First example was right above. The jsonb function. Second was
much faster and contains XQuery (as you said).
Thanks,
-Den.
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/5c926821-64d3-4bbc-95c8-1dda8bff3083%40googlegroups.com.
