ILIKE query inside arrays of objects in jsonb column-CodePudding

I have a jsonb data column made up of various objects. Here is an example.

{"LicensePlates": {"Type": "LicensePlateList", "Value": ["XXXXX"]}, "SubscriptionInfo": {"Type": "SubscriptionInfoList", "Value": [{"id": "1", "lastname": "rossi", "firstname": "paola"}, {"id": "2", "lastname": "Scicolone", "firstname": "Paolo"}]}}

Now I'm searching a specific info in SubscriptionInfo key like this:

SELECT * FROM column WHERE (data -> 'SubscriptionInfo') -> 'Value' @> '[{"firstname": "Paolo"}]';

It works fine, but I would also like to search for "partial" information, eg. searching for the string "pa" (using ILIKE or anything else similar) should return the entire record. it's possible?

CodePudding user response：

You have two options (demo)

convert data to lower case

select * 
from 
  test 
where 
  lower(data -> 'SubscriptionInfo' ->> 'Value')::jsonb @> lower('[{"firstname": "paolo"}]')::jsonb;

Use cross join and extract JSON then use ilike

select distinct on (t.id) t.*
from 
  test t
  cross join jsonb_array_elements(data -> 'SubscriptionInfo' -> 'Value') ej
where
  value ->> 'firstname' ilike '%paolo%';

CodePudding user response：

If you are using Postgres 13 or later, you can use a SQL/JSON path expression:

select t.*
from the_table t
where t.data @@ '$.SubscriptionInfo.Value[*].firstname like_regex "paolo" flag "i"'