How to Parse the JSON fields in the SteemSQL database

I recently did some analysis into the locations given by Steemit users on their profile (link to the post). I decided to write up a guide on how I parsed the JSON after @brianjuice commented that he was trying to learn how to do it too.

When you query the MSSQL SteemSQL database for the profile metadata using:

SELECT json_metadata
FROM [dbo].[accounts] 
WHERE name = 'digimad'

You get this result:

"{"profile":{"profile_image":"","name":"Digimad","about":"Digital Nomad from the UK, now living in South America","location":"World","website":"http://steemforthe.win"}}"

Not exactly the easiest to work with in SQL!

How to Parse this JSON data into something useful?

I wanted to extract the location data. Since the release of SQL Server 2016, there are now specific JSON data functions. This page from Microsoft explains how to use these functions, along with examples:

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

If you are using a tool like SQL Server Management Studio these could be useful. I didn't do it this way.

How I did it

I found another way to do it if you just want a single field, which also lets you do something similar if you are querying from Excel. Since the format is straight forward you can just use a string lookup for the field you want, in my case 'location'.

SELECT id
      , name
      , substring(substring(json_metadata,charindex('location',json_metadata)+13,100),0,charindex('\',substring(json_metadata,charindex('location',json_metadata)+13,100)))  as location_string
      , created 
FROM [dbo].[accounts] 
WHERE json_metadata like '%location%'
AND name = 'digimad'

This just looks up the location of the word 'location', adds in the number of characters for location":\ " and then finds the number of characters until the next field starts. The location in the where statement is to stop unnecessary results - what's the point of returning a row if it doesn't have a location in the JSON at all?

It gives a much more manageable result:

If you were doing this in excel you could use similar substring functions.