• EN
Solved

Having Issue with Array Containing Headers and Data Separately

  • 7 February 2023
  • 4 replies
  • 11 views
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.

icon

Best answer by caracadrian 8 February 2023, 07:07

View original

4 replies

redgeographics
Rank
Userlevel 5
Badge +25

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
Rank
Badge +20

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
Rank
Userlevel 1
Badge +21

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

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


Community Stats

28,700
Posts
109,460
Replies
36,550
Members
Terms & ConditionsCookie 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.