JSON: Parsing from label:value structure

Support and Helpdesk
1

All,

I have this chunk of JSON:

"metadata" : [

{ "label" : "Accession Number", "value" : "03547" },

{ "label" : "File Name", "value" : "03547-2" },

{ "label" : "Call number", "value" : "D657 L726t / 1-SIZE" }

and I am trying to get values for, e.g., Accession Number, File Name, Call number.

So, for the label “Accession Number” I want to retrieve the value of 03547

Trying everywhichway, No Joy.

The structure is sort of a dual key/value pair. Not sure how to parse. Searching for recipe...

Advice appreciated,

Mark

2

What’s throwing me off here is that the JSON is literally saying “label” and “value” when “Accession Number” just is the label/name for an object (once the JSON is parsed out to JS) with its value being “03547”.

So it’s not just “get the value for Accession Number”:

parseJson(value).get("Accession Number")

Somehow, I need to read down the tree here?

metadata –> “label” –> with value of “Accession Number” –> “value” –> The Value I’m Looking For

3

I had a couple rounds with CoPilot on this!

filter(parseJson(value)["sequences"][0]["canvases"][0]["metadata"], m, m["label"] == "Accession Number")[0]["value"]

Good to have a working example. Now studying it to learn exactly what it does.

4

Have you consulted our OpenRefine Docs for the parseJson() function?

5

With a cell containing the following string:

{"metadata" : [{ "label" : "Accession Number", "value" : "03547" },{ "label" : "File Name", "value" : "03547-2" },{ "label" : "Call number", "value" : "D657 L726t / 1-SIZE" }]}

these two expressions should provide some ideas for starting points:

value.replace('"label" :','').replace(', "value"','').replace('{"metadata" : [','').replace('},{',',').parseJson()['File Name']

filter(forEach(value.parseJson().metadata,i,if(i.label =='File Name',i.value,'')),x,not(isBlank(x)))[0]

The first is kind of a hack which uses string manipulation to convert the JSON array to a JSON object with the label values as keys.
The second iterates through the array and matches the label using an If function.

Tom