hmedia1 hmedia1 - 1 month ago 11
JSON Question

How to combine (concatenate) JSON objects which contain a common key name but unique values in jq

Using the jq command, I am trying to transform some data into a specific format

I have a sample JSON set:



  • {
    "site": [
    {
    "uuid": "23451fae-a14f-49d1-a096-8f033f69dc80",
    "testtype": "Scheduled",
    "name": "JANE DOE HEAD OFFICE",
    "equip": "Unique Item One"
    }
    ]
    }
    {
    "site": [
    {
    "uuid": "23451fae-a14f-49d1-a096-8f033f69dc80",
    "testtype": "Scheduled",
    "name": "JANE DOE HEAD OFFICE",
    "equip": "Unique Item Two"
    }
    ]
    }
    {
    "site": [
    {
    "uuid": "23451fae-a14f-49d1-a096-8f033f69dc80",
    "testtype": "Scheduled",
    "name": "JANE DOE HEAD OFFICE",
    "equip": "Unique Item Three"
    }
    ]
    }









I want to extract the unique values (
equip
);



  • From known locations with common names in the data set (
    site
    ); and

  • Have the values concatenated with a string delimiter that I can change;

  • In order to get an output like this: (shown with a
    comma
    as my choice of delimiter):







  • {
    "site": [
    {
    "uuid": "23451fae-a14f-49d1-a096-8f033f69dc80",
    "testtype": "Scheduled",
    "name": "JANE DOE HEAD OFFICE",
    "equip": "\"Unique Item One\",\"Unique Item Two\",\"Unique Item Three\""
    }
    ]
    }








Just addressing the
equip
value as a first step; some of the command combinations I've tried using the sample dataset (cropping the output to the first k:v pair on each):


  • Trying the -j flag: (Problem: Tailing comma)
    jq -jr '.site[].equip | . + ","' 

    Unique Item One,Unique Item Two,Unique Item Three,






  • Using Reduce (Problem: Leading comma)

    jq -j '.site[].equip | reduce . as $item (""; "" + "," + ($item))'

    ,Unique Item One,Unique Item Two,Unique Item Three

  • Using join(): (Problem: Not used properly so no
    comma
    at all)
    jq '.site | map(.equip) | join(",")'

    "Unique Item One"
    "Unique Item Two"
    "Unique Item Three"






  • Using @csv: (Problem: trailing
    comma
    , and not even from @csv)
    jq -rj '.site[].equip + "," | [.] | @csv'

    "Unique Item One,""Unique Item Two,""Unique Item Three,"





Other things I've tried result in a leading comma, or concatenating and stringifying every single object recursively, but I have come to the conclusion that I'm fundamentally looking at this wrong.


  • If I bring in external input for testing, (In this case passing --arg) I can get close enough to the desired result to progress further, using assignment operators, but it still produces a line for each result, and is getting very inflexible:


  • jq -c --arg new2 "Unique Item Two" --arg new3 "Unique Item Three" -r '.site[] |= .equip + "," + $new2 + "," + $new3 | add | .[] | tojson'

    "Unique Item One,Unique Item Two,Unique Item Three"



Now I'm not sure if I'm missing something simple or if this requires some complex counting and iteration.

Note: I know how easy it would be to cut the output up externally - there are certain reasons that I want to do this entirely in jq, as it's really the structure I am trying to get right.

Answer

You could group them up to determine what equip values need to be combined, then you could build up your string.

$ jq -n --arg delim ',' '{
    site: [inputs.site[]]
        | group_by(.uuid)
        | map({
            uuid: .[0].uuid,
            testtype: .[0].testtype,
            name: .[0].name,
            equip: (map(.equip | tojson) | unique | join($delim))
        })
}' input.json

This produces: (note: the item order is not necessarily preserved)

{
  "site": [
    {
      "uuid": "23451fae-a14f-49d1-a096-8f033f69dc80",
      "testtype": "Scheduled",
      "name": "JANE DOE HEAD OFFICE",
      "equip": "\"Unique Item One\",\"Unique Item Two\",\"Unique Item Three\""
    }
  ]
}

If the string is meant to look like csv data and needs escaping, be sure to do so. Assuming quotes need to be escaped, you could modify this part:

map(.equip | gsub("\"";"\"\"") |  tojson)