Filtering and find with jq

Robin Michael -

Debugging a JavaScript Object Notation (JSON) response and wanted to filter and find a specific case and then convert the matches into a Comma Separated Values  (CSV) response for others to work with in a spreadsheet.  Need to quickly push this out with little or no work and well that is the perfect case for the jq utility

Sample data

{
  "brands": [
    {
      "name": "DEFAULT",
      "displayNameState": "REVIEW_STATE_UNSPECIFIED",
      "iconState": "REVIEW_STATE_UNSPECIFIED",
      "propertyCount": 0
    },
    {
      "name": "Company1",
      "displayNameState": "REVIEW_STATE_UNSPECIFIED",
      "iconState": "REVIEW_STATE_UNSPECIFIED",
      "propertyCount": 2
    },
    {
      "name": "Company2",
      "submittedDisplayNames": [
        {
          "text": "Company 2 Title",
          "languageCode": "en-US"
        }
      ],
      "displayNameState": "REVIEW_STATE_NEW",
      "submittedIcon": "company2.png",
      "iconState": "REVIEW_STATE_NEW",
      "propertyCount": 10
    },
    {
      "name": "Company3",
      "submittedDisplayNames": [
        {
          "text": "Company 3 Title",
          "languageCode": "en-US"
        }
      ],
      "displayNameState": "REVIEW_STATE_NEW",
      "iconState": "REVIEW_STATE_UNSPECIFIED",
      "propertyCount": 0
    },
    {
      "name": "Company4",
      "submittedDisplayNames": [
        {
          "text": "Company 4 Title",
          "languageCode": "en-US"
        }
      ],
      "displayNameState": "REVIEW_STATE_NEW",
      "iconState": "REVIEW_STATE_UNSPECIFIED",
      "propertyCount": 7
    }
  ]
}

Goal 1: Find all the brands that are in REVIEW_STATE_NEW

  • Use jq to reference the brands -> .brands[]
  • Filter that data by using the select operator on the field displayNameState combined with the contains for text searching .brands[] | select( .displayNameState | contains("REVIEW_STATE_NEW")
  • Select the fields into a single field and pipe the output as csv format | [.name, .displayNameState, .iconState, .propertyCount] | @csv
  • Make sure to use the -r command on jq to get the raw format otherwise you get  \" everywhere

All together

cat brands.json | jq -r '.brands[] | select( .displayNameState | contains("REVIEW_STATE_NEW")) | [ .name, .displayNameState, .iconState, .propertyCount] | @csv'

Results

"Company2","REVIEW_STATE_NEW","REVIEW_STATE_NEW",10
"Company3","REVIEW_STATE_NEW","REVIEW_STATE_UNSPECIFIED",0
"Company4","REVIEW_STATE_NEW","REVIEW_STATE_UNSPECIFIED",7

Goal 2: Find all the brands that have 0 properties

  • Use jq to reference the brands -> .brands[]
  • Filter that data by using the select operator on the field displayNameState combined with the contains for text searching  .brands[] | select( .propertyCount == 0)
  • Select the fields into a single field and pipe the output as csv format | [.name, .displayNameState, .iconState, .propertyCount] | @csv
  • Make sure to use the -r command on jq to get the raw format otherwise you get  \" everywhere

All together

 cat brands.json | jq -r '.brands[] | select( .propertyCount == 0) | [.name, .displayNameState, .iconState, .propertyCount] | @csv'

Results

"DEFAULT","REVIEW_STATE_UNSPECIFIED","REVIEW_STATE_UNSPECIFIED",0
"Company3","REVIEW_STATE_NEW","REVIEW_STATE_UNSPECIFIED",0