In this blog post, I would like to share an alternative way of working with JSON, especially with huge json data.
As we all experience, JSON is the most popular standard when it comes to integration, communicating to 3rd party systems. And SAP finally has good libraries to work with json(/ui2/cl_json) . But before that we need to use non-standard sap libraries for parsing, such as zJson. zJson is slower than /ui2/cl_json. Still, I thank to zJson developers for providing an option.
/ui2/cl_json is good to transform between DDIC and Json. But what you would do if you need to query json as you would query a database table? This blog post will be about that. We will see how to query a json document in MSSQL and how to use MSSQL's this ability in abap.
My scenario is, CAD software provides mbom data in json format and that is a huge file. PO converts that Json document to xml, parses that document and Sap consumes that data parts with inbound proxy calls. SAP Abap code gets data from inbound proxy and stores them in tables. Afterwards whole different data sets are processed. But Abap layer does not see original json document. That document is not stored in a database, in case if you want to compare differences between documents. And I thought, It would be great if we could store that json in a database and query it quickly directly from Abap :).
That is all. As you can see, we can harness power of MSSQL database and combine it with Abap. In that way instead of normalizing json data in Z tables, we can store them in database tables as json documents and query it like querying and database table.
I hope that post was helpful for you and gave you an idea.