CODEWITHSUNDEEP
bashawksedjq
Sasee
Sasee

Reputation: 47

Parse json object to column in linux

I have a output of json format as below

{
"ip": "0.0.0.0",
"hostname": "No Hostname",
"city": "Beijing",
"region": "Beijing Shi",
"country": "CN",
"loc": "39.9289,116.3883",
"org": "AS55967 Beijing Baidu Netcom Science and Technology Co., Ltd."
}

i need values of country and city as below 

CN        Beijing

i have used below jq command and sed command to display country but dont know how to display city as another column.

jq

 curl  -s ipinfo.io/0.0.0.0 | jq '.country'

sed

 curl  -s ipinfo.io/0.0.0.0 | sed '/country/!d' | sed s/\"country\":\ //g | sed 's/\"//g' | sed 's/\,//g'

The output of this country and city columns should be added to 4th & 5th column of another csv file sample below

2016-03-29 00:05:23 0.0.0.0 CN Beijing 10.0.0.197 
2016-03-29 00:56:37 1.1.1.1 FR France 10.0.1.117
2016-03-29 00:57:20 2.2.2.2 FR France 10.0.0.197

Upvotes: 1

Views: 2030

Answers (3)

Ben Dowling
Ben Dowling

Reputation: 17541

You can first put to two fields you're interested in into an array:

$ curl -s ipinfo.io | jq '[.country, .city]'
[
  "US",
  "Mountain View"
]

And then convert to csv with @csv:

$ curl -s ipinfo.io | jq '[.country, .city] | @csv'
"\"US\",\"Mountain View\""

The -r (raw) argument cleans up some of that escaping:

$ curl -s ipinfo.io | jq -r '[.country, .city] | @csv'
"US","Mountain View"

You can then use paste to combine the output with another file.

Upvotes: 0

jijinp
jijinp

Reputation: 2662

curl  -s ipinfo.io/0.0.0.0 | jq -r '.country +"\t"+  .city'

Upvotes: 2

Ed Morton
Ed Morton

Reputation: 203635

$ cat tst.awk
BEGIN { FS="\": \""; OFS="\t" }
{ gsub(/^"|",$/,""); f[$1] = $2 }
/}/ { print f["country"], f["city"]; delete f }

$ awk -f tst.awk file
CN      Beijing

Since you didn't provide the "other file" to add the output of this to or provide any details on how you want to do that, it's not clear what you want to do with that so it's left to you...

Upvotes: 1

Related Questions