Explode_json_columns Output Filter Function

The explode_json_columns option for Treasure Data's integrations explodes columns having JSON into multiple columns.

Add the explode_json_columns option and JSON column name to the out: section, as shown in the following example:

in:
...
out:
  explode_json_columns: ["json_column"]
...

The original JSON column name remains in the result even if the column is configured to explode.

If your JSON data has a name that duplicates another column, you must use the rename filter. Refer to Use Case 4 in this article.

When you have csv data that contains a JSON column, add the items to your data connector .yml file.

For example, if your source data is as follows:

id  jsons
1   {"time2":1455829282,"ip":"93.184.216.34","name":"frsyuki"}
2   {"time2":1455829282,"ip":"172.36.8.109","name":"sadayuki"}
3   {"time2":1455829284,"ip":"example.com","name":"Treasure Data"}
4   {"time2":1455829282,"ip":"10.98.43.1","name":"MessagePack"}

You add the following to the .yml file. In this case, the data connector will explode the jsons column.

out:
  explode_json_columns: ["jsons"]
  mode: append

The result is as follows:

When you have csv data that includes multiple JSON columns, set explode column name.

For example, if your source data is as follows:

id  jsons   jsons2
1   {"time2":1455829282,"ip":"93.184.216.34","name":"frsyuki"}  {"country":"Japan","language":"Japanese"}
2   {"time2":1455829282,"ip":"172.36.8.109","name":"sadayuki"}  {"country":"USA","language":"English"}
3   {"time2":1455829284,"ip":"example.com","name":"Treasure Data"}  {"country":"Brazil","language":"Portuguese"}
4   {"time2":1455829282,"ip":"10.98.43.1","name":"MessagePack"} {"country":"Spain","language":"Spanish"}

You add the following to the .yml file. In this case, Data Connector will explode the jsons column.

out:
  explode_json_columns: ["jsons","jsons2"]
  mode: append

The result is as follows:

If your source data is the data type JSON, set explode the column name as record.

For example, if your source data is as follows:

{"time2":1455829282,"ip":"93.184.216.34","name":"frsyuki"}
{"time2":1455829282,"ip":"172.36.8.109","name":"sadayuki"}
{"time2":1455829284,"ip":"example.com","name":"Treasure Data"}
{"time2":1455829282,"ip":"10.98.43.1","name":"MessagePack"}

You add the following to the .yml file. In this case, Data Connector will explode JSON data.

out:
  explode_json_columns: ["record"]
  mode: append

The result is as follows:

If JSON data has a name that duplicates another column, use the rename filter option. More details at add_time filter plugin. In case of sample, jsons has name key which is same as name column in csv.

For example, if your source data is as follows:

name    jsons
A   {"time2":1455829282,"ip":"93.184.216.34","name":"frsyuki"}
B   {"time2":1455829282,"ip":"172.36.8.109","name":"sadayuki"}
C   {"time2":1455829284,"ip":"example.com","name":"Treasure Data"}
D   {"time2":1455829282,"ip":"10.98.43.1","name":"MessagePack"}

You add the rename option to filter section in your .yml file. In this case, Data Connector can explode jsons data.

in:
...
filters:
  - type: rename
    columns:
      name: code
out:
  explode_json_columns: ["jsons"]
  mode: append

The result is as follows:

If you don't use 'rename' to handle the duplicated name column, the explode_json_columns option does not explode the name element and the results are as follows: