Process JSON data using `jq`

JSON is a widely used exchange format and we have many opportunities to process JSON data in daily work.

jq is a flexible and popular command-line JSON processor. It's very helpful to transform JSON data.

Here are my practical examples using jq to process JSON data.

The example JSON data (input.json) is like:

[
  {
    "id": "0c1160b4-42c9-4aee-8287-68b0cf03ebda",
    "billingId": "54bdc967-0132-4cf2-9a9e-67d0230f7cce",
    "requestBy": "6b303448-4010-4ab8-a3dc-30bbd4145475",
    "paymentType": "DIRECT_DEBIT",
    "createdAt": "2024-06-11T23:22:54.777Z",
    "refundAmount": 29.9,
    "refundDate": "2024-05-30",
    "updatedAt": "2024-06-11T23:22:54.777Z"
  },
  {
    "id": "4fa36c92-a1c1-44be-bd5e-f0697fd91420",
    "billingId": "4f13d5f9-d0d9-470f-8e98-d5c6a37ac428",
    "requestBy": "6b303448-4010-4ab8-a3dc-30bbd4145475",
    "paymentType": "CREDIT_CARD",
    "createdAt": "2024-06-11T23:22:54.777Z",
    "memberId": "52dd5dfc-82d2-526f-b3ef-f0df008eb37a",
    "refundAmount": 13.0,
    "refundDate": "2024-05-30",
    "updatedAt": "2024-06-11T23:22:54.777Z"
  },
  {
    "id": "3ff9266a-18f6-4473-ac9c-ac99d28c608e",
    "billingId": "4823b60d-79ca-411b-a993-9d9b3d4f2350",
    "requestBy": "6b303448-4010-4ab8-a3dc-30bbd4145475",
    "paymentType": "CREDIT_CARD",
    "createdAt": "2024-05-11T23:22:54.777Z",
    "memberId": "9f993c23-e3ab-4a65-93ed-5fe4c343843c",
    "refundAmount": 38.0,
    "refundDate": "2024-06-30",
    "updatedAt": "2024-06-28T23:22:54.777Z"
  }
]

Task 1: extract and rename fields

• extract only two fields id and billingId
• rename billingId to id, id to refundTransactionId.

The expected result is:

[
  {
    "id": "54bdc967-0132-4cf2-9a9e-67d0230f7cce",
    "refundTransactionId": "0c1160b4-42c9-4aee-8287-68b0cf03ebda"
  },
  {
    "id": "4f13d5f9-d0d9-470f-8e98-d5c6a37ac428",
    "refundTransactionId": "4fa36c92-a1c1-44be-bd5e-f0697fd91420"
  },
  {
    "id": "4823b60d-79ca-411b-a993-9d9b3d4f2350",
    "refundTransactionId": "3ff9266a-18f6-4473-ac9c-ac99d28c608e"
  }
]

The command is

cat input.json | jq '[.[] | {id: .billingId, refundTransactionId: .id}]'

Task 2: filter and extract fields

• find all records with paymentType = DIRECT_DEBIT
• only select fields: id, billingId, refundAmount

The expected result is:

[
  {
    "id": "0c1160b4-42c9-4aee-8287-68b0cf03ebda",
    "billingId": "54bdc967-0132-4cf2-9a9e-67d0230f7cce",
    "refundAmount": 29.9
  }
]

The command is:

jq '[.[] | select(.paymentType == "DIRECT_DEBIT") | {id, billingId, refundAmount}]' input.json

Task 3: filter and sum

• find all records with paymentType = CREDIT_CARD
• get the sum of refundAmount

The expected result is: 51.

The command is:

jq '[.[] | select(.paymentType == "CREDIT_CARD")] | map(.refundAmount | tonumber) | add' input.json

References

• jq Tutorial