Hi Kirsten,

This is an interesting one, that gets into the tricky territory of dynamically defined attribute names and values. 

I’ve put together an example workspace (attached) that can do this without the need for python.
The steps are as follows:

1. Flatten the JSON string using a JSONFlattener and expose the columns{} and rows{}.values{} lists
2. Use a ListExploder to explode the feature on the columns{} list
3. Use a JSONFragmenter to fragment on the rows using the query json[“rows”][*]. This gives us a feature per unique value, that we will aggregate. 
4. Rename the following using an AttributeRenamer, to make our indexes clearer
   1. _json_index → key_index
   2. _element_index → value_index
5. Using an AttributeCreator to dynamically create the attribute name and value:
   1. Output Attribute: @Value(columns)
   2. Value: @Value(rows{@Value(key_index)}.values{@Value(value_index)})
6. Use an Aggregator to aggregate to a unique feature per set of data, using key_index as the group by and merging incoming attributes.

This will create an attribute per header found, with the value attached. 
You can then expose them as required, use a dynamic writer (with a schema feature) to write them, etc…

If you are comfortable with using custom transformers from FME Hub, you can use the ListZipper and ListKeyValuePairExtractor transformers.

This workspace starts the same as the one above from sam.mcdonald.

1. Flatten the JSON string using a JSONFlattener and expose the columns{} and rows{}.values{} lists
2. Use a ListExploder to explode the feature on the columns{} list
3. Use a ListZipper (FME Hub) to combine the columns{} list and the rows{}.values{} list into a new list with key-value-pairs
4. Use a ListKeyValuePairExtractor (FME Hub) to create attribitutes with values from the combined list

Continue as needed as described by sam.mcdonald.

See the attached workspace (FME 2023.1.1)

Note that ListZipper uses a TclCaller and ListKeyValuePairExtractor utilizes a PythonCaller internally.

This is one of the use cases that for me personally, processing the json in python is far simpler than an FME solution

import fme
import fmeobjects
import json


class FeatureProcessor(object):


    def __init__(self):
        pass


    def input(self, feature: fmeobjects.FMEFeature):
        myjson = json.loads(feature.getAttribute('json'))
        columns =  myjson['columns']
        for x in myjson['rows']:
            values =x['values']
            for i,col in enumerate(columns):
                feature.setAttribute(col,values[i])
            self.pyoutput(feature)
    def close(self):

        pass

Hi ​@brown3k ,

You can convert a JSON object consisting of key-value pairs like this to a set of feature attributes with JSONFlattener.

{
   "sub_organisation" : "24",
   "system_id" : "8",
   "type" : "1840",
   "mounted" : "Path",
   "location" : "8",
   "road_id" : "997",
   "offset" : "10.9",
   "side" : "L",
   "geometry" : "POINT (1748664.55420184 5427732.02625559)"
}

A possible way based on the ability of JSONFlattener is, firstly populate the elements in the "columns" array and the "values" array into list attributes with JSONFlattener and ListExploder, convert the list attributes to a JSON object consisting of key (column) value pairs with JSONTemplater, and convert the JSON object to feature attributes with JSONFlattener. You can then expose the attribute (column) names with AttributeExposer, if necessary.

The template expression set in the JSONTemplater: 

{|
    let $k := fme:get-list-attribute("columns{}")
    let $v := fme:get-list-attribute("values{}")
    for $i in (1 to count($k))
    return {$k[$i] : $v[$i]}
|}

See also the attached workspace example to learn more.

{
   "sub_organisation" : "24",
   "system_id" : "8",
   "type" : "1840",
   "mounted" : "Path",
   "location" : "8",
   "road_id" : "997",
   "offset" : "10.9",
   "side" : "L",
   "geometry" : "POINT (1748664.55420184 5427732.02625559)"
}

Great solution! 

​@takashi, thank you. I figured there’d be a way to do it using JSONTemplater but I couldn’t work that bit out.