How do I use a JSON element as attribute value?

I have a JSON file, structured like this:

{
  "W-Number": {
    "EP1": �
      {
        "timestamp": 1407859200000,
        "value": 1.35
      },
      {
        "timestamp": 1407862800000,
        "value": 1.44
      }
    ],
    "EP2": �
      {
        "timestamp": 1407859200000,
        "value": 1.58
      },
      {
        "timestamp": 1407862800000,
        "value": 1.63
      }
    ]
  }
}

I want to turn it into a table, like this

W-Number | timestamp | value
EP1 | 1407859200000 | 1.35
EP1 | 1407862800000 | 1.44
EP2 | 1407859200000 | 1.58
EP2 | 1407862800000 | 1.63

What I managed so far: used JSONFlattener, then AttributeExposer to expose (see below*). Then I used a ListExploder; it works when I specify e.g. W-Number-EP1{}, which gives me a table for the timestamp and value of EP1, however I require a single Table for all EP's that might exist, without exploding the list for each manually. Would that be possible somehow?

exposed

Page 1 / 1

Here's one way using two JSONFragmenters in parallel:

You'll maybe want to add e.g. an AttributeCreator after each JSONFragmenter to identify if each line is from EP1 or EP2.

Here's one way using two JSONFragmenters in parallel:

You'll maybe want to add e.g. an AttributeCreator after each JSONFragmenter to identify if each line is from EP1 or EP2.

Yes, but would basically have the same effect as using a bunch of ListExploders, which would also require manually inserting one for each "EP". I'd kinda need something like.. ["W-Number"]["EP"

]

I'd use a JSON fragmenter to break into EP fragments then a list exploder

Capture Capture

It can probably be done in xquery using the XMLXQueryExtractor, however I dropped into python instead.

import json
 
class FeatureProcessor(object):
    def __init__(self):
        pass
    def input(self,feature):
        j = json.loads(feature.getAttribute('json'))
        w = ja"W-Number"]
        for k,v in w.items():
            feature.setAttribute("W-Number",k)
            for x in v:
                for l,m in x.items():
                    feature.setAttribute(l,m)
                self.pyoutput(feature)
                
    def close(self):
        pass

I'd use a JSON fragmenter to break into EP fragments then a list exploder

Capture Capture

For some reason when I originally tried this, the JSONFragmenter rejected the feature.

Trying it again, it's now working, and the best solution. All you need to do afterwards is rename the json_index attribute to W-Number.

Yes, but would basically have the same effect as using a bunch of ListExploders, which would also require manually inserting one for each "EP". I'd kinda need something like.. ["W-Number"]["EP"

]

I don't understand the comparison with ListExploders. I understand wanting to avoid them, as they are very slow if you have lots of data, but the JSONFragmenter should be much faster.

I'd use a JSON fragmenter to break into EP fragments then a list exploder

Capture Capture

Wonderful, this got rid of the JSONFlattener and AttributeExposer as well 👍

Yes, but would basically have the same effect as using a bunch of ListExploders, which would also require manually inserting one for each "EP". I'd kinda need something like.. ["W-Number"]["EP"

]

The output (and effort to make/edit the workspace) would be the same. I got kinda stuck trying to use the AttributeExposer output, but it wasn't even necessary in the end (see the other answer).

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded