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

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.

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.

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:

{
    "statusCode":200,
    "headers": {
        "Pragma":"no-cache",
        "Transfer-Encoding":"chunked",
        "Vary":"Accept-Encoding",
        "x-ms-request-id":"6a626fb0-d7b0-4a16-a837-2b707f3e2961",
        "Strict-Transport-Security":"max-age=31536000; includeSubDomains",
        "X-Content-Type-Options":"nosniff",
        "X-Frame-Options":"DENY",
        "Timing-Allow-Origin":"*",
        "x-ms-apihub-cached-response":"true",
        "Cache-Control":"no-store, no-cache",
        "Date":"Mon, 21 Jun 2021 16:11:57 GMT",
        "Content-Type":"application/json; charset=utf-8; odata.metadata=minimal",
        "Expires":"-1","Content-Length":"13661146"
        },
    "body": {
        "ResultSets": {
            "Table1": [
                {
                    "FirstName": "Ind",
                    "PhoneNumber": 654321987,
                    "Surname": "Ian",
                    "DateOfBirth": "2021-05-20"
                },
                {
                    "FirstName": "Bri",
                    "PhoneNumber": 987654321,
                    "Surname": "Tish",
                    "DateOfBirth": "2021-04-20"
                }
            ]
        }
    }
}

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

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

An example error can be seen here

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.

Enough talk, show me examples

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

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

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:

declare @json NVARCHAR(400) = N'{
    "body": {
        "ResultSets": {
            "Table1": [
                {
                    "FirstName": "Ind",
                    "PhoneNumber": 654321987,
                    "Surname": "Ian",
                },
                {
                    "FirstName": "Bri",
                    "PhoneNumber": 987654321,
                    "Surname": "Tish",
                }
            ]
        }
    }';
select * from OPENJSON(@json, '$.body.ResultSets.Table1')

by default it would print something like:

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.

declare @json NVARCHAR(400) = N'{
    "body": {
        "ResultSets": {
            "Table1": [
                {
                    "FirstName": "Ind",
                    "PhoneNumber": 654321987,
                    "Surname": "Ian",
                },
                {
                    "FirstName": "Bri",
                    "PhoneNumber": 987654321,
                    "Surname": "Tish",
                }
            ]
        }
    }';
select * from OPENJSON(@json, '$.body.ResultSets.Table1')
WITH (
    [FirstName] varchar(15) '$.FirstName',
    [PhoneNumber] number '$.PhoneNumber',
    [Surname] varchar(15) '$.Surname'
)

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!

Useful Links

• Logic Apps - Microsoft Docs - SQL Connector
• Logic Apps - Microsoft Docs - Handling bulk data in a SQL Connector
• Logic Apps - Microsoft Docs - Use Parse JSON action
• Logic App Import CSV into SQL Server
• Logic Apps - How do they work - Microsoft Docs
• SQL Server - Microsoft Docs - OPENJSON