How extract array of array as TSV from json using jq?

Question

I'd like to extract the data from here.

http://d2p2.pro/api/seqid/["P04637","P51608"]

I currently use .[] | [ .[][0], .[][2]["disorder"]["consranges"][] ]. And I get this.

[
  "P04637",
  [
    "1",
    "22"
  ],
  [
    "26",
    "27"
  ],
  [
    "30",
    "31"
  ],
  [
    "33",
    "97"
  ],
  [
    "281",
    "329"
  ],
  [
    "343",
    "393"
  ]
]
[
  "P51608",
  [
    "1",
    "118"
  ],
  [
    "151",
    "281"
  ],
  [
    "283",
    "486"
  ]
]

But I want to get the result in a TSV format, as this.

P04637<TAB>1<TAB>22
P04637<TAB>26<TAB>27
...

Does anybody know what is the correct jq command to extract the output in this format?

EDIT:

Of the following to files.

==> 1.json <==
{"P51608":[["P51608","Uniprot 2018_03 genome",{"structure":{"weak":[],"pfam":[["Domain","CL0081","PF01429.14","MBD","Methyl-CpG binding domain","4e-21","74.3","90","164"]],"strong":[["DNA-binding domain","54171","6.54e-33","Methyl-CpG-binding domain, MBD","54178","0.000000724","73-187"]]},"disorder":{"disranges":[["VLXT","1","120"],["VSL2b","1","117"],["PrDOS","1","93"],["PV2","1","118"],["IUPred-S","1","10"],["IUPred-L","1","113"],["Espritz-N","1","133"],["Espritz-X","1","119"],["Espritz-D","1","486"],["IUPred-S","13","111"],["IUPred-L","115","126"],["IUPred-L","136","137"],["Espritz-N","141","243"],["IUPred-L","143","143"],["PV2","146","486"],["Espritz-X","146","218"],["VSL2b","150","486"],["IUPred-L","150","214"],["IUPred-S","153","214"],["PrDOS","161","486"],["VLXT","163","209"],["IUPred-S","216","216"],["IUPred-L","216","486"],["IUPred-S","218","218"],["IUPred-S","220","278"],["Espritz-X","226","243"],["VLXT","234","281"],["Espritz-N","252","263"],["Espritz-N","266","273"],["Espritz-N","283","294"],["Espritz-X","285","289"],["VLXT","290","319"],["Espritz-N","298","312"],["Espritz-X","318","486"],["Espritz-N","324","486"],["VLXT","334","362"],["IUPred-S","334","486"],["VLXT","377","436"],["VLXT","446","483"],["VLXT","485","485"]],"conflict":[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,7,8,8,8,7,6,5,4,4,4,4,4,3,3,2,2,2,2,2,2,1,1,2,2,1,1,1,2,2,4,3,2,4,4,4,4,6,7,7,7,7,7,7,7,7,7,7,8,8,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],"consranges":[["1","118"],["151","281"],["283","486"]],"consensus":[9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,7,8,8,8,7,6,5,4,4,4,4,4,3,3,2,2,2,2,2,2,1,1,2,2,1,1,1,2,2,4,3,2,4,4,4,4,6,7,7,7,7,7,7,7,7,7,7,8,8,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,8,8,8,8,7,7,7,8,7,7,7,7,7,7,7,8,8,8,8,8,8,8,8,9,9,9,9,9,9,9,9,9,9,7,7,7,7,7,7,7,7,8,8,8,8,8,8,8,8,8,8,8,8,7,7,8,8,8,8,8,8,8,8,7,7,7,7,7,7,7,7,6,7,7,8,8,8,8,8,8,8,8,8,8,7,7,7,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,7,7,7,7,7,8,8,7,7,7,7,8,8,8,8,8,8,8,8,8,8,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,8,8,8,8,8,8,8,8,8,8,8,8,8,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,8,8,8,8,8,8,8,8,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,9,8]}}]],"P01106":[]}
==> 2.json <==
{"P01106":[],"P51608":[["P51608","Uniprot 2018_03 genome",{"structure":{"strong":[["DNA-binding domain","54171","6.54e-33","Methyl-CpG-binding domain, MBD","54178","0.000000724","73-187"]],"weak":[],"pfam":[["Domain","CL0081","PF01429.14","MBD","Methyl-CpG binding domain","4e-21","74.3","90","164"]]},"disorder":{"conflict":[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,7,8,8,8,7,6,5,4,4,4,4,4,3,3,2,2,2,2,2,2,1,1,2,2,1,1,1,2,2,4,3,2,4,4,4,4,6,7,7,7,7,7,7,7,7,7,7,8,8,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],"consranges":[["1","118"],["151","281"],["283","486"]],"disranges":[["VLXT","1","120"],["VSL2b","1","117"],["PrDOS","1","93"],["PV2","1","118"],["IUPred-S","1","10"],["IUPred-L","1","113"],["Espritz-N","1","133"],["Espritz-X","1","119"],["Espritz-D","1","486"],["IUPred-S","13","111"],["IUPred-L","115","126"],["IUPred-L","136","137"],["Espritz-N","141","243"],["IUPred-L","143","143"],["PV2","146","486"],["Espritz-X","146","218"],["VSL2b","150","486"],["IUPred-L","150","214"],["IUPred-S","153","214"],["PrDOS","161","486"],["VLXT","163","209"],["IUPred-S","216","216"],["IUPred-L","216","486"],["IUPred-S","218","218"],["IUPred-S","220","278"],["Espritz-X","226","243"],["VLXT","234","281"],["Espritz-N","252","263"],["Espritz-N","266","273"],["Espritz-N","283","294"],["Espritz-X","285","289"],["VLXT","290","319"],["Espritz-N","298","312"],["Espritz-X","318","486"],["Espritz-N","324","486"],["VLXT","334","362"],["IUPred-S","334","486"],["VLXT","377","436"],["VLXT","446","483"],["VLXT","485","485"]],"consensus":[9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,7,8,8,8,7,6,5,4,4,4,4,4,3,3,2,2,2,2,2,2,1,1,2,2,1,1,1,2,2,4,3,2,4,4,4,4,6,7,7,7,7,7,7,7,7,7,7,8,8,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,8,8,8,8,7,7,7,8,7,7,7,7,7,7,7,8,8,8,8,8,8,8,8,9,9,9,9,9,9,9,9,9,9,7,7,7,7,7,7,7,7,8,8,8,8,8,8,8,8,8,8,8,8,7,7,8,8,8,8,8,8,8,8,7,7,7,7,7,7,7,7,6,7,7,8,8,8,8,8,8,8,8,8,8,7,7,7,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,7,7,7,7,7,8,8,7,7,7,7,8,8,8,8,8,8,8,8,8,8,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,8,8,8,8,8,8,8,8,8,8,8,8,8,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,8,8,8,8,8,8,8,8,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,8,9,8]}}]]}

Here is the output. So "try(.[][0]) | [.[0]] + (.[2].disorder.consranges[]) | @tsv" depends on the order of entries in the input. Therefore, it is not robust.

$ jq -r "try(.[][0]) | [.[0]] + (.[2].disorder.consranges[]) | @tsv" < /tmp/1.json
P51608  1   118
P51608  151 281
P51608  283 486
$ jq -r "try(.[][0]) | [.[0]] + (.[2].disorder.consranges[]) | @tsv" < /tmp/2.json

peak · Accepted Answer · 2019-10-03 02:41:12Z

1

The following filter produces results as shown below, which are in accordance with those in the question, so hopefully this will provide the guidance you're looking for:

.[][0]
| [.[0]] + (.[2].disorder.consranges[])
| @tsv

Output

P04637  1   22
P04637  26  27
P04637  30  31
P04637  33  97
P04637  281 329
P04637  343 393
P51608  1   118
P51608  151 281
P51608  283 486

Robustification

The requirements are not very clear with respect to robustness, but sprinkling one or more try statements around will confer a measure of robustness. The following will handle both the two variants in the update to the Q:

  .[][0]
  | try [.[0]] + (.[2].disorder.consranges[])
  | @tsv

Flexibility

Here is an illustration of one way to increase the flexibility of the program with respect to the structure of the inputs, without using try at all:

.[][]
| .[0] as $p
| ..
| objects
| select(has("disorder"))
| [$p] + (.disorder.consranges[])
| @tsv

But be careful what you wish for!

edited Oct 3, 2019 at 2:41

answered Oct 2, 2019 at 2:22

peak

119k21 gold badges185 silver badges218 bronze badges

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

7 Comments

user1424739 Over a year ago

I don't get how to add the 3rd column. Could you show me?

user1424739 Over a year ago

Thanks. How to make the query robust against something empty at this URL <d2p2.pro/api/seqid/["P01106"]>.

peak Over a year ago

You could (for example) prefix the entire expression with: try

user1424739 Over a year ago

No. This will not work when some records still contain valid results. No results will not printed (E.g., <d2p2.pro/api/seqid/["P51608","P01106"]>)

peak Over a year ago

Please see new section. By the way, your robustness requirements were not (and are not) very clear.

|

Collectives™ on Stack Overflow

How extract array of array as TSV from json using jq?

1 Answer 1

Output

Robustification

Flexibility

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Output

Robustification

Flexibility

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related