JSON API becomes very popular nowadays so here is a little help of how to parse complex JSON files.
- Always run your JSON parser in Big5 sandbox first before applying to any Big5 profiles. This will save you some disappointments and possibly some mess in HS3. You need to wrap it in ${…..} for the sandbox and remove the wrap for use in the Big5 profile.
- Always validate your JSON. Online tools are available for that. Missing a single ” may wreck havoc.
- Determine if your JSON file contains lists. They are in square brackets. [ ..]
- You may use this public API for practicing
https://randomuser.me/api - The examples below are based on JSON obtained from the above link. The sample input is shown below.
- To parse a string use
JSON (Input, “element1.element2…”)
Example: ${JSON (Input, “info.seed”)} will produce a string
“a3b2469bb5c8d443” (see the sample JSON below bottom part)
7. To parse a number use
JSON_Num (Input, “element1.element2,…)
Example: ${JSON_Num(Input, “info.page”) } will result in number 1 (see Input JSON sample below).
The difference between string and number is that you can use the numbers in math operations.
Example:
${JSON_Num(Input, “info.page”) +54} will result in number 55
8. To parse an element of a list specify the the number of the element in [ ] starting from 0 for the first element. So the number you use will be one less than the actual number as 0 is used for the first element. To address element 23 you will use [22].
Example:
${JSON (Input, “results[1].name.first”)}
will produce the string “”gina”
9. Watch for the level of nesting. In the above example name must be used before first. It won’t work if you try to address first directly like that
${JSON (Input, “results[1].first”)} – THIS IS WRONG
Example:
${JSON (Input, “results[1].location.coordinates,lattitude”)} will produce a result “86.0001”
For exercise: Write a parser to retrieve the e-mail address of Mr. Storm Olsen from the sample JSON below.
Sample JSON used for the examples retrieved from randomuser.me/api
{
“results”: [
{
“gender”: “male”,
“name”: {
“title”: “mr”,
“first”: “storm”,
“last”: “olsen”
},
“location”: {
“street”: “7860 helgesvej”,
“city”: “sønder stenderup”,
“state”: “nordjylland”,
“postcode”: 95545,
“coordinates”: {
“latitude”: “-32.4944”,
“longitude”: “-115.1097”
},
“timezone”: {
“offset”: “-6:00”,
“description”: “Central Time (US & Canada), Mexico City”
}
},
“email”: “storm.olsen@example.com”,
“login”: {
“uuid”: “ea3c4d9f-d236-4332-89a8-20d01f8f00a2”,
“username”: “redleopard233”,
“password”: “mememe”,
“salt”: “3kpzppkF”,
“md5”: “ecbe1a8e0c3f95663fcace90362d8069”,
“sha1”: “11492b77fc7a8975934a3c52bff2580307dbd122”,
“sha256”: “8ea2ca40e52dea4a1ff900a2ba8dca6ea17bbd8df46d2550589bd15ac652d65f”
},
“dob”: {
“date”: “1945-12-04T07:30:06Z”,
“age”: 73
},
“registered”: {
“date”: “2016-03-08T21:21:17Z”,
“age”: 3
},
“phone”: “09590061”,
“cell”: “76063349”,
“id”: {
“name”: “CPR”,
“value”: “751069-3269”
},
“picture”: {
“large”: “https://randomuser.me/api/portraits/men/6.jpg”,
“medium”: “https://randomuser.me/api/portraits/med/men/6.jpg”,
“thumbnail”: “https://randomuser.me/api/portraits/thumb/men/6.jpg”
},
“nat”: “DK”
},{
“gender”: “female”,
“name”: {
“title”: “mrs”,
“first”: “gina”,
“last”: “reid”
},
“location”: {
“street”: “8006 mcgowen st”,
“city”: “roseville”,
“state”: “minnesota”,
“postcode”: 47444,
“coordinates”: {
“latitude”: “86.0001”,
“longitude”: “172.9285”
},
“timezone”: {
“offset”: “-8:00”,
“description”: “Pacific Time (US & Canada)”
}
},
“email”: “gina.reid@example.com”,
“login”: {
“uuid”: “5b82c7c7-622f-480b-99e9-9580645e38d4”,
“username”: “organicmouse406”,
“password”: “tequila”,
“salt”: “ji7IaShK”,
“md5”: “e321c82c15edda62f970885f08f95256”,
“sha1”: “f9973d2921ffd32bfa09a0a678395182c4a15c61”,
“sha256”: “dbe14d3206256b55cd4f70fd726f3aa610659b296cb9c38fdaea1cc3a4dfd773”
},
“dob”: {
“date”: “1973-05-14T00:22:51Z”,
“age”: 46
},
“registered”: {
“date”: “2003-03-04T06:10:35Z”,
“age”: 16
},
“phone”: “(238)-258-2117”,
“cell”: “(039)-682-5081”,
“id”: {
“name”: “SSN”,
“value”: “579-77-9622”
},
“picture”: {
“large”: “https://randomuser.me/api/portraits/women/41.jpg”,
“medium”: “https://randomuser.me/api/portraits/med/women/41.jpg”,
“thumbnail”: “https://randomuser.me/api/portraits/thumb/women/41.jpg”
},
“nat”: “US”
}
],
“info”: {
“seed”: “a3b2469bb5c8d443”,
“results”: 1,
“page”: 1,
“version”: “1.2”
}
}
10. Special characters
If your JSON Expression doesn’t work because of some special characters like “.” for example you need to escape the special characters like that
Example:
Replace:
JSON(input,”pm2.5″)
By
JSON(input,”[‘pm2.5’]”)