BUG: reading file with JSON ogr subtype is broken with use_arrow=True

[jorisvandenbossche]

Issue with #556

Create a small Parquet file with a nested (list of struct) column:

import shapely
import pyarrow as pa
import pyarrow.parquet as pq

table = pa.table({
    "geometry": shapely.to_wkb(shapely.points(np.ones((3, 2)))), 
    "col_flat": [0, 1, 2], 
    "col_nested": [[{"a": 1, "b": 2}]*2]*3
})
pq.write_table(table, "test_nested.parquet")

Reading with pyogrio works with default, but fails with arrow:

>>> pyogrio.read_dataframe("test_nested.parquet")
   col_flat                            col_nested     geometry
0         0  [{'a': 1, 'b': 2}, {'a': 1, 'b': 2}]  POINT (1 1)
1         1  [{'a': 1, 'b': 2}, {'a': 1, 'b': 2}]  POINT (1 1)
2         2  [{'a': 1, 'b': 2}, {'a': 1, 'b': 2}]  POINT (1 1)

>>> pyogrio.read_dataframe("test_nested.parquet", use_arrow=True)
...
File ~/scipy/repos/pyogrio/pyogrio/geopandas.py:336, in read_dataframe(path_or_buffer, layer, encoding, columns, read_geometry, force_2d, skip_features, max_features, where, bbox, mask, fids, sql, sql_dialect, fid_as_index, use_arrow, on_invalid, arrow_to_pandas_kwargs, **kwargs)
    334 for ogr_subtype, c in zip(meta["ogr_subtypes"], df.columns):
    335     if ogr_subtype == "OFSTJSON":
--> 336         df[c] = df[c].map(json.loads, na_action="ignore")
    338 if fid_as_index:
    339     df = df.set_index(meta["fid_column"])
...
TypeError: the JSON object must be str, bytes or bytearray, not int

I think the problem lies in zip(meta["ogr_subtypes"], df.columns), where the order of the columns in meta is not guaranteed to be the same as df.columns.
In this case I think because the geometry column is at the start of the file, and is not included in the meta?

[jorisvandenbossche]

An additional problem is that GDAL seems to expand simple struct columns into separate fields:

>>> table = pa.table({
...     "geometry": shapely.to_wkb(shapely.points(np.ones((3, 2)))),
...     "col_flat": [0, 1, 2],
...     "col_struct": [{"a": 1, "b": 2}]*3,
...     "col_nested": [[{"a": 1, "b": 2}]*2]*3
... })
... pq.write_table(table, "test_nested.parquet")
>>> 
>>> pyogrio.read_info("test_nested.parquet")
{'layer_name': 'test_nested',
 'crs': None,
 'encoding': 'UTF-8',
 'fields': array(['col_flat', 'col_struct.a', 'col_struct.b', 'col_nested'],
       dtype=object),
 'dtypes': array(['int64', 'int64', 'int64', 'object'], dtype=object),
 'ogr_types': ['OFTInteger64', 'OFTInteger64', 'OFTInteger64', 'OFTString'],
 'ogr_subtypes': ['OFSTNone', 'OFSTNone', 'OFSTNone', 'OFSTJSON'],
...

and this will also create a mismatch between the order of the "ogr_subtypes" and df.columns
(when using use_arrow=True at least, because in the default read_dataframe, it seems the resulting df also has those flattened columns. Not sure if that is a separate bug? At least it gives some inconsistency between use_arrow True vs False)

So we should probably iterate over meta["fields"] instead of df.columns, and then check if that field name is available (or use a try/except around the parsing)

[theroggy]

Probably cleaner to move the "additional problem" to a seperate issue and close this one?