1

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" } ] } } ] } } 
4
  • Maybe GDAL cannot support table valued function like json_each "The json_each(X) and json_tree(X) table-valued functions walk the JSON value provided as their first argument and return one row for each element." I recommend to write mail to gdal-dev mailing list and ask. Include a link to this question. Commented Mar 15, 2023 at 16:02
  • Maybe explore the nested attributes open options Commented Mar 15, 2023 at 21:34
  • Actually, looks like FLATTEN_NESTED_ATTRIBUTES doesn't work with doubly nested attributes, i.e a list containing a dict like your example. Commented Mar 15, 2023 at 21:51
  • Thanks for your answers. I created an issue on GDAL github: https://github.com/OSGeo/gdal/issues/7464 Commented Mar 16, 2023 at 10:42

1 Answer 1

1

The issue was fixed and there is a workaround:

You can workaround the issue by adding extra parenthesis around json_each, that is

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 

I hope it helps, thanks again.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.