+1Hello,
I have the JSON string format like this “[[{"name":"Column1","value":"Value1"},{"name":"Column2","value":"Value2"},{"name":"Column3","value":"Value3"}],
[{"name":"Column1","value":"ValueA"},{"name":"Column2","value":"ValueB"},{"name":"Column3","value":"ValueC"}]]”
And I would like to convert it to table like:
------------------------------------------------
Column1 | Column2 | Column3
Value1 | Value2 | Value3
ValueA | ValueB | ValueC
Is there any transformer can help me to do so?
Best answer by geomancer
The JSONFragmenter creates a list (named ‘array’). You can see the list in the Feature Information window. There you can also see the array is not exposed.
It is neccessary to expose the array to proceed. Therefore you need to expose array{}.name and array{}.value in the JSONFragmenter.
After that, the easiest solution is to use the ListKeyValuePairExtractor from FME Hub to add attributes with name array{}.name and value array{}.value . The final step is to expose those attributes (Column1, Column2, Column3).
See the attached workspace.
+5try the json fragmenter followed by an attribute exposer.
+1try the json fragmenter followed by an attribute exposer.
With json fragmenter followed by an attribute exposer. I can restructure it like:
name | value
---------------------------
Column1 | Value1
Column2 | Value2
Column3 | Value3
Column1 | ValueA
Column2 | ValueB
Column3 | ValueC
But I would like to restructure it like:
Column1 | Column2 | Column3
----------------------------------------------
Value1 | Value2 | Value3
ValueA | ValueB | ValueC
I believe FME is capable to restructure it but I am too new on FME so I need some assistance.
Appreciate to any advice.
Thank you so much.
+47The JSONFragmenter creates a list (named ‘array’). You can see the list in the Feature Information window. There you can also see the array is not exposed.
It is neccessary to expose the array to proceed. Therefore you need to expose array{}.name and array{}.value in the JSONFragmenter.
After that, the easiest solution is to use the ListKeyValuePairExtractor from FME Hub to add attributes with name array{}.name and value array{}.value . The final step is to expose those attributes (Column1, Column2, Column3).
See the attached workspace.
+1Thank you very much for your assistance. That really solved my blocker.
I have one more question about the ListKeyValuePairExtractor. When I try to use it, a message pops up requesting the installation of the component. The FME script is intended to run on an FME Server with a scheduler. Are there any precautions I should take when using a component like this?
+47You can read up on custom transformers and security here:
You can find more information on FME Server (FME Flow) and custom transformers here: https://support.safe.com/hc/en-us/articles/25407468358029-Custom-Transformers-and-FME-Flow
+1You can read up on custom transformers and security here:
You can find more information on FME Server (FME Flow) and custom transformers here: https://support.safe.com/hc/en-us/articles/25407468358029-Custom-Transformers-and-FME-Flow
Thank you for the information. Is there any possible way to use a Safe Software provided transformer to replace ListKeyValuePairExtractor? I find ListKeyValuePairExtractor very useful, but I'm concerned about any policies that might prohibit using custom transformers not provided by Safe Software.
+47Certainly 😀
There is a neat FME trick involved here: naming an attribute after the value of another attribute.
Also, to expose the column names (Column1, Column2, Column3) you will have to know their names.
+47When you open ListKeyValuePairExtractor in FME Form you can see that it is a combination of a few FME Transformers, including a PythonCaller which does most of the work.
You would have to study the Python code to ascertain the ListKeyValuePairExtractor is safe to use.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
