Nested json to csv.

[wiluite]

Hello,
Is it possible to convert a nested json source to csv automatically (without knowing the data structure)? It seems neither csv::encode_csv nor csv::csv_stream_encoder is a solution.

Are there approximate solutions close to this task?

[danielaparker]

Could you give an example of a nested JSON source and how you would expect it to be mapped to CSV?

[wiluite]

Thank you for your reply!

Suppose, we have this source (testjson_nested.json):

[
    {
        "text": "Chicago Reader", 
        "float": 1.0, 
        "datetime": "1971-01-01T04:14:00", 
        "boolean": true,
        "nested": {
          "time": "04:14:00",
          "nested": {
            "date": "1971-01-01",
            "integer": 40
          }
        }
    }, 
    {
        "text": "Chicago Sun-Times", 
        "float": 1.27, 
        "datetime": "1948-01-01T14:57:13", 
        "boolean": true,
        "nested": {
          "time": "14:57:13",
          "nested": {
            "date": "1948-01-01",
            "integer": 63
          }
        }
    }
]

And here is how Python csvkit's utility in2csv does this conversion:

(base) C:\Users\Wiluite50>in2csv testjson_nested.json
text,float,datetime,boolean,nested/time,nested/nested/date,nested/nested/integer
Chicago Reader,1.0,1971-01-01T04:14:00,True,4:14:00,1971-01-01,40
Chicago Sun-Times,1.27,1948-01-01T14:57:13,True,14:57:13,1948-01-01,63

I've even seen a C++ code that did it as well (except for saving field ordering, what is very important to be).
But, the jsoncons seems to be perfectly designed and quick, and it would be nice to have the same thing within it.

Thanks.

[danielaparker]

Thanks for the suggestion. We don't currently support that, but we'll take it as a feature request for a future version.

[danielaparker]

I've added support for nested json to csv on master. This includes two additional csv options,

• A boolean, flat, which indicates whether the encoder should process JSON as "flat" (without navigating through nested structures). The default is true, for backwards compatibility.
• A vector of JSONPointer/column name pairs, column_mapping, for controlling the selection and order of columns and column naming (when flat is false, column names default to the JSONPointers identifying their location within each item.)

Your example becomes:

#include <jsoncons/json.hpp>
#include <jsoncons_ext/csv/csv.hpp>

namespace csv = jsoncons::csv;

int main()
{
    std::string jtext = R"(
[
    {
        "text": "Chicago Reader", 
        "float": 1.0, 
        "datetime": "1971-01-01T04:14:00", 
        "boolean": true,
        "nested": {
          "time": "04:14:00",
          "nested": {
            "date": "1971-01-01",
            "integer": 40
          }
        }
    }, 
    {
        "text": "Chicago Sun-Times", 
        "float": 1.27, 
        "datetime": "1948-01-01T14:57:13", 
        "boolean": true,
        "nested": {
          "time": "14:57:13",
          "nested": {
            "date": "1948-01-01",
            "integer": 63
          }
        }
    }
]
        )";

    auto j = jsoncons::json::parse(jtext);

    auto options = csv::csv_options{}
        .flat(false);

    std::string buf;
    csv::encode_csv(j, buf, options);
    std::cout << buf << "\n";
}

Output:

/boolean,/datetime,/float,/nested/nested/date,/nested/nested/integer,/nested/time,/text
true,1971-01-01T04:14:00,1.0,1971-01-01,40,04:14:00,Chicago Reader
true,1948-01-01T14:57:13,1.27,1948-01-01,63,14:57:13,Chicago Sun-Times

This is how to customize the column mapping,

    auto j = jsoncons::json::parse(jtext);

    auto options = csv::csv_options{}
        .flat(false)
        .column_mapping({
            {"/datetime","Timestamp"},
            {"/text", "Newspaper"},
            {"/nested/nested/integer","Count"}
        });

    std::string buf;
    csv::encode_csv(j, buf, options);
    std::cout << buf << "\n";

Output:

Timestamp,Newspaper,Count
1971-01-01T04:14:00,Chicago Reader,40
1948-01-01T14:57:13,Chicago Sun-Times,63

Any feedback appreciated.

[wiluite]

Thank you very much! I'll try to return to this topic next week. Thanks.

[wiluite]

Yes, having ojson instead of json and this new "flat" with value "false" does the job, which almost completely simulates Python's default behaviour. Awesome!