0

Given an array of JSON objects, I'd like to output a CSV where one of the rows contains each object key and the others are based on each object value.

The input json is:

{
  "PCID000": {
    "OSmodle": "LINUX",
    "IEversion": "2.15.0",
    "hardwareUSB": [
      "Card reader",
      "keyboard"
    ],
    "OrderStatus": "01"
  },
  "PCID999": {
    "OSmodle": "LINUX",
    "OSversion": "4.0",
    "hardwareUSB": [],
    "OrderStatus": "01"
  }
}

The output would look something like this. The header can be hardcoded.

PCID,OSmodle,OSversion,IEversion,hardwareUSB, OrderStatus
"PCID000","LINUX",,"2.15.0","Card reader&keyboard","01"
"PCID999","LINUX","4.0",,"01
ikegami
  • 367,544
  • 15
  • 269
  • 518
Tai Ka Ki
  • 15
  • 2

1 Answers1

0

You can use the to_entries function to convert an object such as {"a": 1, "b": 2} to an array of key-value objects such as [{"key": "a", "value": 1}, {"key": "b", "value": 2}]. Then map over this to pick the key and the parts of the value of interest.

The jq script would look like this:

to_entries | map([
    .key,
    .value.OSmodle,
    .value.OSversion,
    .value.IEversion,
    (.value.hardwareUSB | join("&")),
    .value.OrderStatus])
| ["PCID", "OSmodle", "OSversion", "IEversion", "hardwareUSB", "OrderStatus"], .[]
| @csv

Output (with -r):

"PCID","OSmodle","OSversion","IEversion","hardwareUSB","OrderStatus"
"PCID000","LINUX",,"2.15.0","Card reader&keyboard","01"
"PCID999","LINUX","4.0",,"","01"

jqplay

Alex Jasmin
  • 39,094
  • 7
  • 77
  • 67
  • 1
    You could use `( . value | .OSmodle, .IEversion, (.hardwareUSB | join("&"))), .OrderStatus )` instead of using `.value` over and over again. – ikegami Aug 31 '21 at 13:29