One of the most powerful ways to modify data is to use the tool jq.
With jq you can process Rest API data directly after the endpoint responds with data - as you can modify the JSON.
Similar to other text-editing tools in unix are used to edit text on the fly (awk, sed etc) the jq utility allows you to edit JSON objects.
Within PixieBrix jq can be used to achieve the same goal, edit data from one brick before pushing it to another one.
If you prefer a video walkthrough to go with the guide, please watch it here:
On PixieBrix you can run HTTP requests using the HTTP brick - this is how it works:
Find the HTTP Request brick inside the PixieBrix editor - it is called HTTP Request
Given the following API endpoint I retrieved some data from the Formula 1 championship https://ergast.com/api/f1/2022/2/drivers.json
When I use PixieBrix to do an HTTP Request I would structure the brick to look like this
Notice url is set to be the API endpoint
There is no service set for this because this API does not require authentication
I picked the method from the dropdown, using GET I can call the API and retrieve its data.
I do not have any parameters, headers or data to pass to the endpoint so I am leaving these endpoints open.
I will be using this API endpoint’s data for the below examples.
Input:
[{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai"
}]
jq Expression: . + [ {driverId: "pixie", permanentNumber: "99", code: "PXB", url: "<https://pixiebrix.com>", givenName: "Pixie", familyName: "Brix", dateOfBirth: "2020-01-01", nationality: "American"} ]
Output:
[
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai"
},
{
"driverId": "pixie",
"permanentNumber": "99",
"code": "PXB",
"url": "<https://pixiebrix.com>",
"givenName": "Pixie",
"familyName": "Brix",
"dateOfBirth": "2020-01-01",
"nationality": "American"
}
]
🧠 Explanation: I first grab the array and then I add an object within an array to the initial array - jq merges the two arrays together with the output below
Input:
[
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai"
},
{
"driverId": "pixie",
"permanentNumber": "99",
"code": "PXB",
"url": "<https://pixiebrix.com>",
"givenName": "Pixie",
"familyName": "Brix",
"dateOfBirth": "2020-01-01",
"nationality": "American"
}
]
jq Expression: .[1:2]
Output:
[
{
"driverId": "pixie",
"permanentNumber": "99",
"code": "PXB",
"url": "<https://pixiebrix.com>",
"givenName": "Pixie",
"familyName": "Brix",
"dateOfBirth": "2020-01-01",
"nationality": "American"
}
]
🧠 Explanation: I can use the jq dot notation to reference the array and then extract the item placed between index 1 and 2
Input:
[
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai"
},
{
"driverId": "pixie",
"permanentNumber": "99",
"code": "PXB",
"url": "<https://pixiebrix.com>",
"givenName": "Pixie",
"familyName": "Brix",
"dateOfBirth": "2020-01-01",
"nationality": "American"
}
]
jq Expression: .[1]
Output:
{
"driverId": "pixie",
"permanentNumber": "99",
"code": "PXB",
"url": "<https://pixiebrix.com>",
"givenName": "Pixie",
"familyName": "Brix",
"dateOfBirth": "2020-01-01",
"nationality": "American"
}
🧠 Explanation: This is similar to the previous, I use the dot notation to reference the array and I extract the item encountered at position 1 - because arrays are 0-indexed in jq
Input
[
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai"
},
{
"driverId": "pixie",
"permanentNumber": "99",
"code": "PXB",
"url": "<https://pixiebrix.com>",
"givenName": "Pixie",
"familyName": "Brix",
"dateOfBirth": "2020-01-01",
"nationality": "American"
}
]
jq Expression: .[] | del(.code) | [.]
Output
[
{
"driverId": "albon",
"permanentNumber": "23",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai"
}
]
[
{
"driverId": "pixie",
"permanentNumber": "99",
"url": "<https://pixiebrix.com>",
"givenName": "Pixie",
"familyName": "Brix",
"dateOfBirth": "2020-01-01",
"nationality": "American"
}
]
🧠 Explanation: I first grab the data and extract the two objects - then I tell jq to delete the key called “code” and then I restructure the data to be an array once again
Input:
[
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai"
}
]
jq Expression: [.[0] + { infractions: 3}]
Output:
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai",
"infractions": 3
}
🧠 Explanation: Take the data and extract it to an object from the array. I then tell jq to add an object. This will merge the two objects together. The result is having added the object as a key:value pair to the exiting object
Input
{
"Drivers": [
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai"
},
{
"driverId": "alonso",
"permanentNumber": "14",
"code": "ALO",
"url": "<http://en.wikipedia.org/wiki/Fernando_Alonso>",
"givenName": "Fernando",
"familyName": "Alonso",
"dateOfBirth": "1981-07-29",
"nationality": "Spanish"
},
{
"driverId": "bottas",
"permanentNumber": "77",
"code": "BOT",
"url": "<http://en.wikipedia.org/wiki/Valtteri_Bottas>",
"givenName": "Valtteri",
"familyName": "Bottas",
"dateOfBirth": "1989-08-28",
"nationality": "Finnish"
}
]
}
jq Expression: .Drivers | {Drivers: map(. + { wheelset: "Bridgestone"}) }
Output
{
"Drivers": [
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai",
"wheelset": "Bridgestone"
},
{
"driverId": "alonso",
"permanentNumber": "14",
"code": "ALO",
"url": "<http://en.wikipedia.org/wiki/Fernando_Alonso>",
"givenName": "Fernando",
"familyName": "Alonso",
"dateOfBirth": "1981-07-29",
"nationality": "Spanish",
"wheelset": "Bridgestone"
},
{
"driverId": "bottas",
"permanentNumber": "77",
"code": "BOT",
"url": "<http://en.wikipedia.org/wiki/Valtteri_Bottas>",
"givenName": "Valtteri",
"familyName": "Bottas",
"dateOfBirth": "1989-08-28",
"nationality": "Finnish",
"wheelset": "Bridgestone"
}
]
}
🧠 Explanation: This one uses an object that contains an array of objects, so I extract those into a large array - for every entry of the array I want to add an object - the wheelset object specified. This object gets added recursively using the map() function to every item of the array.
Input:
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai",
"wheelset": "Bridgestone"
}
jq Expression: . += { kids: [{"name":"Joe", "age": 12},{"name":"Alba", "age": 7}]}
Output:
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai",
"wheelset": "Bridgestone",
"kids": [
{
"name": "Joe",
"age": 12
},
{
"name": "Alba",
"age": 7
}
]
}
🧠 Explanation: Here I use jq to take the initial data and append more data to it. I use the += operator adding a complicated object with nested objects within an array.
Input
[
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23",
"nationality": "Thai"
},
{
"driverId": "alonso",
"permanentNumber": "14",
"code": "ALO",
"url": "<http://en.wikipedia.org/wiki/Fernando_Alonso>",
"givenName": "Fernando",
"familyName": "Alonso",
"dateOfBirth": "1981-07-29",
"nationality": "Spanish"
},
{
"driverId": "bottas",
"permanentNumber": "77",
"code": "BOT",
"url": "<http://en.wikipedia.org/wiki/Valtteri_Bottas>",
"givenName": "Valtteri",
"familyName": "Bottas",
"dateOfBirth": "1989-08-28",
"nationality": "Finnish"
}
]
jq Expression: map(. | del(.nationality))
Output
[
{
"driverId": "albon",
"permanentNumber": "23",
"code": "ALB",
"url": "<http://en.wikipedia.org/wiki/Alexander_Albon>",
"givenName": "Alexander",
"familyName": "Albon",
"dateOfBirth": "1996-03-23"
},
{
"driverId": "alonso",
"permanentNumber": "14",
"code": "ALO",
"url": "<http://en.wikipedia.org/wiki/Fernando_Alonso>",
"givenName": "Fernando",
"familyName": "Alonso",
"dateOfBirth": "1981-07-29"
},
{
"driverId": "bottas",
"permanentNumber": "77",
"code": "BOT",
"url": "<http://en.wikipedia.org/wiki/Valtteri_Bottas>",
"givenName": "Valtteri",
"familyName": "Bottas",
"dateOfBirth": "1989-08-28"
}
]
🧠 Explanation: Here we use jq to take the initial data and use the del() function to delete a nest value.
Input
[
{
"driverId": "max_verstappen",
"permanentNumber": "33",
"code": "VER",
"url": "<http://en.wikipedia.org/wiki/Max_Verstappen>",
"givenName": "Max",
"familyName": "Verstappen",
"dateOfBirth": "1997-09-30",
"nationality": "Dutch"
},
{
"driverId": "zhou",
"permanentNumber": "24",
"code": "ZHO",
"url": "<http://en.wikipedia.org/wiki/Guanyu_Zhou>",
"givenName": "Guanyu",
"familyName": "Zhou",
"dateOfBirth": "1999-05-30",
"nationality": "Chinese",
"kids": [
{
"name": "Joe",
"age": 12
},
{
"name": "Alba",
"age": 7
}
]
}
]
jq Expression: map((select(.code == "ZHO") |= del(.kids)))
Output
[
{
"driverId": "max_verstappen",
"permanentNumber": "33",
"code": "VER",
"url": "<http://en.wikipedia.org/wiki/Max_Verstappen>",
"givenName": "Max",
"familyName": "Verstappen",
"dateOfBirth": "1997-09-30",
"nationality": "Dutch"
},
{
"driverId": "zhou",
"permanentNumber": "24",
"code": "ZHO",
"url": "<http://en.wikipedia.org/wiki/Guanyu_Zhou>",
"givenName": "Guanyu",
"familyName": "Zhou",
"dateOfBirth": "1999-05-30",
"nationality": "Chinese"
}
]
🧠 Explanation: Here we use jq to filter an object based on the value of one of their key. Then we delete a key found inside this object
Input
[
{
"name": "Mary",
"score": "9/10"
},
{
"name": "Bob",
"score": "4/10"
},
{
"name": "Alex",
"score": "7/10"
}
]
jq Expression: map({ (.name): .score }) | add
Output
{
"Mary": "9/10",
"Bob": "4/10",
"Alex": "7/10"
}
Input:
{ "value":" the extraspace was here, the extra space is gone " }
jq Expression: .value |= gsub("^\\\\s+|\\\\s+$";"")
Output:
"the extraspace was here, the space is gone"
This blogpost has outlined how you can use jq to do clever data manipulation, let's keep building upon these learnings to become jq-fu masters 🥷