Parse json object to column in linux

Question

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

jq -j '.country, " ", .city, "\n"'? Then paste other_file jq_output? — Etan Reisner
– Etan Reisner, Commented Apr 5, 2016 at 14:48

jijinp · Accepted Answer · 2016-04-05 14:20:41Z

2

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

answered Apr 5, 2016 at 14:20

jijinp

2,6821 gold badge15 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeff Mercado Over a year ago

Consider using string interpolation instead. It'll be much cleaner.

Ed Morton · Accepted Answer · 2016-04-05 14:15:22Z

1

$ 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...

answered Apr 5, 2016 at 14:15

Ed Morton

209k18 gold badges90 silver badges212 bronze badges

1 Comment

Sasee Over a year ago

Thanks Ed, it helped too

Ben Dowling · Accepted Answer · 2016-04-06 22:06:00Z

0

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.

answered Apr 6, 2016 at 22:06

Ben Dowling

17.6k8 gold badges93 silver badges104 bronze badges

Collectives™ on Stack Overflow

Parse json object to column in linux

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related