I'd like to get a csv/prettyprint output from the following json.
{
"count": 28,
"data": [
{
"id": 100920,
"name": "bam",
"_embedded": {
"addresses": [
{
"address": "10.244.134.115"
}
]
}
},
{
"id": 100983,
"name": "testrecord",
"_embedded": {
"addresses": [
{
"address": "10.244.134.115"
}
]
}
},
{
"id": 101001,
"name": "testrecord2lab",
"_embedded": {
"addresses": [
{
"address": "10.244.134.115"
}
]
}
},
{
"id": 101009,
"name": "a",
"_embedded": {
"addresses": [
{
"address": "10.244.134.100"
}
]
}
},
{
"id": 101019,
"name": "test",
"_embedded": {
"addresses": [
{
"address": "10.244.134.101"
}
]
}
},
{
"id": 101020,
"name": "something",
"_embedded": {}
},
{
"id": 101024,
"name": "db",
"_embedded": {
"addresses": [
{
"address": "10.244.134.11"
}
]
}
},
{
"id": 101036,
"name": "hello",
"_embedded": {
"addresses": [
{
"address": "192.168.30.102"
}
]
}
},
{
"id": 101037,
"name": "external.com",
"_embedded": {
"addresses": []
}
},
{
"id": 101038,
"name": "hey.dbacevic.com",
"_embedded": {
"addresses": []
}
},
{
"id": 101039,
"name": "testexternal",
"_embedded": {}
},
{
"id": 101043,
"name": "ns1",
"_embedded": {
"addresses": [
{
"address": "10.244.134.134"
}
]
}
},
{
"id": 101044,
"name": "cname",
"_embedded": {}
},
{
"id": 101051,
"name": "haha",
"_embedded": {
"addresses": [
{
"address": "10.244.134.102"
}
]
}
},
{
"id": 101063,
"name": "testsomething",
"_embedded": {
"addresses": [
{
"address": "192.168.30.100"
}
]
}
},
{
"id": 101103,
"name": "test",
"_embedded": {}
},
{
"id": 101121,
"name": "hostnamefromgateway",
"_embedded": {
"addresses": [
{
"address": "192.168.30.130"
}
]
}
},
{
"id": 101128,
"name": "micetro-central",
"_embedded": {
"addresses": [
{
"address": "10.244.134.219"
}
]
}
},
{
"id": 101144,
"name": "testingdhcp",
"_embedded": {
"addresses": [
{
"address": "191.168.200.242"
}
]
}
},
{
"id": 101167,
"name": "testCNAME",
"_embedded": {}
},
{
"id": 101168,
"name": "testCNAMEtwo",
"_embedded": {}
},
{
"id": 101169,
"name": "test",
"_embedded": {}
},
{
"id": 101170,
"name": "test",
"_embedded": {}
},
{
"id": 101174,
"name": "asd",
"_embedded": {
"addresses": [
{
"address": "1.1.1.1"
}
]
}
},
{
"id": 101179,
"name": "test",
"_embedded": {
"addresses": [
{
"address": "2.2.2.2"
}
]
}
},
{
"id": 101184,
"name": "arrray1",
"_embedded": {
"addresses": [
{
"address": "3.3.3.3"
}
]
}
},
{
"id": 101189,
"name": "array2",
"_embedded": {
"addresses": [
{
"address": "4.4.4.4"
}
]
}
},
{
"id": 101195,
"name": "ghr",
"_embedded": {
"addresses": [
{
"address": "6.6.6.6"
}
]
}
}
]
}
Something along the lines of:
id,name,address
101121,hostnamefromgateway,192.168.30.130
101128,micetro-central,10.244.134.219
101144,testingdhcp,191.168.200.242
101103,test,{}
101037,external.com,[]
101038,hey.dbacevic.com,[]
...
So far I've been able to get a result like this using jq 'del(.count) | .data[]' | mlr --ijson --ocsv unsparsify.
Note: The input is from a curl and not from a file just to be clear.
id,name,_embedded.addresses.1.address
100920,bam,10.244.134.115
100983,testrecord,10.244.134.115
101001,testrecord2lab,10.244.134.115
101009,a,10.244.134.100
101019,test,10.244.134.101
id,name,_embedded
101020,something,{}
id,name,_embedded.addresses.1.address
101024,db,10.244.134.11
101036,hello,192.168.30.102
id,name,_embedded.addresses
101037,external.com,[]
101038,hey.dbacevic.com,[]
id,name,_embedded
101039,testexternal,{}
id,name,_embedded.addresses.1.address
101043,ns1,10.244.134.134
id,name,_embedded
101044,cname,{}
id,name,_embedded.addresses.1.address
101051,haha,10.244.134.102
101063,testsomething,192.168.30.100
id,name,_embedded
101103,test,{}
id,name,_embedded.addresses.1.address
101121,hostnamefromgateway,192.168.30.130
101128,micetro-central,10.244.134.219
101144,testingdhcp,191.168.200.242
id,name,_embedded
101167,testCNAME,{}
101168,testCNAMEtwo,{}
101169,test,{}
101170,test,{}
id,name,_embedded.addresses.1.address
101174,asd,1.1.1.1
101179,test,2.2.2.2
101184,arrray1,3.3.3.3
101189,array2,4.4.4.4
101195,ghr,6.6.6.6
Is there a way to achieve what I need while only resorting to CLI and not some third party UI websites?
You won't need both jq and mlr. Pick your favorite. Here's an approach using jq with @csv:
jq -r '.data[] | [.id, .name, ._embedded.addresses[0].address] | @csv'
100920,"bam","10.244.134.115"
100983,"testrecord","10.244.134.115"
101001,"testrecord2lab","10.244.134.115"
101009,"a","10.244.134.100"
101019,"test","10.244.134.101"
101020,"something",
101024,"db","10.244.134.11"
101036,"hello","192.168.30.102"
101037,"external.com",
101038,"hey.dbacevic.com",
101039,"testexternal",
101043,"ns1","10.244.134.134"
101044,"cname",
101051,"haha","10.244.134.102"
101063,"testsomething","192.168.30.100"
101103,"test",
101121,"hostnamefromgateway","192.168.30.130"
101128,"micetro-central","10.244.134.219"
101144,"testingdhcp","191.168.200.242"
101167,"testCNAME",
101168,"testCNAMEtwo",
101169,"test",
101170,"test",
101174,"asd","1.1.1.1"
101179,"test","2.2.2.2"
101184,"arrray1","3.3.3.3"
101189,"array2","4.4.4.4"
101195,"ghr","6.6.6.6"
Notes:
To add a header line, prepend it statically, e.g. as ["id", "name", "address"], (.data[] | [.id, .name, ._embedded.addresses[0].address]) | @csv. Demo
To print literally {} or [] if traversing the path ._embedded.addresses[0].address fails at some point, you could use the // operator to provide alternatives, and @json to transform such empty container into a JSON-encoded string. Replace the traversal with (._embedded.addresses[0].address // ._embedded.addresses // ._embedded | strings // @json). Demo
To produce multiple lines if the .addresses array contains more than one item, iterate over them using no index. For example: .data[] | (._embedded.addresses[]? // null) as {$address} | [.id, .name, $address] | @csv Demo