1

Let's say I have data like the following:

table_name id
table_name col_1
table_name col_2
another_table_name id
another_table_name col_1

How can I use awk to group by the first column and reduce the second to a comma-separated list? Per the example, the output should be:

table_name id,col_1,col_2
another_table_name id,col_1

The idea here is I can use the second column to construct a JSON array (the value) via jq based on the data in first column (the key):

{"table_name": ["id", "col_1", "col_2"]}
{"another_table_name": ["id", "col_1"]}

Have everything working except the grouby / "reduce" step; any help is most appreciated!

4
  • If the file isn't big: awk '{a[$1]=a[$1]","$2}END{for(i in a)print i a[i]}' file
    – user414777
    Jan 25 at 8:01
  • Or (in order to obtain the (idiosyncratic) required format awk '{a[$1]=a[$1]","$2}END{for(i in a)print i,substr(a[i],2)}' file: But the "correctly escaping json strings" part is much more interesting than you think.
    – user414777
    Jan 25 at 8:06
  • @user414777, just to check my understanding -- your solution chains columns two entries together (in the first set of braces) and then prints each key followed by this chain in the second set of braces, right?
    – Per48edjes
    Jan 25 at 19:01
  • Yes. Just as you would've done to build that in javascript. This does not assume that the lines are sorted. But as everything's stored in memory, this will not work with very large files.
    – user414777
    Jan 25 at 21:16
1

The simplest option to create your JSON structure would be by using the jo utility:

$ sed 's/[[:blank:]]\{1,\}/[]=/' file | jo -p
{
   "table_name": [
      "id",
      "col_1",
      "col_2"
   ],
   "another_table_name": [
      "id",
      "col_1"
   ]
}

The sed script creates the input for jo by replacing the first run of spaces or tabs by []=. With your example data this yields

table_name[]=id
table_name[]=col_1
table_name[]=col_2
another_table_name[]=id
another_table_name[]=col_1

The jo utility then takes care of properly encoding the data and creating the JSON document. Drop the -p option to jo if you want compact output.


Old answer using jq instead, but does not take care of data that needs JSON encoding:

Assuming none of the data needs special JSON encoding:

jq -n "$(awk '{ printf ".\"%s\" += [\"%s\"] |\n", $1, $2 } END { print "." }' file)"

or

awk '{ printf ".\"%s\" += [\"%s\"] |\n", $1, $2 } END { print "." }' file |
jq -n -f /dev/stdin

This uses awk to create jq expressions that builds your arrays. For the given example data, the jq expression would be

."table_name" += ["id"] |
."table_name" += ["col_1"] |
."table_name" += ["col_2"] |
."another_table_name" += ["id"] |
."another_table_name" += ["col_1"] |
.

When evaluated by jq, this creates the JSON document

{
  "table_name": [
    "id",
    "col_1",
    "col_2"
  ],
  "another_table_name": [
    "id",
    "col_1"
  ]
}

Add the -c option to jq to get compact output.


Using sed instead of awk:

sed -e 's/\([^[:blank:]]*\)[[:blank:]]*\(.*\)/."\1" += ["\2"] |/' \
    -e '$ s/$/./' file |
jq -n -f /dev/stdin
1
  • This is a spectacular answer -- thank you for bringing jo to my attention. I was asking about an awk solution for my own practice, but I agree with you: it's best take advantage of [much smarter] abstraction if & where I can. :)
    – Per48edjes
    Jan 25 at 18:58
1

The basic idea is to remember the first field from the previous line, and if it is the same then print out a comma and the second field otherwise print the first and second fields. Then handle some boundary cases

awk '$1 == saved { printf(",%s", $2); next ; }
     neednl { print ""; }
     { saved=$1 ; neednl=1 ;printf("%s %s",$1,$2) ; }
     END { if (neednl) { print "" ; }}'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.