Postgres Query Of An Array Using Like

I am querying a database in Postgres using psql. I have used the following query to search a field called tags that has an array of text as it's data type: select count(*) from pla

Solution 1:

Use the unnest() function to convert array to set of rows:

SELECTcount(distinct id)
FROM (
    SELECT id, unnest(tags) tag
    FROM planet_osm_ways) x
WHERE tag LIKE'A%'

The count(dictinct id) should count unique entries from planet_osm_ways table, just replace id with your primary key's name.

That being said, you should really think about storing tags in a separate table, with many-to-one relationship with planet_osm_ways, or create a separate table for tags that will have many-to-many relationship with planet_osm_ways. The way you store tags now makes it impossible to use indexes while searching for tags, which means that each search performs a full table scan.

Solution 2:

Here is another way to do it within the WHERE clause:

SELECTCOUNT(*)
FROM planet_osm_ways 
WHERE (
  0< (
    SELECTCOUNT(*) 
    FROMunnest(planet_osm_ways) AS planet_osm_way
    WHERE planet_osm_way LIKE'A%'
  )
);