Skip to main content
Solved

JSON from Oracle Query

  • November 14, 2018
  • 2 replies
  • 53 views
B
Forum|alt.badge.img

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

 

 

}

Best answer by takashi

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.

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

2 replies

takashi
Influencer
  • takashiInfluencer
  • Best Answer
  • November 14, 2018

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
Forum|alt.badge.img
  • bcrowley10
  • Author
  • November 15, 2018

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

Reply


Helpful Members This Week

Community Stats

31,300
Posts
118,696
Replies
38,880
Members

Latest FME

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.