how to refine jq output

I have a json file. A simple example looks like:

[ "host": "a.com", "ip": "1.2.2.3", "port": 8, "name":"xyz" , "host": "b.com", "ip": "2.5.0.4", "port": 3, "name":"xyz" , "host": "c.com", "ip": "9.17.6.7", "port": 4, "name":"xyz" ]

I want to extract the "host" and "ip" value and add them in a comma separated values file. Each record in a line as follows:

a.com,1.2.2.3 b.com,2.5.0.4 c.com,9.17.6.7

I installed jq library to parse the json file. I executed this command:

cat test.json | jq '. | host: .host, ip: .ip'

The output I get is as the following:

"host": "a.com", "ip": "1.2.2.3" "host": "b.com", "ip": "2.5.0.4" "host": "c.com", "ip": "9.17.6.7"

Is there any way I can extract the output as I want? This output that jq produced require additional script to parse it and save the values as I want in csv format, one item in a line.

Doesn't the @csv operator do exactly what you want?
– Barmar
Aug 24 at 23:48

@csv

2 Answers
2

Use the @csv format to produce CSV output from an array of the values.

@csv

cat test.json | jq -r '. | [.host, .ip] | @csv'

The -r option is needed to get raw output rather than JSON, which would wrap an extra set of quotes around the result and escape the quotes that surround each field.

-r

To remove quotes:

$ cat test.json | jq -r '. | [ .host, .ip ] | @csv' | sed 's/"//g' a.com,1.2.2.3 b.com,2.5.0.4 c.com,9.17.6.7

If using OS X, use Homebrew to install GNU sed.

sed

Using sed like this will undoubtedly be acceptable in this case, but in general it could produce incorrect results. In general, if one wants CSV, it is best to use @csv without post-processing, but if one is certain the values do not contain commas or double-quotes, it might be acceptable to use jq’s string interpolation to avoid the quotation marks: "(.host),(.ip)"
– peak
Aug 25 at 0:49

Fair comment. I just figured that hostnames and Internet addresses will not contain quotes per RFC 952, so this should be safe to do. When post-processing other strings, caution should be applied, for sure. tools.ietf.org/html/rfc952
– Alex Reynolds
Aug 25 at 0:57

The md tricked me up: that should be "(.host),(.ip)"
– peak
Aug 25 at 1:00

"(.host),(.ip)"

@peak This produces the format I want: jq -r '.[0] | "(.host),(.ip)"'. Do you see anything wrong with it?
– user9371654
Aug 25 at 9:08

jq -r '.[0] | "(.host),(.ip)"'

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt