Question

Parsing JSON array from HTTPCaller

  • 13 December 2019
  • 7 replies
  • 134 views
J
Badge

Hello!

I'm trying to parse JSON data returned from the API for Oracle Service Cloud. I've figured out the HTTPcaller piece and the data is returned like this:

{
   "count" : 10000,
   "name" : "OpenData REST export",
   "columnNames" : [ "Address", "Channel", "Date Closed", "Date Created", "Date Last Updated", "Disposition ID", "Latitude", "Longitude", "Product Hierarchy", "Product ID", "Service Request ID", "Status Type" ],
   "rows" : [
      [ null, "Email", "'2018-06-20 12:46:36'", "'2018-06-19 17:36:19'", "'2019-08-20 18:22:39'", null, null, null, "Parking", "Valid monthly permit", "2296", "Solved" ],
      [ null, "Email", "'2018-09-06 10:13:19'", "'2018-06-19 18:28:00'", "'2019-08-20 18:22:38'", null, null, null, "Parking", "Disputing information on offence", "2299", "Solved" ],
      [ null, "Email", "'2018-06-28 14:45:50'", "'2018-06-19 19:07:40'", "'2019-08-20 18:22:38'", null, null, null, "Parks", "Residential new tree request / replacement tree", "2303", "Solved" ]

...etc

}

 

I need to extract the "rows" data into FME features with attributes so that they can be processed and eventually written out to CSV.

How can I accomplish this? I've been able to use a JSONfragmenter with the query json["rows"][*] to separate out the features but I'm having trouble creating the attributes. How do I expose the attributes array{0}, array{1}, array{2}, etc with the correct columnNames?

If possible, I'd like to do it dynamically because there is every possibility that the schema will change down the road and I'd rather not have to edit the workbench. Do I have have to hardcode the columnNames into my workbench?

Suggestions appreciated! 

7 replies

takashi
Rank
Userlevel 2
Badge +17

Hi @jess, a possible way I can think of is to transform the column names and each row into comma-separated text and then write them into a file with a Text File Writer.

Note: the JSONExtractor should run before the JSONFragmenter. You can control the order by the Junction right-click menu > Set Connection Runtime Order.

J
Badge

Hi @jess, a possible way I can think of is to transform the column names and each row into comma-separated text and then write them into a file with a Text File Writer.

Note: the JSONExtractor should run before the JSONFragmenter. You can control the order by the Junction right-click menu > Set Connection Runtime Order.

Thanks for the response @takashi ! This is pretty close to what I've got going so far and I can successfully write out to a CSV file.

However I'm wondering if it is possible to dynamically expose attributes from this JSON data in FME. By that I mean, the JSONextractor in my workbench exposes the columnNames array and the JSONfragmenter splits the HTTP response into individual features but the problem is that those features have attributes called array{0}, array{1}, array{2} ... which must then be manually renamed (AttributeManager).

I'd like to do processing on these features based on the values of different attributes within the workbench. With the current design, if the attribute order is changed then the workbench will need to be edited. Can you see a way that I can pull the attribute names directly so rather than array{0}, array{1}, array{2} ... the attributes are named for the associated values in the columnNames array?

My current workbench:

takashi
Rank
Userlevel 2
Badge +17

Hi @jess, a possible way I can think of is to transform the column names and each row into comma-separated text and then write them into a file with a Text File Writer.

0684Q00000ArKy2QAF.pngNote: the JSONExtractor should run before the JSONFragmenter. You can control the order by the Junction right-click menu > Set Connection Runtime Order.

0684Q00000ArKuxQAF.png

Surprisingly the JSONTemplater can be used to execute XQuery Expressions with JSONiq extension to create a new JSON document based on the source JSON document.

0684Q00000ArMIdQAN.png

 

The JSONTemplater with this XQuery expression generates the following JSON array, then the JSONFragmenter performs fragmentation and flattening to create features containing desired attributes.

 

JSONTemplater Template Expressionn: XQuery Expression with JSONiq Extension

[
    let $doc := fme:get-json-attribute("_response_body")
    let $names := $doc("columnNames")
    let $rows := $doc("rows")
    for $i in (1 to jn:size($rows))
    return
    {|
        for $j in (1 to jn:size($names))
        return {$names($j) : $rows($i)($j)}
    |}
]

 

Resulting JSON Array

[
   {
      "Address" : null,
      "Channel" : "Email",
      "Date Closed" : "'2018-06-20 12:46:36'",
      "Date Created" : "'2018-06-19 17:36:19'",
      "Date Last Updated" : "'2019-08-20 18:22:39'",
      "Disposition ID" : null,
      "Latitude" : null,
      "Longitude" : null,
      "Product Hierarchy" : "Parking",
      "Product ID" : "Valid monthly permit",
      "Service Request ID" : "2296",
      "Status Type" : "Solved"
   },
   {
      "Address" : null,
      "Channel" : "Email",
      "Date Closed" : "'2018-09-06 10:13:19'",
      "Date Created" : "'2018-06-19 18:28:00'",
      "Date Last Updated" : "'2019-08-20 18:22:38'",
      "Disposition ID" : null,
      "Latitude" : null,
      "Longitude" : null,
      "Product Hierarchy" : "Parking",
      "Product ID" : "Disputing information on offence",
      "Service Request ID" : "2299",
      "Status Type" : "Solved"
   },

   ...

]

takashi
Rank
Userlevel 2
Badge +17

Hi @jess, a possible way I can think of is to transform the column names and each row into comma-separated text and then write them into a file with a Text File Writer.

Note: the JSONExtractor should run before the JSONFragmenter. You can control the order by the Junction right-click menu > Set Connection Runtime Order.

Additional comment. Currently there is no way to automatically expose attribute names on the Workbench Canvas, which are defined at run-time dynamically. You need to expose them manually if necessary.

However, if the destination attribute names are exactly equal to the "columnNames" and you configure the destination schema statically in the writer feature type as shown in your screenshot (below), you don't need to expose the attribute names in the workflow above.

J
Badge

Additional comment. Currently there is no way to automatically expose attribute names on the Workbench Canvas, which are defined at run-time dynamically. You need to expose them manually if necessary.

However, if the destination attribute names are exactly equal to the "columnNames" and you configure the destination schema statically in the writer feature type as shown in your screenshot (below), you don't need to expose the attribute names in the workflow above.

Very useful. Thanks again @takashi !

J
Badge

Surprisingly the JSONTemplater can be used to execute XQuery Expressions with JSONiq extension to create a new JSON document based on the source JSON document.

0684Q00000ArMIdQAN.png

 

The JSONTemplater with this XQuery expression generates the following JSON array, then the JSONFragmenter performs fragmentation and flattening to create features containing desired attributes.

 

JSONTemplater Template Expressionn: XQuery Expression with JSONiq Extension

[
    let $doc := fme:get-json-attribute("_response_body")
    let $names := $doc("columnNames")
    let $rows := $doc("rows")
    for $i in (1 to jn:size($rows))
    return
    {|
        for $j in (1 to jn:size($names))
        return {$names($j) : $rows($i)($j)}
    |}
]

 

Resulting JSON Array

[
   {
      "Address" : null,
      "Channel" : "Email",
      "Date Closed" : "'2018-06-20 12:46:36'",
      "Date Created" : "'2018-06-19 17:36:19'",
      "Date Last Updated" : "'2019-08-20 18:22:39'",
      "Disposition ID" : null,
      "Latitude" : null,
      "Longitude" : null,
      "Product Hierarchy" : "Parking",
      "Product ID" : "Valid monthly permit",
      "Service Request ID" : "2296",
      "Status Type" : "Solved"
   },
   {
      "Address" : null,
      "Channel" : "Email",
      "Date Closed" : "'2018-09-06 10:13:19'",
      "Date Created" : "'2018-06-19 18:28:00'",
      "Date Last Updated" : "'2019-08-20 18:22:38'",
      "Disposition ID" : null,
      "Latitude" : null,
      "Longitude" : null,
      "Product Hierarchy" : "Parking",
      "Product ID" : "Disputing information on offence",
      "Service Request ID" : "2299",
      "Status Type" : "Solved"
   },

   ...

]

THIS is the answer I was looking for. Thank you so much.

bilal
Rank
Badge +4

Surprisingly the JSONTemplater can be used to execute XQuery Expressions with JSONiq extension to create a new JSON document based on the source JSON document.

0684Q00000ArMIdQAN.png

 

The JSONTemplater with this XQuery expression generates the following JSON array, then the JSONFragmenter performs fragmentation and flattening to create features containing desired attributes.

 

JSONTemplater Template Expressionn: XQuery Expression with JSONiq Extension

[
    let $doc := fme:get-json-attribute("_response_body")
    let $names := $doc("columnNames")
    let $rows := $doc("rows")
    for $i in (1 to jn:size($rows))
    return
    {|
        for $j in (1 to jn:size($names))
        return {$names($j) : $rows($i)($j)}
    |}
]

 

Resulting JSON Array

[
   {
      "Address" : null,
      "Channel" : "Email",
      "Date Closed" : "'2018-06-20 12:46:36'",
      "Date Created" : "'2018-06-19 17:36:19'",
      "Date Last Updated" : "'2019-08-20 18:22:39'",
      "Disposition ID" : null,
      "Latitude" : null,
      "Longitude" : null,
      "Product Hierarchy" : "Parking",
      "Product ID" : "Valid monthly permit",
      "Service Request ID" : "2296",
      "Status Type" : "Solved"
   },
   {
      "Address" : null,
      "Channel" : "Email",
      "Date Closed" : "'2018-09-06 10:13:19'",
      "Date Created" : "'2018-06-19 18:28:00'",
      "Date Last Updated" : "'2019-08-20 18:22:38'",
      "Disposition ID" : null,
      "Latitude" : null,
      "Longitude" : null,
      "Product Hierarchy" : "Parking",
      "Product ID" : "Disputing information on offence",
      "Service Request ID" : "2299",
      "Status Type" : "Solved"
   },

   ...

]

This is perfect. I had the exact same problem and wanted to avoid using an external CSV file. Thank you so much. I tried it with my data and it worked like a dream.

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
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.