Search code examples
sqlpostgresqlhstore

postgresql get the per-row number of keys of hstore data if key is in List "foo", "bar"

i try to count per row, how keys are in hstore data column.

array_length(akeys(tags), 1) as num_keys

this works fine for all tags.

In table nodes are many different more tags - I will just number per row of tags in my SELECT.

"name"=>"Campus", "amenity"=>"restaurant", "wheelchair"=>"yes"

count only "name" -> result 1

SELECT 
 id,
 st_x(ST_Transform(geom,4326)) AS lon, 
 st_y(ST_Transform(geom,4326)) AS lat,
 array_length(akeys(tags), 1) as num_keys,
 tags->'name' AS name,
 tags->'amenity' AS amenity,
 tags->'addr:street' AS street,
 tags->'addr:housenumber' AS housenumber,
 tags->'addr:postcode' AS postcode,
 tags->'addr:city' AS city,
 tags->'cuisine' AS cuisine,
 tags->'beer_garden' AS beer_garden,
 tags->'outdoor_seating' AS outdoor_seating,
 tags->'smoking' AS smoking,
 tags->'brewery' AS brewery,
 tags->'website' AS website,
 tags->'internet_access' AS wlan,
 tags->'phone' AS phone,
 tags->'email' AS email,
 tags->'opening_hours' AS opening_hours
FROM 
 nodes 
WHERE 
 tags->'amenity' IN ('pub','bar','nightclub','biergarten','cafe','restaurant')
AND
 tags->'name' IS NOT NULL;
Solution

  • The solution is:

    SELECT
 CASE WHEN 'email' = ANY (akeys(tags)) THEN 1 ELSE 0 END +
 CASE WHEN 'opening_hours' = ANY (akeys(tags)) THEN 1 ELSE 0 END
 AS count_tags
FROM
 nodes