JSON to Table Node duplicates columns

KNIME Analytics Platform
1

Hi,

I have a JSON string which I'm inputting to a table using the JSON to Table node. The JSON string consists of "Itineraries" within which exist sepearate "Itinerary" elements.

However, when this node reads the JSON string it sees all element names as unique and duplicates the column names so all of the JSON data is in one row when it should be in two rows. This means what I expect to be around 60 columns is 120.

My JSON appears to pass through http://jsonlint.com/ as valid so I'm a little unsure as to what else could be the problem. 

Thanks for any assistance in advance,

 

Bernard

2

Could you show a (small) example and how you tried to use the Table to JSON node? (It also helps if you can provide example how it should look like after the transformation.)

Does your input JSON looks similarly to this?

{"Itineraries": [{"Itinerary": 2}, {"Itinerary": 4}]

Thanks, gabor

3

Hi Bernard,

if you take a look at the node dialog of the JSON to Table node, it is more a Json to row node.

It takes as much as possible from the input data and transforms it into one data row.

 

4

Ah I see...interesting to know. This may mean it's not fit for my requirements.

The below is a more accurate representation of the data I'm attempting to pass through this node. After the first child element of Itinerary is referenced, subsequent elements don't seem to explicitly reference it.

{"Itineraries": [{"Itinerary": 2}, {4}]

Any ideas/input appreciated :)

 

5

This looks like a very strange format to me...

6

I'll post a snippet here for clarification...

{
  "ErrorOccured" : false,
  "ResponseMessage" : null,
  "ResponseCode" : 1,
  "Date" : "2016-02-04T11:53:00.3427549+00:00",
  "PNRData" : {
    "Itineraries" : {
      "@xmlns:xsi" : "http://www.w3.org/2001/XMLSchema-instance",
      "Itinerary" : [ {
        "RecordLocator" : "[data]"
        "GDSName" : "[data]",
        "DownloadedDate" : "2015-09-16T17:24:37.043",
        "FirstDepartureDateTime" : {
          "@xsi:nil" : "true"
        },
        "LastArrivalDateTime" : {
          "@xsi:nil" : "true"
        },
        "DI" : "D",
        "BookingType" : "Classic",
        "TicketStatus" : "Ticketed",
        "Travellers" : {
          "Traveller" : {
            "@xmlns:xsi" : "http://www.w3.org/2001/XMLSchema-instance",
            "FirstName" : "[data]",
            "LastName" : "[data]",
            "JobTitle" : {
              "@xsi:nil" : "true"
            },
            "Department" : {
              "@xsi:nil" : "true"
            }
          }
        },
        },
        "AirSegments" : {
          "AirSegment" : {
            "@xmlns:xsi" : "http://www.w3.org/2001/XMLSchema-instance",
            "IATAAirlineCode" : "IB",
            "FlightNumber" : null,
            "DepartureIATAAirportCode" : "BCN",
            "ArrivalIATAAirportCode" : "MAD",
            "ArrivalDateTime" : {
              "@xsi:nil" : "true"
            },
            "Class" : "ECONOMY",
            "ReasonCode" : {
              "@xsi:nil" : "true"
            },
            "ReasonDescription" : {
              "@xsi:nil" : "true"
            }
          }
        },
        "CarSegments" : {
          "@xsi:nil" : "true"
        },
        "RailSegments" : {
          "@xsi:nil" : "true"
        },
        "HotelSegments" : {
          "@xsi:nil" : "true"
        }
      }, { <-- This represents the start of a new 'Itinerary'
        "RecordLocator" : "[data]",
        "GDSName" : "[data]",
        "DownloadedDate" : "2015-09-16T17:27:59.563",
        "FirstDepartureDateTime" : {
          "@xsi:nil" : "true"
        },
        "LastArrivalDateTime" : {
          "@xsi:nil" : "true"
        },
        "DI" : "D",
        "BookingType" : "Classic",
        "TicketStatus" : "Ticketed",
        "Travellers" : {
          "Traveller" : {
            "@xmlns:xsi" : "http://www.w3.org/2001/XMLSchema-instance",
            "FirstName" : "[data]",
            "LastName" : "[data]",
            "JobTitle" : {
              "@xsi:nil" : "true"
            },
            "Department" : {
              "@xsi:nil" : "true"
            }
          }
        },
        "AirSegments" : {
          "AirSegment" : {
            "@xmlns:xsi" : "http://www.w3.org/2001/XMLSchema-instance",
            "IATAAirlineCode" : "IB",
            "FlightNumber" : null,
            "DepartureIATAAirportCode" : "BCN",
            "ArrivalIATAAirportCode" : "MAD",
            "ArrivalDateTime" : {
              "@xsi:nil" : "true"
            },
            "Class" : "ECONOMY",
            "ReasonCode" : {
              "@xsi:nil" : "true"
            },
            "ReasonDescription" : {
              "@xsi:nil" : "true"
            }
          }
        },
        "CarSegments" : {
          "@xsi:nil" : "true"
        },
        "RailSegments" : {
          "@xsi:nil" : "true"
        },
        "HotelSegments" : {
          "@xsi:nil" : "true"
        }
      } ]
    }
  }
}

Needless to say it does look like Knime is just creating a column for every element.