using ogr2ogr I want to process a geojson file that has a JSON field (see minimal file below).
I tried the following command to extract the description corresponding to group a from the JSON field:
ogr2ogr -t_srs EPSG:4326 \ test_minimal.geojson minimal.geojson \ -sql "SELECT *, json_extract(value, '$.description') AS description FROM layer, json_each(jsonprop) WHERE json_extract(value, '$.group')='a'" \ -dialect sqlite \ This seemed to work great at first, but in fact, when an object has multiple entries in the jsonprop they all show up (it creates multiple objects due to the joining). So I figured out the WHERE part is not working, I tried this command which should create an empty geojson:
ogr2ogr -t_srs EPSG:4326 \ test_minimal.geojson minimal.geojson \ -sql "SELECT *, json_extract(value, '$.description') AS description FROM layer, json_each(jsonprop) WHERE 1=2" \ -dialect sqlite \ But this still gives every result without filtering... Same for LIMIT and others. So it feels like ogr2ogr is not processing the rest of the command, do anyone knows why?
Here is a minimal file:
{ "layer" : { "type" : "FeatureCollection", "crs" : { "type" : "name", "properties" : { "name" : "EPSG:25833" } }, "features" : [ { "type" : "Feature", "geometry" : { "type" : "Polygon", "coordinates" : [ [ [ -83202, 6837229 ], [ -63984, 6795019 ], [ -35345, 6827242 ], [ -83202, 6837229 ] ] ] }, "properties" : { "id": 1, "jsonprop" : [ { "description" : "Obj1 jsonprop in group a", "group" : "a" }, { "description" : "Obj1 jsonprop in group b", "group" : "b" } ] } }, { "type" : "Feature", "geometry" : { "type" : "Polygon", "coordinates" : [ [ [ 3202, 6837229 ], [ 23984, 6795019 ], [ 55345, 6827242 ], [ 3202, 6837229 ] ] ] }, "properties" : { "id": 2, "jsonprop" : [ { "description" : "Obj2 jsonprop in group a", "group" : "a" } ] } } ] } }
FLATTEN_NESTED_ATTRIBUTESdoesn't work with doubly nested attributes, i.e a list containing a dict like your example.