Help with splitting a json array

[Jordi_Agullo]

I am working with Openrefine 3.8 version with the reconciled data from GlobalNames and the information obtained from the Classification. The result is a JSON array with the taxonomy and its hierarchy, such as:

"[{"taxon":"Biota","rank":"unranked"},{"taxon":"Animalia","rank":"kingdom"},{"taxon":"Mollusca","rank":"phylum"},{"taxon":"Gastropoda","rank":"class"},{"taxon":"Heterobranchia","rank":"subclass"},{"taxon":"Euthyneura","rank":"infraclass"},{"taxon":"Tectipleura","rank":"subterclass"},{"taxon":"Eupulmonata","rank":"superorder"},{"taxon":"Stylommatophora","rank":"order"},{"taxon":"Helicina","rank":"suborder"},{"taxon":"Clausilioidei","rank":"infraorder"},{"taxon":"Clausilioidea","rank":"superfamily"},{"taxon":"Clausiliidae","rank":"family"},{"taxon":"Clausiliinae","rank":"subfamily"},{"taxon":"Clausiliini","rank":"tribe"},{"taxon":"Macrogastra","rank":"genus"},{"taxon":"Macrogastra ventricosa","rank":"species"}]"

How can I separate the information with openrefine stored in the cell into columns?, when the rank is the header column and the taxon is the information.

Excuse me, but I'm a bit of a newbie

Any help would be appreciated

[jquartel]

Hi,
When creating a new project from a selected json file, you should find you are using the 'Parse data as JSON files' state in the 'configure parsing options' page. If not (which I think can happen if, say, the file extension is not .json) then select the 'JSON files' option from the list at the bottom left in this page.

You will see the contents of your file in the window and then you'll need to "specify a record path". You do this by hovering your cursor over a section that represents a full record of the table you want to create, making sure the whole record in in a yellow box, as shown here:

Click to acknowledge and then it will show you the columns it will create for the project.

Just click 'Create project' at the top right and you should be good to refine away!

Note that there is slight quirk (which the developers may be looking into) where the column names all start with "_ - ". You can't change this behaviour but you can rename the columns in the project.

[Jordi_Agullo]

Excuse me, I may not have explained myself clearly. I have the information obtained after reconciliation in a single cell in OpenRefine. Like this.

I would need a way o split the information in columns like:

Kingdom | Phylum | Class | ...
Animalia | Mollusca | Gastropoda| ...

I've found this topic Reconcile data from GlobalNames API into separate columns. I manage to create a text string with the terms separated by "|" using the GREL expression, but I don't know how to do the second part.

[Nicolas_VIGNERON]

Hi,

If you have all values separated by a pipe into a single cell, then you can probably do "Edit column" > "Split into several columns" using "|" as the separator (see also https://openrefine.org/docs/manual/columnediting#split-into-several-columns ).

[Jordi_Agullo]

Thank you for your help, I've more or less gotten the information I needed.