COPY(
SELECT hierarchies
 FROM read_parquet('s3://overturemaps-us-west-2/release/2024-07-22.0/theme=divisions/type=division/*', filename=true, hive_partitioning=1)
WHERE subtype IN ('macrohood', 'neighborhood', 'microhood')
   AND country = 'US'
   AND region = 'US-NY'
   AND names.primary = 'SoHo'
   ) TO 'soho.json';

{
"hierarchies": [
 [
   {
     "division_id": "0850c861bfffffff01aeb407d56d3441",
     "subtype": "country",
     "name": "United States"
   },
   {
     "division_id": "085c44b57fffffff01094a5e363404df",
     "subtype": "region",
     "name": "New York"
   },
   {
     "division_id": "0856ca517fffffff016953f454dd7d41",
     "subtype": "locality",
     "name": "City of New York"
   },
   {
     "division_id": "0856c1257fffffff01037a130b7e12e1",
     "subtype": "neighborhood",
     "name": "SoHo"
   }
 ]
]
}

WITH RECURSIVE descendants(id, subtype, names, parent_division_id, geometry) AS (
  SELECT id, subtype, names, parent_division_id, geometry
FROM read_parquet('s3://overturemaps-us-west-2/release/2024-07-22.0/theme=divisions/type=division/*', filename=true, hive_partitioning=1)
   WHERE id = '085c44b57fffffff01094a5e363404df'
  
   UNION ALL
  
  SELECT next_level.id, next_level.subtype, next_level.names, next_level.parent_division_id, next_level.geometry
    FROM read_parquet('s3://overturemaps-us-west-2/release/2024-07-22.0/theme=divisions/type=division/*', filename=true, hive_partitioning=1) AS next_level
    JOIN descendants
      ON next_level.parent_division_id = descendants.id
)
  SELECT id, subtype, names.primary AS primary_name, parent_division_id, ST_GeomFromWKB(geometry) AS geometry
    FROM descendants
    ```