Guru: Parsing JSON That Has Spaces In The Key
July 8, 2024 Mike Larsen
One of my favorite tasks is working with APIs. I’ve worked with many APIs over the years, and some introduce unique challenges that must be overcome. Recently, I worked on a project where I was to consume a REST API that provided a JSON payload. While that seems straight forward, I quickly discovered a challenge.
The JSON payload had keys that have spaces in them (Figure 1). That may be considered bad practice, but I still needed to figure out how I could parse it correctly. Having never seen JSON like this before, I started with a Google search. I found some examples that were similar, but they were on different platforms and not something that was useful in my case. The JSON payload is shown below and I’ve changed the data elements so I don’t expose proprietary information.
Figure 1. JSON payload
{
"data": [{
"FirstName": "John",
"LastName": "Doe",
"City": "Paramus",
"State": "NJ",
"Zip code": "07082",
"Date of birth": "11/12/1952",
"Favorite color": "Blue"
},
{
"FirstName": "Jane",
"LastName": "Doe",
"City": "Milford",
"State": "PA",
"Zip code": "08596",
"Date of birth": "02/23/1945",
"Favorite color": "Red"
},
{
"FirstName": "Harry",
"LastName": "Henderson",
"City": "Palm Springs",
"State": "CA",
"Zip code": "90258",
"Date of birth": "05/25/1925",
"Favorite color": ""
},
{
"FirstName": "Juli",
"LastName": "Ambrose",
"City": "Austin",
"State": "TX",
"Zip code": "85247",
"Date of birth": "09/01/1985",
"Favorite color": ""
},
{
"FirstName": "Ron",
"LastName": "Nettles",
"City": "Virginia Beach",
"State": "VA",
"Zip code": "09874",
"Date of birth": "07/30/1971",
"Favorite color": "Orange"
},
{
"FirstName": "Betty",
"LastName": "Winn",
"City": "Rochester Hills",
"State": "MI",
"Zip code": "02547",
"Date of birth": "11/01/1990",
"Favorite color": "Brown"
}
]
}
Looking at the JSON payload, there are three keys that have embedded spaces (Zip code, Date of birth, and Favorite color). I use the SQL function, Json_table, to parse JSON payloads. Needing to figure this out on my own, I started trying different variations of code. After a few attempts, I found something that worked. The final code is shown below (Figure 2).
Figure 2. Json_table SQL code
Select
Coalesce(x.FirstName, ' ') as FirstName,
Coalesce(x.LastName, ' ') as LastName,
Coalesce(x.City, ' ') as City,
Coalesce(x.State, ' ') as State,
Coalesce(x.ZipCode, ' ') as ZipCode,
Coalesce(x.DateOfBirth, ' ') as DateOfBirth,
Coalesce(x.FavoriteColor, ' ') as FavoriteColor
from Json_table
( :variableWithJsonPayload
,
'$'
Columns
(Nested '$.data[*]'
Columns
(FirstName varchar(25) Path '$.FirstName',
LastName varchar(25) Path '$.LastName',
City varchar(25) Path '$.City',
State varchar(2) Path '$.State',
ZipCode varchar(10) Path '$."Zip code"',
DateOfBirth varchar(10) Path '$."Date of birth"',
FavoriteColor varchar(15) Path '$."Favorite color"'
)
)
) as x;
Note that I have the JSON payload populated into variable variableWithJsonPayload as I’m using the SQL function in an RPG program. The solution to parsing JSON that has spaces in the keys is to put quotes around the string that is defined in the path (see columns Zip code, Date of birth, and Favorite color). When I did that, the entire string was considered versus just the word up to the space. When I run the SQL statement, I get the desired results (Figure 3).
Figure 3. Result of parsed JSON payload
I’ve added this SQL statement to my toolbox as I’m sure I will encounter this situation again. Hopefully this can help save you time if you face the same challenge.
Mike Larsen is a director of information technology at Auburn Pharmaceutical and has been working with IBM i systems for over 20 years. He specializes in RPG, CL, and SQL and recently has been working with PHP and Python. Current projects have given Mike the opportunity to work with generating and parsing XML and JSON from SQL and consuming SOAP and REST web services. Although his main area of expertise is on IBM i, Mike has a passion for learning other languages and how he can integrate other platforms with IBM i.
RELATED STORIES
Guru: Partitioning Result Sets Using SQL
Guru: Comparing IFS Directories Using SQL
Guru: String Manipulation Using SQL
Guru: Regular Expressions, Part 1
Guru: Regular Expressions, Part 2
Guru: Debugging SQL Stored Procedures With ACS
