Here’s a quick (or maybe not so quick?) tip for using SAP Data Services to extract nested JSON data from a column in a raw data file.

With the huge increase in unstructured data being generated from multiple systems, like Customer Information Systems (CIS) it’s getting more common that some, if not all, of the data interchange is in JSON format.

Newer databases, such as SAP Hana or Google BigQuery can handle JSON quite easily, but this can be a challenge for traditional databases to parse and load. By using a cool feature of Data Services, we can map the JSON fields, and “unpack” them into a structured table that these traditional databases prefer. Here’s a relatively simple example.

Creating a XML Schema File Format

Firstly, we need to create a “template” for the Data Services function extract_from_json by using XML Schema File Format.

Take a single record of JSON data that has the full structure and convert that JSON into XML then place that into a .xsd file (XML Schema file). There are free sites online to do this conversion for you.

*For JSON to XML Conversion:

*For XSD Schema generator:

Now, open Data Services, and under Formats – Nested Schema’s, create a new XML Schema file as shown below:

Now provide the details for the XML Schema you created:

Now we’re ready to use the function.

Using the extract_from_json function in a Dataflow

Pull in your source table or flat file, connect this to a Query Transform and map all of your columns to the output. Right-click the column that has the JSON data and click New Function Call and select extract_from_json:

For JSON field name select the field that contains the JSON Data which in this case is JSON. Then for Schema Name select the XML Schema file format that you created earlier. For Enable validation select 0 if you do not want validation to occur at runtime of the job or select 1 if you do want validation to occur then click next.

Now, choose which fields you want mapped to the output (in this case we mapped all fields to the output) and then click Finish.

Now, as we are going to load this data into a table, we will need to use the “Unnest” option. To do this you will need to create an additional Query Transform:

Map all necessary fields to the output and then right click and select unnest for all of the parent fields, in this example do this for details and inventory.

Create a Template Table and connect the second Query Transform to the Template Table. Click Validate All to make sure that there are no errors.

Run the job and then check that the JSON data loaded correctly in the table:

As you will see, we’ve “unpacked” the JSON from a single column and flattened it into a structured table with multiple columns that we can now integrate into the rest of the data model for future analysis.

Read More in these other tricks and tips articles

Read More about EIM in these recent articles

increase your knowledge

See more Business Intelligence insights or get future articles sent right to your inbox

increase your knowledge