Transforming high-redundancy CSV data into nested JSON using jq (or awk)?

Here's a jq solution that assumes the CSV input is very simple (e.g., no field has embedded commas), followed by a brief explanation.

To handle arbitrary CSV, you could use a CSV-to-TSV conversion tool in conjunction with the jq program given below with trivial modifications.

A Solution

The following jq program assumes jq is invoked with the -R option. (The -n option should not be used as the header row is read without using input.)

# sort-free plug-in replacement for the built-in group_by/1
def GROUP_BY(f): 
   reduce .[] as $x ({};
     ($x|f) as $s
     | ($s|type) as $t
     | (if $t == "string" then $s else ($s|tojson) end) as $y
     | .[$t][$y] += [$x] )
   | [.[][]]
   ;

# input: an array
def obj($keys):
  . as $in | reduce range(0; $keys|length) as $i ({}; .[$keys[$i]] = $in[$i]);

# input: an array to be grouped by $keyname 
# output: an object 
def gather_by($keyname; $newkey):
  ($keyname + "s") as $plural
  | GROUP_BY(.[$keyname])
  | {($plural): map({($keyname): .[0][$keyname], 
                     ($newkey) : map(del(.[$keyname])) } ) }
  ;

split(",") as $headers
| [inputs
   | split(",")
   | obj($headers)
  ]
| gather_by("broker"; "clients")
| .brokers[].clients |= (gather_by("client"; "contracts") | .clients)
| .brokers[].clients[].contracts |= (gather_by("contract_id"; "documents") | .contract_ids)
| .brokers[].clients[].contracts[].documents |= (gather_by("task_type"; "doc_names")  | .task_types)
| .brokers[].clients[].contracts[].documents[].doc_names |= map(.doc_names)
| .brokers

Explanation

The expected output as shown respects the ordering of the input lines, and so jq's built-in group_by may not be appropriate; hence GROUP_BY is defined above as a plug-in replacement for group_by. It's a bit complicated because it is completely generic in the same way as group_by.

The obj filter converts an array into an object with keys $keys.

The gather_by filter groups together items in the input array as appropriate for the present problem.

gather_by/2 example

To get a feel for what gather_by does, here's an example:

[ {a:1,b:1}, {a:2, b:2}, {a:1,b:0}] | gather_by("a"; "objects")

produces:

{
  "as": [
    {
      "a": 1,
      "objects": [
        {
          "b": 1
        },
        {
          "b": 0
        }
      ]
    },
    {
      "a": 2,
      "objects": [
        {
          "b": 2
        }
      ]
    }
  ]
}

Output

[
  {
    "broker": "alice@company.com",
    "clients": [
      {
        "client": "John Doe",
        "contracts": [
          {
            "contract_id": "33333",
            "documents": [
              {
                "task_type": "prove-employment",
                "doc_names": [
                  "important-doc-pdf",
                  "paperwork-pdf"
                ]
              },
              {
                "task_type": "submit-application",
                "doc_names": [
                  "blah-pdf"
                ]
              }
            ]
          },
          {
            "contract_id": "00000",
            "documents": [
              {
                "task_type": "prove-employment",
                "doc_names": [
                  "test-pdf"
                ]
              },
              {
                "task_type": "submit-application",
                "doc_names": [
                  "test-pdf"
                ]
              }
            ]
          }
        ]
      },
      {
        "client": "Jane Smith",
        "contracts": [
          {
            "contract_id": "11111",
            "documents": [
              {
                "task_type": "prove-employment",
                "doc_names": [
                  "important-doc-pdf"
                ]
              },
              {
                "task_type": "submit-application",
                "doc_names": [
                  "paperwork-pdf",
                  "unimportant-pdf"
                ]
              }
            ]
          }
        ]
      }
    ]
  },
  {
    "broker": "bob@company.com",
    "clients": [
      {
        "client": "John Doe",
        "contracts": [
          {
            "contract_id": "66666",
            "documents": [
              {
                "task_type": "submit-application",
                "doc_names": [
                  "pdf-I-pdf"
                ]
              }
            ]
          },
          {
            "contract_id": "77777",
            "documents": [
              {
                "task_type": "submit-application",
                "doc_names": [
                  "pdf-J-pdf"
                ]
              }
            ]
          }
        ]
      }
    ]
  }
]

Here's a jq solution which uses a generic approach that makes no reference to specific header names except for the specification of certain plural forms.

The generic approach is encapsulated in the recursively defined filter nested_group_by($headers; $plural).

The main assumptions are:

1. The CVS input can be parsed by splitting on commas;
2. jq is invoked with the -R command-line option.

# Emit a stream of arrays, each array being a group defined by a value of f,
# which can be any jq filter that produces exactly one value for each item in `stream`.
def GROUP_BY(f): 
   reduce .[] as $x ({};
     ($x|f) as $s
     | ($s|type) as $t
     | (if $t == "string" then $s else ($s|tojson) end) as $y
     | .[$t][$y] += [$x] )
   | [.[][]]
   ;

def obj($headers):
  . as $in | reduce range(0; $headers|length) as $i ({}; .[$headers[$i]] = $in[$i]);

def nested_group_by($array; $plural):
  def plural: $plural[.] // (. + "s");
  if $array == [] then .
  elif $array|length == 1 then GROUP_BY(.[$array[0]]) | map(map(.[])[])
  else ($array[1] | plural) as $groupkey
  | $array[0] as $a0
  | GROUP_BY(.[$a0])
  | map( { ($a0): .[0][$a0], ($groupkey): map(del( .[$a0] )) } )
  | map( .[$groupkey] |= nested_group_by($array[1:]; $plural) )
  end
  ;

split(",") as $headers
| {contract_id: "contracts",
   task_type: "documents",
   doc_names: "doc_names" } as $plural
| [inputs
   | split(",")
   | obj($headers)
  ]
| nested_group_by($headers; $plural)