how to use wildcard for a column jsonb type

--------------------------------------------------------------
ID   | Day              | ftfm_profile
--------------------------------------------------------------
23   |  22/10/2020      | {"name": ["EGMHTMA", "EGMHCR", "EDYYFOX2", "EGTTFIR", "EGTTEI"],"type": ["AUA", "ES", "ES", "FIR"]}
-------------------------------------------------------------------------------------------------
24   |  22/10/2020      | {"name": ["LFBBRL1", "LFBMC2", "LFBBTT6", "LFTTN8", "EGTTEI"],"type": ["AUA", "ES", "ES", "FIR"]}
-------------------------------------------------------------------------------------------------
25   |  22/10/2020      | {"name": ["LFBGTH4", "LFBMC2", "LFFFE7", "LFTTN8", "EGTTEI"],"type": ["AUA", "ES", "ES", "FIR"]}

I have a table (named profile) in my Postgres database, which has 3 columns: ID, Day, Ftfm_profile of type jsonb, I tried to extract the row where the profile name (ftfm_profile->'name') begins with ‘LFBB’ ( sql: LFBB%) using the wildcard as following:

select * from public.profile where ftfm_profile ->'name' ? 'LFBB%'

the expected result:

-------------------------------------------------------------------------------------------------
24   |  22/10/2020      | {"name": ["LFBBRL1", "LFBMC2", "LFBBTT6", "LFTTN8", "EGTTEI"],"type": ["AUA", "ES", "ES", "FIR"]}
-------------------------------------------------------------------------------------------------

I can’t seem to find the solution, thanks for your help

Answer

One option unnests the json arrray in an exists subquery:

select * 
from public.profile 
where exists (
    select 1
    from jsonb_array_elements_text(ftfm_profile ->'name') as e(name) 
    where e.name like 'LFBB%'
)

Advertisement

Answer