Skip to main content
Best Answer

How do I use a JSON element as attribute value?

  • November 12, 2020
  • 8 replies
  • 412 views
C
Forum|alt.badge.img

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 

Best answer by ebygomm

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

 

CaptureCapture

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

8 replies

david_r
Celebrity
  • david_rCelebrity
  • November 12, 2020

Here's one way using two JSONFragmenters in parallel:

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

C
Forum|alt.badge.img

Here's one way using two JSONFragmenters in parallel:

jsonfragmenterYou'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"[*]][*]?

ebygomm
Influencer
Forum|alt.badge.img+46
  • ebygommInfluencer
  • Influencer
  • Best Answer
  • November 12, 2020

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

 

CaptureCapture

jdh
Contributor
Forum|alt.badge.img+40
  • jdhContributor
  • Contributor
  • November 12, 2020

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 = j["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

 

jdh
Contributor
Forum|alt.badge.img+40
  • jdhContributor
  • Contributor
  • November 12, 2020

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

 

CaptureCapture

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.

david_r
Celebrity
  • david_rCelebrity
  • November 13, 2020

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.

C
Forum|alt.badge.img

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

 

CaptureCapture

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

C
Forum|alt.badge.img

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).

Community Stats

32,180
Posts
122,164
Replies
39,853
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.