QGIS: Converting a { "DisplayValue": "Value being displayed", "Value": "value" } string format from a GeoJSON file

Question

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.

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.

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:

---

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.

Answer 1

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.

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.

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

:

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

• 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)