Skip to main content
  • EN

Hi,

I am new with JSON. I am actually fetching dats using HTTPCaller and saving the output in csv format. I have used Creator-HTTPCaller-JSONValidator-JSONFormatter-JSONFlattener-Writter(csv). Everything seems correct till formatter, but when I use flattener, the elements are not getting exploded in the csv output or even inspector.

Hi,

Firstly check the result from the JSONFlattener with the Inspector (Feature Information window of Data Inspector).

In addition, I don't think you need to use the JSONFormatter before flattening a valid JSON object.


Takashi

The response body is a JSON array. You have to use the JSONFragmenter to transform elements of the array into individual JSON objects, before flattening with the JSONFlattener. The JSONFormatter is not essential to do that.

See here to learn more about the structure of JSON objects.

The response body is a JSON array. You have to use the JSONFragmenter to transform elements of the array into individual JSON objects, before flattening with the JSONFlattener. The JSONFormatter is not essential to do that.

See here to learn more about the structure of JSON objects.

In addition, if you set "Yes" to the "Load Keys as Attributes" and "Recursively Flatten Objects/Arrays" parameters of the JSONFragmenter, you can remove the JSONFlattener.

Thanks Takashi for the advice. It was really helpful.

On a way ahead of that, if you see the file, there are two "name", which we need to extract. The first is having a number but the other is have real name (not the number). Can we extract "name" which does not have the number. It is giving a problem since its not an attribute, but a JSON file. Even when I use ListExploder with name{}, the output is null and gives blank output.

Thanks

A simple way is to filter the features by the "name" attribute value after flattening the JSON objects. For example:

Tester: name Matches Regex ^\\d+$

This regular expression matches a string consisting of numbers only.

Exactly, but the the "name", unlike others is not getting filtered by itself, i can't understand why !!!

I'm not sure why the features cannot be filtered by the "name" attribute value. Could you please describe the situation in more detail?

nameextract.txt

I have attached the file in text format. What I want is to extract "name" which is, in this case "Edmonton Green Bus Station - Brent Cross Shopping Centre".

The text you posted contains just one JSON object, which contains a string member called "name" and two array members called "routeSections" and "serviceTypes". Every element of those arrays also contains a member called "name".  Which objects do you want to extract?

This is the JSON object you posted.

{
   "$type" : "Tfl.Api.Presentation.Entities.Line, Tfl.Api.Presentation.Entities",
   "id" : "102",
   "name" : "102",
   "modeName" : "bus",
   "created" : "2015-12-07T14:16:21.757",
   "modified" : "2015-12-07T14:16:21.757",
   "lineStatuses" : <],
   "routeSections" : 1
      {
         "$type" : "Tfl.Api.Presentation.Entities.MatchedRoute, Tfl.Api.Presentation.Entities",
         "name" : "Brent Cross Shopping Centre - Edmonton Green Bus Station",
         "direction" : "inbound",
         "originationName" : "Brent Cross Shopping Centre",
         "destinationName" : "Edmonton Green Bus Station",
         "originator" : "490004282D",
         "destination" : "490009983A",
         "serviceType" : "Regular"
      },
      {
         "$type" : "Tfl.Api.Presentation.Entities.MatchedRoute, Tfl.Api.Presentation.Entities",
         "name" : "Edmonton Green Bus Station - Brent Cross Shopping Centre",
         "direction" : "outbound",
         "originationName" : "Edmonton Green Bus Station",
         "destinationName" : "Brent Cross Shopping Centre",
         "originator" : "490009983G",
         "destination" : "490004282A",
         "serviceType" : "Regular"
      },
      {
         "$type" : "Tfl.Api.Presentation.Entities.MatchedRoute, Tfl.Api.Presentation.Entities",
         "name" : "Brent Cross Shopping Centre - Edmonton Green Bus Station",
         "direction" : "inbound",
         "originationName" : "Brent Cross Shopping Centre",
         "destinationName" : "Edmonton Green Bus Station",
         "originator" : "490004282D",
         "destination" : "490009983A",
         "serviceType" : "Regular"
      },
      {
         "$type" : "Tfl.Api.Presentation.Entities.MatchedRoute, Tfl.Api.Presentation.Entities",
         "name" : "Edmonton Green Bus Station - Brent Cross Shopping Centre",
         "direction" : "outbound",
         "originationName" : "Edmonton Green Bus Station",
         "destinationName" : "Brent Cross Shopping Centre",
         "originator" : "490009983G",
         "destination" : "490004282A",
         "serviceType" : "Regular"
      }
   ],
   "serviceTypes" : P
      {
         "$type" : "Tfl.Api.Presentation.Entities.LineServiceTypeInfo, Tfl.Api.Presentation.Entities",
         "name" : "Regular",
         "uri" : "/Line/Route?ids=102&serviceTypes;=Regular"
      }
   ]
}

Hi @takashi Thanks for the advice and patience of bearing me.

The text file that I have attached was just one of the information extracted (one row in 30k). Here, I want to extract the "name", in this case- "Brent Cross Shopping Centre - Edmonton Green Bus Station". Generally, both the array sections in "routesections" will have the same "name". I want to extract the first one. How can we do that?

OK, try the JSONFragmenter with these parameter settings.

  • JSON Query: json["routeSections"][0]
  • Load Keys as Attributes: Yes
  • Attributes to Expose: name (and other key names which you want to expose)

The query extracts the first element (index = 0) of the "routeSections" array.

If the original JSON data is an array that contains multiple objects (i.e. [{...}, {...}, ...]) , replace the JSON Query with this query.

json

  • ["routeSections"][0]

    • Thanks. That was amazing 🙂 That was exactly I was supposed to do.

    Thanks. That was amazing 🙂 That was exactly I was supposed to do.

    My previous post has not been displayed with my browser. Can you see that?

    Thanks. That was amazing 🙂 That was exactly I was supposed to do.

    Just a reminder, repost the previous answer. Detail may be different ;)

    -----

    Try the JSONFragmenter with these parameter settings.

    • JSON Query: json["routeSections"][0]
    • Load Keys as Attributes: Yes
    • Attributes to Expose: name

    The query extracts the first element (index = 0) of the "routeSections" array.

    If the original JSON data is an array that contains multiple objects, replace the JSON query with this one:

    json

  • ["routeSections"][0]

    • My previous post has not been displayed with my browser. Can you see that?

    Yes... Its visible at the top of the first page. Even I am having a bit of confusion with that.

    Regarding your query on JSON- json

  • ["routeSections"][0], now the "id" is not being extracted. Only the elements which were in the array ["routeSelections"] are extracted. The "id" is not the part of that array, but needs to be extracted. I tried using json
  • ["routeSections"][0]["id"], "id', id. I also tried for using 2 JSONFragmenter, one for "id" and other for name and merging them. Either ways id didn't work. Can we have a better solution than that?

    • Regarding your query on JSON- json

  • ["routeSections"][0], now the "id" is not being extracted. Only the elements which were in the array ["routeSelections"] are extracted. The "id" is not the part of that array, but needs to be extracted. I tried using json
  • ["routeSections"][0]["id"], "id', id. I also tried for using 2 JSONFragmenter, one for "id" and other for name and merging them. Either ways id didn't work. Can we have a better solution than that?

    • A possible way is to use a combination of the JSONFragmenter and ListIndexer

    JSONFragmenter

    • JSON Query: json
    • Load Keys as Attributes: Yes
    • Recursively Flatten Objects/Arrays: Yes
    • Attributes to Expose: id, routeSections{}.name

    ListIndexer

    • List Attribute: routeSections{}
    • List Index: 0

    Note: If the "routeSections" array was empty, the resulting "name" would be the value of "name" of the root object because the root object also has a member called "name". If there could be such a case, check the number of "routeSections{}" elements before the ListIndexer.

    # I didn't think that my previous answer has been moved to the top of the first page.

    A possible way is to use a combination of the JSONFragmenter and ListIndexer

    JSONFragmenter

    • JSON Query: json
    • Load Keys as Attributes: Yes
    • Recursively Flatten Objects/Arrays: Yes
    • Attributes to Expose: id, routeSections{}.name

    ListIndexer

    • List Attribute: routeSections{}
    • List Index: 0

    Note: If the "routeSections" array was empty, the resulting "name" would be the value of "name" of the root object because the root object also has a member called "name". If there could be such a case, check the number of "routeSections{}" elements before the ListIndexer.

    # I didn't think that my previous answer has been moved to the top of the first page.

    Another way: JSONFragmenter x 2

    JSONFragmenter (1)

    • JSON Query: json
    • Load Keys as Attributes: Yes
    • Recursively Flatten Objects/Arrays: No
    • Attributes to Expose: id, routeSections

    JSONFragmenter (2)

    • JSON Attribute: routeSections
    • JSON Query: json[0]
    • Load Keys as Attributes: Yes
    • Attributes to Expose: name

    With this way, the JSONFragmenter (2) will not extract empty "routeSections".

    Reply


    Community Stats

    30,109
    Posts
    114,302
    Replies
    37,745
    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.