The use of Custom JSON field on the STEEM Blockchain is becoming more and more popular. From discussion I have read, this field can do almost anything from creating Tokens to holding game information.
JSON data is a way of representing objects or arrays. It is easy to read, and it is easy to parse. Many API calls will return JSON format and many web apps use JSON to easily move information around the internet.
Its is therefore no surprise that developers are now using the JSON filed on steem to move data between an app and the blockchain.
Initially the Custom JSON field was used for holding follow information, where one account on the blockchain follows another. However, over time, this has expanded to holding information from multiple sources for multiple reasons.
With that in mind I wanted to take a quick look at the Txcustoms table in the STEEMSQL database to just get an idea of what and who is using this JSON field. This type of analysis is commonly called exploratory analysis.
Repository
https://github.com/steemit/steem
Aim of Exploratory Analysis
- Get a feel for the Txcustoms table in STEEMSQL.
- Establish if there was growth in the use of this field from 2018 to 2019
- Establish what app is using this most
- Get an idea of the type of data held
Analysis
The table below shows the custom JSONs for the first week of January 2018. None of the Custom JSONs required additional posting or other authorities. As we can see, most of the transitions using this field were Follows, however there were a few others too.
Pacman-live seems to be one of the first games to host data on the blockchain. Looking at this further the data sent to the blockchain was the username, the score, the game level and a timestamp.
Chainbb seem to have been using the JSON field to store details of Forum objects, such as forum configurations and forum posts.
Distribution is an interesting one and seems to be holding information about distributions from randowhale
And we can also see some testing of STEEMMonsters under TEST_sm_pre_sale_packs
Jumping on now to the first week of January 2019 and things are looking rather different. 980162 custom JSON fields were used, down 58% on the same time last year. However, this is not bad news because in 2018 99.8% of these related to follows and unfollows. With the downturn in activity on the blockchain, for the same period in 2019 follows and unfollows make up only 9.8% of the custom JSON transactions. The was a reduction of 95% in the actual number of follows and unfollows.
The table above shows the number of transitions of each time with custom JSONS. I have split this list in two. On the right we have all the STEEMMonsters custom JSONS. This makes up 88% of all custom JSONs. On the left we have others such as Follows, DiceHash and Dlux.
The table below shows a sample of the data contained within some of the JSONs.
Also changed considerably since 2018 is the user of authority to post and this is also recorded in the blockchain and the SQL database. Looking at this for STEEMMonsters in the table below, by doing a count of the required_posting_auths we can see that for this period there were 366,192 find match and by carrying out a distinct count of the same field, we can establish this was by 2379 different accounts.
Conclusion
Although there was a reduction in the number of custom JSONs used from Jan 18 to Jan 19, this is a reflection on the downturn in social activity on the block and not development activity.
The social activity, follows and unfollows is down 95%. However, in 2018 non-social activity recorded in JSON was only 0.02%, in 2018 this was 90.2%. This is an indication of development on the block and a sign in the shift of the platform from being a content/social platform to something more.
By looking at the data contained within the JSONs my eyes have been opened on how simple it can be to really make ‘ANY’ app a DApp on the STEEM Blockchain and I hope it gives you that sense too.
Obviously STEEMMonsters are well out there in terms of usage compared to other apps using JSONs but many apps are only being to test this feature. Some of this comes from the shift away from Steemit.Inc as the only developers on the block. STEEM is coded in C++ which is a skill many do not have. However, there is no shortage of App developers with JSON knowledge.
Now that I have a better understanding of how this table in the SQL database is kept and what data it contains, it opens up a new world of analysis.
So, who will be first to have a D+ site ready to replace G+ communities using our new friend JSON?
Data and Query
The data for this post was taken from the STEEMSQL database held and managed by 
The query used for 2019 was
select *from Txcustomswhere CONVERT(DATE,timestamp) BETWEEN '2019-01-01' AND '2019-01-07'and for 2018 was
select *from Txcustomswhere CONVERT(DATE,timestamp) BETWEEN '2018-01-01' AND '2018-01-07'As always, I used PowerBI to
- Connect to the data
- Transform the data
- Carry out calculations
- Visualise the data
If you want to learn more about using PowerBI or Excel do follow
