Convert json using jq based on specific constraints
<p>I have a json file 'OpenEnded_mscoco_val2014.json'.The json file contains 121,512 questions.<br>
Here is some sample :</p>

<pre><code>"questions": [
{
  "question": "What is the table made of?",
  "image_id": 350623,
  "question_id": 3506232
},
{
  "question": "Is the food napping on the table?",
  "image_id": 350623,
  "question_id": 3506230
},
{
  "question": "What has been upcycled to make lights?",
  "image_id": 350623,
  "question_id": 3506231
},
{
  "question": "Is this an Spanish town?",
  "image_id": 8647,
  "question_id": 86472
}
</code></pre>

<p>]</p>

<p>I used <code>jq -r '.questions | [map(.question), map(.image_id), map(.question_id)] | @csv' OpenEnded_mscoco_val2014_questions.json &gt;&gt; temp.csv</code> to convert json into csv. <br>
But here output in csv is question followed by image_id which is what above code does.<br>
The expected output is : </p>

<pre><code>"What is table made of",350623,3506232
"Is the food napping on the table?",350623,3506230
</code></pre>

<p>Also is it possible to filter only results having<code>image_id &lt;= 10000</code> and to <code>group questions having same image_id</code>? e.g. 1,2,3 result of json can be combined  to have 3 questions, 1 image_id, 3 question_id.</p>

<p>EDIT : The first problem is solved by <code>possible duplicate question</code>.I would like to know if is it possible to invoke comparison operator on command line in jq for converting json file. In this case get all fields from json if <code>image_id &lt;= 10000</code> only.</p>

<p>1) Given your input (suitably elaborated to make it valid JSON), the following query generates the CSV output as shown:</p>

<pre><code>$ jq -r '.questions[] | [.question, .image_id, .question_id] | @csv'

"What is the table made of?",350623,3506232
"Is the food napping on the table?",350623,3506230
"What has been upcycled to make lights?",350623,3506231
"Is this an Spanish town?",8647,86472
</code></pre>

<p>The key thing to remember here is that @csv requires a flat array, but as with all jq filters, you can feed it a stream.</p>

<p>2) To filter using the criterion <code>.image_id &lt;= 10000</code>, just interpose the appropriate <code>select/1</code> filter:</p>

<pre><code>.questions[]
| select(.image_id &lt;= 10000)
| [.question, .image_id, .question_id]
| @csv
</code></pre>

<p>3) To sort by image_id, use sort_by(.image_id)</p>

<pre><code>.questions
| sort_by(.image_id)
|.[]
| [.question, .image_id, .question_id]
| @csv
</code></pre>

<p>4) To group by <code>.image_id</code> you would pipe the output of the following pipeline into your own pipeline:</p>

<pre><code>.questions | group_by(.image_id)
</code></pre>

<p>You will, however, have to decide exactly how you want to combine the grouped objects.</p>

python json csv filtering jq