OPENJSON - The Utility function you did not know you needed in a Data transfer Logic App

OPENJSON - The Utility function you did not know you needed in a Data transfer Logic App

Overview

Background

I have been using Logic Apps to transfer data from one database to another. Of course, there is business logic in the queries run on the source database. And in such a case, maybe Azure Data Factory might have been a better choice. It very well could be. But for now I have been using a logic app, that simply runs a pretty big query, fetches the results and inserts into my destination database after some checks on the data present in the destination database. It works.

If it works, don't touch it
However, using your Logic App to do some data related operations, especially to a SQL database, requires some wisdom.

What are Logic Apps again?

In my last post, I gave an introduction into Azure Logic Apps in general and walked through an example of a logic app, deployed through Azure Devops Pipelines.

So what is the problem then?

In a Logic App, you can access the output of an earlier action using something called a Dynamic Content. This is like a little overlay that appears on every action where something can be used from another action, usually a predecessor action.

Azure Logic Apps - Dynamic Content Overlay
The image, shows that something called ResultSets RegistrationCode is accessible in this block. Now when you try to use that it appears like a weird icon in your SQL code.
Azure Logic Apps - Dynamic Content in Action
It is neither code nor an image! It is a button amongst code!
Totally smoke out of my brains!

When I write code, I prefer interacting with code and not boxes and shapes. So this was really awkward. Now you might think differently but I have my reasons. What are they?

  1. It does not clearly state what that thing is - no description or tooltip or help anywhere
  2. It does not tell the user how to use or extract data from it or even the fact that there is data that can be retrieved from it

Is it really that bad?

I am glad that I am not the first person to try out Azure Logic Apps for the purpose of running some SQL queries on one database and inserting it into another. I had some other blogs actually mention how to do this in a series of steps, which was invaluable in learning how to get started. Else I would never have realised that one would consume data produced from one action in another one using this weird graphical button. Alright. So now you know that the button like thing, the so called Dynamic Content is actually stuff that has data in it, how do you consume it?

That is where you need OPENJSON

Data interchange in Logic Apps

Data from one action to another in a Logic App is passed in JSON format. Well, this is something you should have guessed if you had thought about it. They are little web applications after all. An example output from an action in JSON looks like:

 1{
 2    "statusCode":200,
 3    "headers": {
 4        "Pragma":"no-cache",
 5        "Transfer-Encoding":"chunked",
 6        "Vary":"Accept-Encoding",
 7        "x-ms-request-id":"6a626fb0-d7b0-4a16-a837-2b707f3e2961",
 8        "Strict-Transport-Security":"max-age=31536000; includeSubDomains",
 9        "X-Content-Type-Options":"nosniff",
10        "X-Frame-Options":"DENY",
11        "Timing-Allow-Origin":"*",
12        "x-ms-apihub-cached-response":"true",
13        "Cache-Control":"no-store, no-cache",
14        "Date":"Mon, 21 Jun 2021 16:11:57 GMT",
15        "Content-Type":"application/json; charset=utf-8; odata.metadata=minimal",
16        "Expires":"-1","Content-Length":"13661146"
17        },
18    "body": {
19        "ResultSets": {
20            "Table1": [
21                {
22                    "FirstName": "Ind",
23                    "PhoneNumber": 654321987,
24                    "Surname": "Ian",
25                    "DateOfBirth": "2021-05-20"
26                },
27                {
28                    "FirstName": "Bri",
29                    "PhoneNumber": 987654321,
30                    "Surname": "Tish",
31                    "DateOfBirth": "2021-04-20"
32                }
33            ]
34        }
35    }
36}

The body property holds the data. ResultSets.Table1 represents the rows of data!

This was something I found out after I ran into issues.

Azure Logic Apps - Bad request error
This is priceless when debugging and trying to understand why your subsequent action is unable to process data from the previous step!

An example error can be seen here

Azure Logic Apps - 400 cannot insert null

I spent a good deal of time trying to investigate where the null was coming from. The data always had values. So where was this from?

The culprit

As mentioned earlier, data from a previous action is accessed using a special object. But it is our responsibility to parse it correctly.

When I got data in my action2 from action 1, I stored it in a temporary varchar(max) variable named @listOfPeopleImported.

I then declare a table variable to suitably insert the imported data after parsing it out of the JSON format. This is where knowing how to use OPENJSON in SQL SERVER comes in handy.

What is OPENJSON

It is a table valued function. It creates a relational view of JSON data. It takes in a JSON document as an argument, parses it and returns the objects and properties in a tabular format! Brilliant stuff! From JSON to table in a simple function call.

Giphy - pretty cool

Enough talk, show me examples

1select * from OPENJSON('["Name", "Eakan", "Surname", "Gopalakrishnan"]')

That was an array in javascript notation. The result would look like:

Azure Logic Apps - Select array screenshot

This is the default representation of stuff returned by OPENJSON.

The three columns named key, value and type.

  • Key: name of the property or the index of the property
  • Value: the value of the property
  • Type: The JSON datatype - 0-5

what are types?

Generally JSON data types are the following:

  • 0 - null
  • 1 - string
  • 2 - number
  • 3 - bool
  • 4 - array
  • 5 - object

This numbered set of types are not really something you would know if you only knew JSON. This numbering is specific to OPENJSON.

And knowing this was not the most important thing for me. But knowing how to consume the data in SQL was priceless.

Okay, enough hype! How do you consume data?

Example json:

 1declare @json NVARCHAR(400) = N'{
 2    "body": {
 3        "ResultSets": {
 4            "Table1": [
 5                {
 6                    "FirstName": "Ind",
 7                    "PhoneNumber": 654321987,
 8                    "Surname": "Ian",
 9                },
10                {
11                    "FirstName": "Bri",
12                    "PhoneNumber": 987654321,
13                    "Surname": "Tish",
14                }
15            ]
16        }
17    }';
18select * from OPENJSON(@json, '$.body.ResultSets.Table1')

by default it would print something like:

keyvaluetype
0{ "FirstName" : "Ind", "PhoneNumber" : 654321987, "Surname" : "Ian" }5
1{ "FirstName" : "Bri", "PhoneNumber" : 987654321, "Surname" : "Tish" }5

That is cool but I need the object transformed into a table. In other words, property names must be column names and rows must be the values. How do I do that?

Transforming data using a schema

You are in luck. That is also possible using a Schema.

 1declare @json NVARCHAR(400) = N'{
 2    "body": {
 3        "ResultSets": {
 4            "Table1": [
 5                {
 6                    "FirstName": "Ind",
 7                    "PhoneNumber": 654321987,
 8                    "Surname": "Ian",
 9                },
10                {
11                    "FirstName": "Bri",
12                    "PhoneNumber": 987654321,
13                    "Surname": "Tish",
14                }
15            ]
16        }
17    }';
18select * from OPENJSON(@json, '$.body.ResultSets.Table1')
19WITH (
20    [FirstName] varchar(15) '$.FirstName',
21    [PhoneNumber] number '$.PhoneNumber',
22    [Surname] varchar(15) '$.Surname'
23)
FirstNamePhoneNumberSurname
Ind654321987Ian
Bri987654321Tish
oh wow!

That is how you extract OPENJSON data from the Dynamic Content generated from an earlier action into a subsequent action in an Azure Logic App!

comments powered by Disqus