How to convert table to JSON and keep date as string not an object

[lusnyaktah]

I am trying to convert table data to JSON following way

StringWriter writer = new StringWriter();           
tableData.write().usingOptions(JsonWriteOptions.builder(writer).asObjects(true).header(false).build());

It returns the following result:

[
   {
      "country":"Belgium",
      "province":"",
      "city":"",
      "lastUpdate":{
         "nano":0,
         "year":2020,
         "monthValue":3,
         "dayOfMonth":29,
         "hour":23,
         "minute":8,
         "second":13,
         "month":"MARCH",
         "dayOfWeek":"SUNDAY",
         "dayOfYear":89,
         "chronology":{
            "id":"ISO",
            "calendarType":"iso8601"    
}
}   
}
]

I want to get the DateTime column as follows:

[
   {
      "country":"Belgium",
      "province":"",
      "city":"",
      "lastUpdate": "2020-03-29 23:08:13"
}
]

Which configuration I should add to get normal result or Is there any way when converting JSON data into table specify to convert datetime/date types to string?

[benmccann]

Ah, yeah, we should probably convert dates in some other way. Unfortunately, there's no standard way to serialize dates in JSON, so it's arguable what we should do. My preference would probably be to convert it to a number containing milliseconds since the epoch.

I just added the Jackson Java 8 support module. It's too bad that's not included by default. Anyway, the default with Java 8 support in Jackson is to convert it to [2020, 03, 29, 23, 08, 13]. Will that work for you?

[lusnyaktah]

Unfortunately, no it also will not work for me. In my case, first I convert JSON to the table following way: table = Table.read().usingOptions(JsonReadOptions.builderFromString(jsonData)); If you could add some configuration that during the reading I can set datetime/date column type to string it will be great

[benmccann]

The initial post was about writing and the last comment was about reading. Do you need string support for both reading and writing? Is any string format okay or do you need a specific one? Can you clarify why it must be a string?

[lusnyaktah]

Yes, I need string support for both reading and writing. the initial JSON that I convert to the table looks like as follows where you can see that 'lastUpdate' is a string, so I want to keep that format

[
   {
      "country":"Belgium",
      "province":"",
      "city":"",
      "lastUpdate": "2020-03-29 23:08:13"
}
]

I convert JSON to table applying to summarize functionality then convert the result table back to JSON.

[lusnyaktah]

One more thing that I noticed 'columnTypesToDetect' doesn't work, because when it gathers column names(JsonReader class) it keeps in Set type object and Set doesn't keep the order, it makes senes to keep in LinkedHashSet table = Table.read().usingOptions(JsonReadOptions.builderFromString(jsonData).columnTypesToDetect(columnTypesToDetect));

[lusnyaktah]

I found a workaround, I am copying the column as string column then replacing the DateTime column with new string column