10

I am a developer and have a tool that generates a geojson asset for our data team to work with using the QGIS app. Due to the original source of the data, these features have some attributes that are represented as complex hierarchical data, impossible to project into a flat schema.

We need to be able to view / edit the attribute field as effectively a JSON blob, instead of a string literal.

What facilities are available to do this in QGIS? Are there any field editing plugins that would support such a job? Ideally some kind of popup JSON editor with document format validation and collapsible nodes, similar to how Visual Studio (Code) editors work.

Alternatively some other workflow that would enable us to manage this data in an external tool and re-inject it into the source feature.

UPDATE: Got Postgres spun up and created a JSON column from an existing text column containing JSON. QGIS hides it in the attribute table. Any further recommendations on working with JSON attributes directly in the DB?

5
  • 1
    Sounds like jobs for postgres with JSON support postgresql.org/docs/9.4/static/datatype-json.html
    – Mapperz
    Jan 23, 2018 at 16:26
  • Did look at Postgres, not sure how it would integrate with the QGIS editor, but would be an ideal situation to work with, everything centralised. Jan 23, 2018 at 16:36
  • I'm also interested in this, but I haven't seen anything that facilitates this in QGIS. SQLite also has support for JSON, by the way: sqlite.org/json1.html
    – arjan
    Jan 23, 2018 at 16:43
  • It's not the storage that's the problem, it's the editor. We effectively have a team of data engineers cleaning up geojson files and passing them over to us. I'd be happy to have this backed by a database, but we would still need rich editor support for this to be usable. Might just learn Python and write a plugin :) Jan 23, 2018 at 16:55
  • @TristanRhodes If you do write a plugin (or find another solution), please let us know!
    – arjan
    Mar 26, 2018 at 11:17

3 Answers 3

9

In recent QGIS versions (> 3.3), if you have a JSON field in a PostgreSQL database, you can use the map_get function anywhere with the Expression Dialog to extract a specific value.

E.g if you have a JSON field called "extraData" and the contents are:

{ 
        "param1": "Some string",
        "param2": "Another string"
}

then map_get(extraData,'param1') would produce Some string

I suppose (though I haven't tried it) you can recursively use the function to retrieve deeply nested values.

There are other functions such as the from_json function that returns the entire contents. Take a look at the Maps section inside the Expression Dialog.

4

Suppose we have a field/column called 'location' with the following content/value (it is a string but has json struct) into QGIS:

{
    "address": "A-319, Hornos, Jaén, Andalusia, 23292, Spain",
    "continent": "Europe",
    "country": "Spain",
    "region": "Andalucía",
    "subregion": "Jaén"
}

and you want access to 'subregion' property, so the expression:

map_get(json_to_map("location"),'subregion')

will return 'Jaén'

Tested with geopackage database.

1
3

Check this: https://github.com/qgis/QGIS/pull/7869 Now the json/jsonb type attributes are visible using QGIS 3.3.0 Nighly (master dev version)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.