1

Currently we are trying to to convert a JSON file, which has an attribute table that is too chaotic and needs to be changed. There is certain string format in a few columns that keeps popping up and we want to covert this into one go.

It always looks like this { "DisplayValue": "Value being displayed", "Value": "value" }.

Note: Value being displayed" and value are placeholders, and yes there are encapsulated into "". What we suspected is that these things were supposed to be part of a value map...kinda because there a few columns, that are also supposed to be a date.

For example { "DisplayValue": "2020-03-05T16:37:27.7Z", "Value": "2020-03-05T16:37:27.7Z" }

Also we have something like this: { "diepteNauwkeurigheid": "Onbekend", "dieptePeil": "1 m" } that is something that has the same string format, but this does not seem to be a value map.

What actually needs to be extracted is the value, without the special marks.

I have bit of trouble to convert this in one go. Sure I can make some replace and right left commands but can anyone tell me how do this in one go?

Edit: TO clarify the files we have are GeoJSON. And no, it is not just one single attribute we want, we want multiple columns to have the same kind of string format converted. Here is screenshot, some data is cut because of privacy. enter image description here

Edit 2: To Illustrate further I have for example the procedure to translate the the theme column into the appriate string:


CASE 
WHEN theme= '{ "DisplayValue": "Elektriciteit", "Value": "electricity" }' 
THEN
'electricity'

WHEN theme= '{ "DisplayValue": "Telecommunicatie", "Value": "telecommunications" }'
THEN
'telecommunications'

WHEN theme ='{ "DisplayValue": "Water", "Value": "water" }'
THEN
'water'

WHEN theme ='{ "DisplayValue": "Riolering", "Value": "sewer" }'
THEN
'sewer'

Else
NULL

END

Now this is just for translating the text of ONE Column specifically, I am looking for something more specific that I can apply to all columns.

So it should be something like this, this is just to illustrate what must happen, actual value and Displayed Value are placeholders.

CASE WHEN COLUMN = '{ "DisplayValue": "Displayed Value", "Value": " actual value" }'
THEN
actual value

I hope this helps in helping me find a solution.

Edit 3: Leo M gave me a good solution but there is sadly a problem that has arrived. I get this error message, not all values also seem to be translated.

After the okay button has be clicked my changes has been undone.

enter image description here

enter image description here

Edit 5: This is also the error message in the Field Calculator itself, yes it is on a different column but I had to test it out: enter image description here


Edit 6: Thanks to LeoM the sollution has been found, apparently because I had Null Value's, the expresion did not work so I made a part of a case: For example

Case when ElementTyp IS NOT NULL THEN
string_to_array( "elementTyp" ,'"')[7]
ELSE
ElementTyp = NULL
END

You can replace Null with anything.

2
  • 1
    Also please state more clearly what you goal is. Do you need a shapefile? Do you only need a single attribute? Also, where does the data come from?
    – Erik
    Jun 10, 2020 at 7:00
  • The data comes from a Klip file which then can be converted in Zip file and then extracted. We have two GeoJSON files, one feature collection and one Geojson that only has points. We want this data to be converted to multiple shapefiles, but first the attributes need to be converted to a certain standard. Jun 10, 2020 at 7:35

1 Answer 1

2

EDIT : Same method directly in QGIS, using string_to_array :

For each field, first select only the features for which the value is not null and use the field calculator to create a new field with the expression :

string_to_array( "elemenTyp" ,'"')[7]

It cuts the field at the " and takes the 7th part which should always be the actual value.

enter image description here

enter image description here

Method in Excel or equivalent :

  • Export the attribute table as a .csv file.

  • In Excel, use Text to Column and split at the " symbol. It should work for all your fields as there is always the same number of " in the format { "DisplayValue": "Value being displayed", "Value": "value" } you mention.

enter image description here

  • Result will look like this, your value will always be in the same column :

enter image description here:

  • Do that for all your field, delete useless columns in Excel, reconstitute a clean table with copy-pastes :

enter image description here

  • Save it in .csv and join it back to your layer in QGIS using a unique ID (in your case probably the id or unaZoneId field)
9
  • Thank you for the sollution but is there really no way, to do this in QGIS or at least Python? I am trying to incoperate this step into a future possible procedure (A question comes up for this later) since these attribute tables are kind of build the same. Nice sollution do not get me wrong, but I am looking for something I can Automate later. Jun 10, 2020 at 9:57
  • I edited my answer to show how to do it in Qgis, still considering that you will always have the same template { "DisplayValue": "Value being displayed", "Value": "value" }
    – Léo M
    Jun 10, 2020 at 10:00
  • Your sollution is good, but I got an error with it. I edited my post with that error. Jun 10, 2020 at 10:21
  • Yes, that's because you have NULL values in your fields. First do a selection where elementTyp IS NOT NULL (and same for others fields you're working on).
    – Léo M
    Jun 10, 2020 at 11:22
  • I incoparated the formula into this: Case when ElementTyp IS NOT NULL THEN string_to_array( "elementTyp" ,'"')[7] ELSE ElementTyp = NULL END Jun 10, 2020 at 11:30

Your Answer

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

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