CODEWITHSUNDEEP
jsonjq
Bond
Bond

Reputation: 945

Parse JSON output with JQ

Lets say I have an I/P json file as below. And I want to extract the O/P in a CSV format with the below fields. Specifically, I want to get the value of the key "Gamma" in the o/p if the key "Gamma" exists in "tags" map. If the key doesn't exists, it should just print a NULL value. The expected o/p is below.

generated_time,platform,id,,
2021-09-09:12:03:12,earth,2eeee67748,Ray,2021-08-25 09:41:06
2021-09-09:12:03:12,sun,xxxxx12334,NULL,2021-08-25 10:11:31


[
  { 
    "generated_time": "generated_time",
    "platform": "platform",
    "id": "id"
  },
    {
    "generated_time": "2021-09-09:12:03:12",
    "platform": "earth",
    "id": "2eeee67748",
    "tags": {
      "app": "map",
      "Gamma": "Ray",
      "null": [
        "allow-all-humans"
      ]
          },
    "created": "2021-08-25 09:41:06"
  },
   { 
    "generated_time": "2021-09-09:12:03:12",
    "platform": "sun",
    "id": "xxxxx12334",
    "tags": {
      "component": "machine",
      "environment": "hot",
      "null": [
        "aallow-all-humans"
      ]
    },
    "created": "2021-08-25 10:11:31"
   }
]

Upvotes: 0

Views: 295

Answers (1)

pmf
pmf

Reputation: 36033

jq has a builtin @csv which renders an array

as CSV with double quotes for strings, and quotes escaped by repetition.

If the additional quoting (as compared to your expected output) isn't an issue, the following

jq --raw-output '
  
  # produce an array for each element in the input array
  .[] | [
    
    # containing the first three columns unchanged
    .generated_time, .platform, .id,
    
    # if the input element has a field named "tags"
    if has("tags")
    
    # then add two more columns and replace an inexistant Gamma with "NULL"
    then (.tags.Gamma // "NULL", .created)
    
    # otherwise add two empty columns instead
    else (null, null) end
    
  # and convert the array into CSV format
  ] | @csv
  
' input.json

will produce

"generated_time","platform","id",,
"2021-09-09:12:03:12","earth","2eeee67748","Ray","2021-08-25 09:41:06"
"2021-09-09:12:03:12","sun","xxxxx12334","NULL","2021-08-25 10:11:31"

Upvotes: 1

Related Questions