Format nested JSON input using JQ?

Question

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!!

First, it would seem the input is intended to be JSON, so if that's the case, please fix it. Second, the expected output is invalid as JSON, so it would be helpful to know what you really want, especially as the duplication of keys within each object is so problematic. Third, since SO is not a free programming service, questions on SO are usually accompanied by an illustration showing at least one attempt to solve the problem. — peak
– peak, Commented Aug 28, 2020 at 7:45
Ultimately I want to convert the input to CSV. the input I provided is only a snip of what I actually have, so I manually typed in some values and brackets. Might have made it look weird. — Joe Jiang
– Joe Jiang, Commented Aug 31, 2020 at 3:49
essentially I'd want the output to be grouped by id (2185 / 2186) and list the 'value' row, separated by comma. — Joe Jiang
– Joe Jiang, Commented Aug 31, 2020 at 3:50

peak · Accepted Answer · 2020-08-31 04:00:47Z

1

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"

edited Aug 31, 2020 at 4:00

answered Aug 28, 2020 at 7:54

peak

119k21 gold badges185 silver badges218 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joe Jiang Over a year ago

I am trying to convert this to csv. This works but when I try to convert it to csv it says 'object ({"id":"2185...) cannot be csv-formatted, only array'

Joe Jiang Over a year ago

Thank you very much! One last question - how would I go about adding an arbitrary header at the beginning?

peak Over a year ago

The basic idea: (HEADER), (JQ_FILTER), e.g. "a\tb\tc", (... | @tsv)

Collectives™ on Stack Overflow

Format nested JSON input using JQ?

1 Answer 1

program.jq

Output

Producing CSV

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

program.jq

Output

Producing CSV

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related