当我的JSON列为动态时，将JSON文件转换为CSV文件 [英] JSON file to CSV file conversion when my JSON columns are dynamic

问题描述

我找到了将json转换为csv的解决方案.下面是示例json和解决方案.

I found the solution for json to csv conversion. Below is the sample json and solution.

{
  "took" : 111,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "alerts",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "alertID" : "639387c3-0fbe-4c2b-9387-c30fbe7c2bc6",
          "alertCategory" : "Server Alert",
          "description" : "Successfully started.",
          "logId" : null
          }
       },
       {
        "_index" : "alerts",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "alertID" : "2",
          "alertCategory" : "Server Alert",
          "description" : "Successfully stoped.",
          "logId" : null
          }
       }
   ]
  }
}

解决方案:

jq -r '.hits.hits[]._source | [ "alertID" , "alertCategory" , "description", "logId" ], ([."alertID",."alertCategory",."description",."logId" // "null"]) | @csv' < /root/events.json

此解决方案的问题是我必须对列名进行硬编码.如果我的json稍后在_source标签下添加了一些内容怎么办?我需要一个可以处理_source下的动态数据的解决方案.我对Shell中的其他任何工具或命令都持开放态度.

The problem with this solution is that I have to hard code the column names. What If my json gets a few additions under _source tag later? I need a solution which can handle the dynamic data under _source. I am open to any other tool or command in shell.

推荐答案

只需使用keys_unsorted(如果希望对它们进行排序，则使用keys).参见例如使用jq将JSON数组转换为CSV 或

Simply use keys_unsorted (or keys if you want them sorted). See e.g. Convert JSON array into CSV using jq or How to convert arbitrary simple JSON to CSV using jq? for two SO examples. There are many others too.

这篇关于当我的JSON列为动态时，将JSON文件转换为CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！