Configuring the components

Double-click tFileInputJSON to display its Basic settings view.

Click Edit schema to open the schema editor.

Click the [+] button to add one column, namely friends, of the String type.

Click OK to close the editor.

Click the [...] button to browse for the JSON file, facebook.json in this case:

{ "user": { "id": "9999912398",
            "name": "Kelly Clarkson",
            "friends": [
                 { "name": "Tom Cruise",
                   "id": "55555555555555",
                   "likes": {
                       "data": [
                            { "category": "Movie",
                              "name": "The Shawshank Redemption",
                              "id": "103636093053996",
                              "created_time": "2012-11-20T15:52:07+0000"
                            },
                            { "category": "Community",
                              "name": "Positiveretribution",
                              "id": "471389562899413",
                              "created_time": "2012-12-16T21:13:26+0000"
                            }
                                ]
                            }
                 },
                 { "name": "Tom Hanks",
                   "id": "88888888888888"
                   "likes": {
                        "data": [
                            { "category": "Journalist",
                              "name": "Janelle Wang",
                              "id": "136009823148851",
                              "created_time": "2013-01-01T08:22:17+0000"
                            },
                            { "category": "Tv show",
                              "name": "Now With Alex Wagner",
                              "id": "305948749433410",
                              "created_time": "2012-11-20T06:14:10+0000"
                            }
                            ]
                           }
                  }
                        ]
          }
}

Clear the Read by XPath check box.

In the Mapping table, enter the JSONPath query "$.user.friends[*]" next to the friends column, retrieving the entire friends node from the source file.

Double-click tExtractJSONFields to display its Basic settings view.

Click Edit schema to open the schema editor.

Click the [+] button in the right panel to add five columns, namely id, name, like_id, like_name and like_category, which will hold the data of relevant nodes in the JSON field friends.

Click OK to close the editor.

In the pop-up Propagate box, click Yes to propagate the schema to the subsequent components.

In the Loop XPath query field, enter "/likes/data".

In the Mapping area, type in the queries of the JSON nodes in the XPath query column. The data of those nodes will be extracted and passed to their counterpart columns defined in the output schema.

Specifically, define the XPath query "../../id" (querying the "/friends/id" node) for the column id, "../../name" (querying the "/friends/name" node) for the column name, "id" for the column like_id, "name" for the column like_name, and "category" for the column like_category.

Double-click tLogRow to display its Basic settings view.

Select Table (print values in cells of a table) for a better display of the results.

Procedure

Did this page help you?