Skip to main content
Solved

Having Issue with Array Containing Headers and Data Separately

H

Hi, I am using the census bureau's API to gather population data for a county, and the data returns in the response as two separate arrays. One array contains the headers, and the other contains the data:

 

[["NAME","B01001_001E","state","county"],

["Spokane County, Washington","546040","53","063"]]

 

I am mainly having issues with transforming the data in this format as it isn't what outputs normally look like when using other REST APIs. Using a JSONfragmenter will correctly assign these as two distinct arrays, but I can't seem to figure out how to associate the corresponding values to build a table from these arrays.

Best answer by caracadrian

I can think of two options. You start with JSONFlattener that creates the arrays, and expose array{}.array{} via AttributeExposer.

Option 1: use AttributeCreator. For New Attribute use Attribute Value array{0}.array{x} and for Attribute Value use array{1}.array{x} for x=[0..n].

AttributeCreator Array_arrayYou can create a custom transformer that uses ListElementCounter (on a copied and exploded array{} - array{} has a count of 2, you need the count of array{}.array{}) and iterate through all elements to create the correct number of attributes.

Option 2: use JSONTempleter to create a correct JSON with template:

{
    fme:get-attribute("array{0}.array{0}"):fme:get-attribute("array{1}.array{0}"),
    fme:get-attribute("array{0}.array{1}"):fme:get-attribute("array{1}.array{1}"),
    fme:get-attribute("array{0}.array{2}"):fme:get-attribute("array{1}.array{2}"),
    fme:get-attribute("array{0}.array{3}"):fme:get-attribute("array{1}.array{3}")
}

if you know you have a fixed number of attributes in the API response.

Then JSONFlattener to get attributes.

I also attached a sample Workspace.

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

4 replies

redgeographics
Celebrity
Forum|alt.badge.img+47

It's an ugly hack, but I think it'll work. Remove all [ and ] characters and then write to CSV with a FeatureWriter, making sure the line with the header is the first line in the CSV, then use a FeatureReader to read it back in with the CSV reader.

caracadrian
Contributor
Forum|alt.badge.img+22
  • caracadrianContributor
  • Contributor
  • Best Answer
  • February 8, 2023

I can think of two options. You start with JSONFlattener that creates the arrays, and expose array{}.array{} via AttributeExposer.

Option 1: use AttributeCreator. For New Attribute use Attribute Value array{0}.array{x} and for Attribute Value use array{1}.array{x} for x=[0..n].

AttributeCreator Array_arrayYou can create a custom transformer that uses ListElementCounter (on a copied and exploded array{} - array{} has a count of 2, you need the count of array{}.array{}) and iterate through all elements to create the correct number of attributes.

Option 2: use JSONTempleter to create a correct JSON with template:

{
    fme:get-attribute("array{0}.array{0}"):fme:get-attribute("array{1}.array{0}"),
    fme:get-attribute("array{0}.array{1}"):fme:get-attribute("array{1}.array{1}"),
    fme:get-attribute("array{0}.array{2}"):fme:get-attribute("array{1}.array{2}"),
    fme:get-attribute("array{0}.array{3}"):fme:get-attribute("array{1}.array{3}")
}

if you know you have a fixed number of attributes in the API response.

Then JSONFlattener to get attributes.

I also attached a sample Workspace.

ebygomm
Influencer
Forum|alt.badge.img+31
  • ebygommInfluencer
  • Influencer
  • February 8, 2023

I'm sure it's possible in an XMLXQueryExtractor but it's too early in the morning to wrestle with that!

It's very straightforward in python, just requires exposing the attributes afterwards

import fme
import fmeobjects
import json
 
def processFeature(feature):
    m = json.loads(feature.getAttribute('json_example'))
    arraylength = len(m[0])
    for i in range(0,arraylength):
        feature.setAttribute(m[0][i],m[1][i])

image

H
caracadrian wrote:

I can think of two options. You start with JSONFlattener that creates the arrays, and expose array{}.array{} via AttributeExposer.

Option 1: use AttributeCreator. For New Attribute use Attribute Value array{0}.array{x} and for Attribute Value use array{1}.array{x} for x=[0..n].

AttributeCreator Array_arrayYou can create a custom transformer that uses ListElementCounter (on a copied and exploded array{} - array{} has a count of 2, you need the count of array{}.array{}) and iterate through all elements to create the correct number of attributes.

Option 2: use JSONTempleter to create a correct JSON with template:

{
    fme:get-attribute("array{0}.array{0}"):fme:get-attribute("array{1}.array{0}"),
    fme:get-attribute("array{0}.array{1}"):fme:get-attribute("array{1}.array{1}"),
    fme:get-attribute("array{0}.array{2}"):fme:get-attribute("array{1}.array{2}"),
    fme:get-attribute("array{0}.array{3}"):fme:get-attribute("array{1}.array{3}")
}

if you know you have a fixed number of attributes in the API response.

Then JSONFlattener to get attributes.

I also attached a sample Workspace.

Thank you so much!  This ended up being a great solution.

Reply


Most helpful members this week

Community Stats

30,845
Posts
116,935
Replies
38,669
Members
Terms & ConditionsCookie settings

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