CODEWITHSUNDEEP
jsonjq
Joe Jiang
Joe Jiang

Reputation: 33

Format nested JSON input using JQ?

I am trying to convert the sample input below into the output below using jq:

Input JSON

  "elements": [
    {
      "type": "CustomObjectData",
      "id": "2185",
      "fieldValues": [
        {
          "type": "FieldValue",
          "id": "169",
          "value": "9/6/2017 12:00:00 AM"
        },
        {
          "type": "FieldValue",
          "id": "190",
          "value": "ABC"
        } 
        ]
        },
 {
      "type": "CustomObjectData",
      "id": "2186",
      "contactId": "13",
      "fieldValues": [
        {
          "type": "FieldValue",
          "id": "169",
          "value": "8/31/2017 12:00:00 AM"
        },
        {
          "type": "FieldValue",
          "id": "190",
          "value": "DEF"
        }
        ]
        }
]

Desired Output (group by id)

Essentially trying to extract "value" field from each "fieldValues" object and group them by "id"

{
"id:"2185",
"value": "9/6/2017 12:00:00 AM",
 "value": "ABC"
},
{
"id:"2186",
"value": "8/31/2017 12:00:00 AM",
 "value": "DEF"
}

What jq syntax should i use to achieve this? Thanks very much!!

Upvotes: 0

Views: 251

Answers (1)

peak
peak

Reputation: 116810

Assuming the input shown in the Q has been modified in the obvious way to make it valid JSON, the following filter will produce the output as shown below, that is, a stream of valid JSON values that is similar to the allegedly expected output included in the Q. If a single array is desired, one possibility would be to wrap the program in square brackets.

program.jq

.elements[]
| {id, values: [ .fieldValues[].value] }

Output

{
  "id": "2185",
  "values": [
    "9/6/2017 12:00:00 AM",
    "ABC"
  ]
}
{
  "id": "2186",
  "values": [
    "8/31/2017 12:00:00 AM",
    "DEF"
  ]
}

Producing CSV

One of many possibilities:

.elements[]
| [.id] + [.fieldValues[].value]
| @csv

With the -r command-line option, this produces the following CSV:

"2185","9/6/2017 12:00:00 AM","ABC"
"2186","8/31/2017 12:00:00 AM","DEF"

Upvotes: 1

Related Questions