Segment.Road is varchar not struct

[BruceHarold]

Hi, in the January edition I find that the segment/road column is varchar, not struct, which makes it hard to parse.
Thanks

┌────────────────────────────┬─────────────────────────────────────────────────────────────────────────────────────────┐ │ id │ road │ │ varchar │ varchar │ ├────────────────────────────┼─────────────────────────────────────────────────────────────────────────────────────────┤ │ 0879a14070ffffff042f7bce… │ {"class":"unknown"} │ │ 0899a14070c7ffff0445fb63… │ {"class":"driveway"} │ │ 0879a14070ffffff045df8b4… │ {"class":"secondary","roadNames":{"common":[{"language":"local","value":"Route 009"}]}} │ │ 0879a14028ffffff0437ffe6… │ {"class":"unclassified"} │ │ 0889a140283fffff0447fe2a… │ {"class":"residential"} │ │ 0899a140282fffff043f7e3f… │ {"class":"residential"} │ │ 08a9a140282cffff04577f38… │ {"class":"residential"} │ │ 0869a1402fffffff0467ff2f… │ {"class":"residential"} │ │ 0899a140282fffff044f7f4a… │ {"class":"tertiary","roadNames":{"common":[{"language":"local","value":"Route 003"}]}} │ │ 08a9a140282c7fff04377f67… │ {"class":"tertiary","roadNames":{"common":[{"language":"local","value":"Route 003"}]}} │ ├────────────────────────────┴─────────────────────────────────────────────────────────────────────────────────────────┤ │ 10 rows 2 columns │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

[BruceHarold]

I found the struct_pack function to make a struct but ideally the data should come as struct.
The struct_pack function seems not to recursively convert varchars into the parent struct.

[jwass]

This is a known issue due to some of the dynamic "oneOf" typing in the current JSON schema (https://github.com/OvertureMaps/schema/blob/main/schema/transportation/segment.yaml#L147-L160). These don't translate well to Parquet which requires strict typing so the current solution casts to a JSON string unfortunately. We are working to fix these in the schema and you'll soon see the struct you expect.

However you can use the JSON functions in Duckdb (https://duckdb.org/docs/extensions/json.html).

Here's an example:

D select id, cast(json_extract(road, '$.class')as varchar) from read_parquet('./overture-road.parquet') limit 10;
┌──────────────────────────────────┬────────────────────────────────────────────────┐
│                id                │ CAST(json_extract(road, '$.class') AS VARCHAR) │
│             varchar              │                    varchar                     │
├──────────────────────────────────┼────────────────────────────────────────────────┤
│ 086b4aa2cfffffff0476be1817546ec0 │ "tertiary"                                     │
│ 086b4aa2cfffffff045477790218f3fa │ "residential"                                  │
│ 088b4aa2c81fffff047b353827354c71 │ "unknown"                                      │
│ 08ab4aa2c801ffff04767d6191fced0d │ "tertiary"                                     │
│ 088b4aa2c81fffff047a7f69a5d49fe0 │ "unknown"                                      │
│ 088b4aa2c81fffff04117d4c75e19e8b │ "residential"                                  │
│ 088b4aa2c81fffff04103d6b6a31a508 │ "residential"                                  │
│ 089b4aa2c80fffff043377597c9a488a │ "residential"                                  │
│ 089b4aa2c80fffff04533d4f76f68e3f │ "residential"                                  │
│ 088b4aa2c81fffff0474fda75c6669d4 │ "tertiary"                                     │
├──────────────────────────────────┴────────────────────────────────────────────────┤
│ 10 rows                                                                 2 columns │
└───────────────────────────────────────────────────────────────────────────────────┘

[BruceHarold]

Thank you Jacob, looking forward to developments in the schema. Ultimately I would like only core SQL to unpack things.

[BruceHarold]

March 12 release is still varchar for roads :-(