Skip to main content
Solved

How do I use a JSON element as attribute value?

  • November 12, 2020
  • 8 replies
  • 331 views

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

View original
Did this help you find an answer to your question?

8 replies

david_r
Evangelist
  • 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.


Forum|alt.badge.img
david_r wrote:

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+32
  • 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+28
  • 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+28
  • Contributor
  • November 12, 2020
ebygomm wrote:

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
Evangelist
  • November 13, 2020
clickingbuttons wrote:

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.


Forum|alt.badge.img
ebygomm wrote:

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 👍


Forum|alt.badge.img
clickingbuttons wrote:

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings