CODEWITHSUNDEEP
jq
smoooosher
smoooosher

Reputation: 133

Group nested array objects to parent key in JQ

I have JSON coming from an external application, formatted like so:

   {
      "ticket_fields": [
      {
        "url": "https://example.com/1122334455.json",
        "id": 1122334455,
        "type": "tagger",
        "custom_field_options": [
          {
            "id": 123456789,
            "name": "I have a problem",
            "raw_name": "I have a problem",
            "value": "help_i_have_problem",
            "default": false
          },
          {
            "id": 456789123,
            "name": "I have feedback",
            "raw_name": "I have feedback",
            "value": "help_i_have_feedback",
            "default": false
          },
        ]
      }
      {
        "url": "https://example.com/6677889900.json",
        "id": 6677889900,
        "type": "tagger",
        "custom_field_options": [
          {
            "id": 321654987,
            "name": "United States,
            "raw_name": "United States",
            "value": "location_123_united_states",
            "default": false
          },
          {
            "id": 987456321,
            "name": "Germany",
            "raw_name": "Germany",
            "value": "location_456_germany",
            "default": false
          }
        ]
      }
    ]
  }

The end goal is to be able to get the data into a TSV in the sense that each object in the custom_field_options array is grouped by the parent ID (ticket_fields.id), and then transposed such that each object would be represented on a single line, like so:

Ticket Field ID Name Value
1122334455 I have a problem help_i_have_problem
1122334455 I have feedback help_i_have_feedback
6677889900 United States location_123_united_states
6677889900 Germany location_456_germany

I have been able to export the data successfully to TSV already, but it reads per-line, and without preserving order, like so:

Using jq -r '.ticket_fields[] | select(.type=="tagger") | [.id, .custom_field_options[].name, .custom_field_options[].value] | @tsv'

Ticket Field ID Name Name Value Value
1122334455 I have a problem I have feedback help_i_have_problem help_i_have_feedback
6677889900 United States Germany location_123_united_states location_456_germany

Each of the custom_field_options arrays in production may consist of any number of objects (not limited to 2 each). But I seem to be stuck on how to appropriately group or map these objects to their parent ticket_fields.id and to transpose the data in a clean manner. The select(.type=="tagger") is mentioned in the query as there are multiple values for ticket_fields.type which need to be filtered out.

Based on another answer on here, I did try variants of jq -r '.ticket_fields[] | select(.type=="tagger") | map(.custom_field_options |= from_entries) | group_by(.custom_field_options.ticket_fields) | map(map( .custom_field_options |= to_entries))' without success. Any assistance would be greatly appreciated!

Upvotes: 2

Views: 1571

Answers (1)

pmf
pmf

Reputation: 36033

You need two nested iterations, one in each array. Save the value of .id in a variable to access it later.

jq -r '
  .ticket_fields[] | select(.type=="tagger") | .id as $id
  | .custom_field_options[] | [$id, .name, .value]
  | @tsv
'

Upvotes: 4

Related Questions