• EN
Solved

JSON from Oracle Query

  • 14 November 2018
  • 2 replies
  • 14 views
B
Badge

Hello: I have an oracle query that I need to generate as a json file. The query contains all of the feature necessary for the output however, there can be multiple addresses for a given project name. For projects that have multiple addresses, I need to repeat the following attributes: street, cagisID, addressID

 

I had this structure in a JSONTemplater but when a project contains multiple rows (with the differing address), it generates all of the elements below multiple times. I tried to configure the "SUB" feature of the transformer but I must need to only send a project query to the ROOT and all of the possible address variations for a project to the SUB. But then I could figure out how to link the two for this to work.

 

{

 

"totalInvestment": fme:get-attribute("totalInvestment"),

 

"squareFootageRetail": fme:get-attribute("squareFootageRetail"),

 

"squareFootageOffice": fme:get-attribute("squareFootageOffice"),

 

"squareFootageManufacturing": fme:get-attribute("squareFootageManufacturing"),

 

"recordType": fme:get-attribute("recordType"),

 

"projectStage": fme:get-attribute("projectStage"),

 

"projectName": fme:get-attribute("projectName"),

 

"projectId": fme:get-attribute("projectId"),

 

"projectDescription": fme:get-attribute("projectDescription"),

 

"projectDate": fme:get-attribute("projectDate"),

 

"propertyAddresses":[{

 

"street": fme:get-attribute("street"),

 

"cagisId": fme:get-attribute("cagisId"),

 

"addressId": fme:get-attribute("addressId")

 

}],

 

"programType": fme:get-attribute("programType"),

 

"numberOfUnits": fme:get-attribute("numberOfUnits"),

 

"numberOfSqFt": fme:get-attribute("numberOfSqFt"),

 

"numberOfParkingSpaces": fme:get-attribute("numberOfParkingSpaces"),

 

"numberOfHotelRooms": fme:get-attribute("numberOfHotelRooms"),

 

"neighborhood": fme:get-attribute("neighborhood"),

 

"jobsRetained": fme:get-attribute("jobsRetained"),

 

"jobsCreated": fme:get-attribute("jobsCreated"),

 

"developer": fme:get-attribute("developer"),

 

"category": fme:get-attribute("category"),

 

"cagisId": fme:get-attribute("cagisId"),

 

"address": fme:get-attribute("address")

 

 

}
icon

Best answer by takashi 14 November 2018, 22:59

View original

2 replies

takashi
Rank
Userlevel 3
Badge +17

If all the attributes except address have the same values in all the rows like this simplified example,

projectIdprojectNamestreetcagisid100ProjectAfoo1100ProjectAbar2100ProjectAfoobar3

a possible way is to select and send a single row to the ROOT port and send all rows to the SUB port of the JSONTempleter, then aggregate the JSON objects generated with the SUB expression as an JSON array in the ROOT expression.

ROOT Expression:

{
    "projectId" : fme:get-attribute("projectId"),
    "projectName" : fme:get-attribute("projectName"),
    "propertyAddresses" : [
        fme:process-features("SUB")
    ]
}

SUB Expression:

{
    "street" : fme:get-attribute("street"),
    "cagisid" : fme:get-attribute("cagisid")
}

0684Q00000ArKUsQAN.png

Result:

{
    "projectId" : "100",
    "projectName" : "ProjectA",
    "propertyAddresses" : [
        {
            "street" : "foo",
            "cagisid" : "1"
        },
        {
            "street" : "bar",
            "cagisid" : "2"
        },
        {
            "street" : "foobar",
            "cagisid" : "3"
        }
    ]
}

If there were two or more projects in the source dataset, set project ID attribute to both the Group By parameter in the Sampler and the Group Sub-Features By parameter in the JSONTemplater.

Hope this helps.

B
Badge

Thank you @takashi - That worked perfectly. The Sampler transformer is not something I would have thought of.

Reply


Community Stats

28,845
Posts
109,910
Replies
36,650
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.