CODEWITHSUNDEEP
jsonsortinggroup-byjq
dzafer
dzafer

Reputation: 49

Grouping and sorting JSON records in Bash

I'm using curl to get JSON file. My problem is that I would like to get group of 4 words in one line, then break the line, and sort it by first column.

I'm trying:

curl -L 'http://mylink/ | jq '.[]| .location, .host_name, .serial_number, .model'

I'm getting

"Office-1"    
"work-1"    
"11xxx111"    
"hp"    
"Office-2"    
"work-2"    
"33xxx333"    
"lenovo"    
"Office-1"    
"work-3"    
"22xxx222"    
"dell"

I would like to have:

"Office-1", "work-1", "11xxx111", "hp"    
"Office-1" "work-3", "22xxx222", "dell"    
"Office-2", "work-2", "33xxx333", "lenovo"

I tried jq -S ".[]| .location| group_by(.location), and few other combinations like sort_by(.location) but it doesn't work. I'm getting error: jq: error (at <stdin>:1): Cannot iterate over string ("Office-1")

Sample of my JSON file:

[
  {
    "location": "Office-1",
    "host_name": "work-1",
    "serial_number": "11xxx111",
    "model": "hp"
  },
  {
    "location": "Office-2",
    "host_name": "work-2",
    "serial_number": "33xxx333",
    "model": "lenovo"
  },
  {
    "location": "Office-1",
    "host_name": "work-3",
    "serial_number": "22xxx222",
    "model": "dell"
  }
]

Upvotes: 1

Views: 346

Answers (3)

tripleee
tripleee

Reputation: 189749

You can ask jq to produce arbitrary formatted strings.

curl -L 'http://mylink/ |
jq -r '.[]| "\"\(.location)\", \"\(.host_name)\", \"\(.serial_number)\", \"\(.model)\""' |
sort

Inside the double quotes, \" produces literal double quotes, and \(.field) interpolates a field name. The -r option is required to produce output which isn't JSON.

Upvotes: 2

peak
peak

Reputation: 116919

To sort by .location only, without an external sort:

map( [ .location, .host_name, .serial_number, .model] )
| sort_by(.[0])[]
| map("\"\(.)\"") | join(", ")

The ", " is per the stated requirements.

If you want the output as CSV, simply replace the last line in the jq program above by @csv.

If minimizing keystrokes is a goal, then if you are certain that the keys are always in the desired order, you could get away with replacing the first line by map( [ .[] ] )

Upvotes: 3

Joe Casadonte
Joe Casadonte

Reputation: 16869

This will get you the output you wanted:

jq -r 'group_by(.location) | .[] | .[] | map(values) | "\"" + join ("\", \"") + "\""'

like so:

$ jq -r 'group_by(.location) | .[] | .[] | map(values) | "\"" + join ("\", \"") + "\""' /tmp/so7713.json
"Office-1", "work-1", "11xxx111", "hp"
"Office-1", "work-3", "22xxx222", "dell"
"Office-2", "work-2", "33xxx333", "lenovo"

If you want it all as one string, it's a bit simpler:

$ jq 'group_by(.location) | .[] | .[] | map(values) | join (", ")' /tmp/so7713.json
"Office-1, work-1, 11xxx111, hp"
"Office-1, work-3, 22xxx222, dell"
"Office-2, work-2, 33xxx333, lenovo"

Note the lack of -r in the second example.

I feel there has to be a better way of doing .[] | .[], but I don't know what it is (yet).

Upvotes: 1

Related Questions