Use JQ to parse JSON array of objects, using select to match specified key-value in the object element, then convert to CSV

Question

Use JQ to parse JSON array of objects, using select to match specified key-value in the object element, then convert to CSV.

Data Source:

{
  "Other": [],
  "Objects": [
    {
      "ObjectElementName": "Test 123",
      "ObjectElementArray": [],
      "ObjectElementUnit": "1"
    },
    {
      "ObjectElementName": "Test ABC 1",
      "ObjectElementArray": [],
      "ObjectElementUnit": "2"
    },
    {
      "ObjectElementName": "Test ABC 2",
      "ObjectElementArray": [],
      "ObjectElementUnit": "5"
    }
  ],
  "Language": "en-US"
}

JQ command to extract

jq -r '.Objects[] | select(.ObjectElementName | contains("ABC"))'

Output given (individual objects only, no JSON structure)...

{
  "ObjectElementName": "Test ABC 1",
  "ObjectElementArray": [],
  "ObjectElementUnit": "2"
}
{
  "ObjectElementName": "Test ABC 2",
  "ObjectElementArray": [],
  "ObjectElementUnit": "5"
}

Output needed in this format (preserving the JSON array above the objects)

{
  "Other": [],
  "Objects": [
    {
      "ObjectElementName": "Test ABC 1",
      "ObjectElementArray": [],
      "ObjectElementUnit": "2"
    },
    {
      "ObjectElementName": "Test ABC 2",
      "ObjectElementArray": [],
      "ObjectElementUnit": "5"
    }
  ],
  "Language": "en-US"
}

Output needed in this format, so CSV conversion can be executed

in2csv -f json --key Objects > output.csv

Output CSV required

ObjectElementName,ObjectElementUnit
Test ABC 1,2
Test ABC 2,5

peak · Accepted Answer · 2019-11-22 15:17:05Z

1

If you want CSV, you should probably use jq's @csv filter, which essentially guarantees valid CSV output. For example, using your input:

jq -r '
  ["ObjectElementName","ObjectElementUnit"],
  (.Objects[]
   | select(.ObjectElementName | test("ABC"))
   | [.ObjectElementName,.ObjectElementUnit] )
  | @csv' input.json

produces:

"ObjectElementName","ObjectElementUnit"
"Test ABC 1","2"
"Test ABC 2","5"

However, if you're willing to take the risks, you could replace @csv by join(","), which in the present case would result in:

ObjectElementName,ObjectElementUnit
Test ABC 1,2
Test ABC 2,5

edited Nov 22, 2019 at 15:17

answered Nov 22, 2019 at 3:05

peak

119k21 gold badges185 silver badges218 bronze badges

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

2 Comments

seafre Over a year ago

Thank you, confirmed this works, but is there any way to keep the header row? For example: ObjectElementName,ObjectElementUnit with subsequent rows as.... "Test ABC 1","2"

seafre Over a year ago

Perfect :) Solved

Collectives™ on Stack Overflow

Use JQ to parse JSON array of objects, using select to match specified key-value in the object element, then convert to CSV

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related