How to search in array with LIKE operator

Question

 id |   name   |          ipAddress           
----+----------+-------------------------
  1 | testname | {192.168.1.60,192.168.1.65}

I want to search ipAddress with LIKE. I tried:

{'$mac_ip_addresses.ip_address$': { [OP.contains]: [searchItem]}},

This one also:

{'$mac_ip_addresses.ip_address$': { [OP.Like] : { [OP.any]: [searchItem]}}},

The data type of ipAddress is text[]. I want to search in ipAddress with LIKE. searchItem contains the IP that need to be searched in the ipAddress field so I want to search in array with LIKE.

score 5 · Answer 1 · answered Apr 02 '19 at 17:35

I don't know Sequelize but I can answer from postgres side.

There is no short syntax to search for a pattern inside array in PostgreSQL.

If you want to check pattern for each array element individually, then you need to unfold the array using unnest:

SELECT id, name, ipaddress
FROM testing
WHERE EXISTS (
  SELECT 1 FROM unnest(ipaddress) AS ip
  WHERE ip LIKE '8.8.8.%'
);

If the array is frequently searched this way, it's better to store the data in normalized form.

However, there is a short syntax (plus GIN index support) for for equality based search (see @> and other operators here).

SELECT id, name, ipaddress
FROM testing
WHERE ipaddress @> ARRAY['8.8.8.8'];

What if we want to just check that the first element in each array matches? — Hadi, Oct 26 '21 at 22:26

Erwin Brandstetter · Answer 2 · 2019-04-04T22:19:59.017

What you asked

~~ is the operator used internally to implementing SQL LIKE. There is no commutator for it - no operator that works with left and right operand switched. That's the one you'd need for your attempt to use the ANY construct with the pattern to the left. Related:

You can create the operator, though, and it's pretty simple:

CREATE OR REPLACE FUNCTION reverse_like (text, text)
  RETURNS boolean LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT $2 LIKE $1';

CREATE OPERATOR <~~ (function = reverse_like, leftarg = text, rightarg = text);

Inspired by Jeff Janes' idea here:

Match string pattern to any array element

Then your query can have the pattern to the left of the operator:

SELECT *
FROM   mac_ip_addresses
WHERE  '192.168.2%.255' <~~ ANY (ipaddress);

Simple, but considerably slower than the EXISTS expression demonstrated by filiprem.

Then again, either query is excruciatingly slow for big tables, since neither can use an index. A normalized DB design with a n:1 table holding one IP each would allow that. It would also occupy several times the space on disk. Still, the much cleaner implementation ...

While stuck with your current design, there is still a way: create a trigram GIN index on a text representation of the array and add a redundant, "sargable" predicate to the query additionally. Confused? Here's the recipe:

First, trigram indexes? Read this if you are not familiar:

PostgreSQL LIKE query performance variations

Neither the cast from text[] to text nor array_to_string() are immutable. But we need that for an expression index. Long story short, fake it with an immutable wrapper function:

CREATE OR REPLACE FUNCTION f_textarr2text(text[]) 
  RETURNS text LANGUAGE sql IMMUTABLE AS $$SELECT array_to_string($1, ',')$$;

CREATE INDEX iparr_trigram_idx ON iparr
USING gin (f_textarr2text(iparr) gin_trgm_ops);

Related answer with the long story (and why it's safe):

Indexing an array for full text search

Then your query can be:

SELECT *
FROM   mac_ip_addresses
WHERE  NOT ('192.168.9%.255' <~~ ANY (ipaddress))
AND    f_textarr2text(ipaddress) LIKE '192.168.9%.255';  -- logically redundant

The added predicate is logically redundant, but can tap into the power of the trigram index.
Much faster for big tables. Still a bit faster, yet:

SELECT *
FROM   mac_ip_addresses
WHERE  EXISTS (SELECT FROM unnest(ipaddress) ip WHERE ip LIKE '192.168.9%.255')
AND    f_textarr2text(ipaddress) LIKE '192.168.9%.255';

But that's minor now.

db<>fiddle here

I addressed the question asked, as I took an interest. Might be of interest to the general public. Most probably not what you need, though.

What you need

I want to search in ipAddress with LIKE. searchItem contains the IP that need to be searched in the ipAddress field so I want to search in array with LIKE.

That should probably read:

"I want to search a given IP address (searchItem) in the array ipAddress. My first idea is to use LIKE ..."

Well, LIKE is for pattern matching. To find complete IP addresses in an array, it's the wrong tool. filiprem's second query with array operators is the way to go. Probably good enough.

Using the built-in data type cidr instead of text would be better. And the ip4 data type of the additional ip4r module would be much better, yet. All in combination with standard array operators like demonstrated.

Finally, converting IPv4 addresses to integer and using that with the additional inrarray module should be stellar - as far as performance is concerned.

How to search in array with LIKE operator

2 Answers2

What you asked

What you need