Dynamic SQL and JSON — an amazing pairing for modern web applications

Whenever we want to create an amazing web application that communicates with the database, we will most likely use APIs to ensure communication. Nowadays, one of the most widespread formats for APIs is JSON.

JSON (Javascript Object Notation) is an open data interchange format that is easy to write and read by humans, and also very easy to generate and parse by & for machines. It’s based on the key-value pairs, where the key is always a string and represents the identifier of the values, and the value can be of any type (string, number, boolean, null, array, or object).

If you haven’t heard about dynamic SQL, you’ll find some nifty details here and here.

Ok, ok…but where is our star, our beloved SQL?

Well, our star comes in handy when we want to parse JSON and ingest the data from it into our database. And because our SQL is always a great hero, it provides us with some impressive commands like:

• ISJSON — helps us to check if the JSON is well written and formatted (if it’s a valid JSON)
• OPENJSON —to retrieve the desired data from JSON, we use the OPENJSON command, which allows us to ‘open’ the JSON like a treasure. By using this SQL command, we can display the data in a tabular format that is easier for us to work with.
• JSON_VALUE — if we want to extract only the scalar value from a JSON string, this is the command that will help us.
• JSON_QUERY — as we know, in this data-driven world, we don't always work with just numeric values, but also with strings and so on; if JSON_VALUE helped us “catch” the numeric values, JSON_QUERY can extract arrays and strings from a JSON.
• JSON_MODIFY — all good, but what if we want to modify a value in a JSON string? Don’t worry, SQL has thought of it and provided us with the JSON_MODIFY command.

Suppose we have the following JSON:

{
  "firstname":"Jon",
  "lastname":"Snow",
  "age": 24,
  "occupation": "Lord Commander",
  "ability":"Expert swordsman",
  "seasons": [1,2,3,4,5,6,7,8],
  "resurrected": true
}

//he was 24 old in the last season 😃

In SQL we put it into a variable

declare @json nvarchar(250) = N'{
  "firstname":"Jon",
  "lastname":"Snow",
  "age": 24,
  "occupation": "Lord Commander",
  "ability":"Expert swordsman",
  "seasons": [1,2,3,4,5,6,7,8],
  "resurrected": true
}'

First, we want to check if this JSON is valid

select ISJSON(@json) --> the result is 1

If the result of this line is 1, then the JSON is valid, otherwise, we need to check it again.

Let’s see how old Jon Snow is:

select JSON_VALUE(@json,'$.age') --> the result is 24

Now I bet you’re wondering what JSON looks like in a tabular form…let’s find out using the OPENJSON command:

SELECT * from OPENJSON(@json)

The result looks like this:

where the type column contains the identifier of each data type (1 for strings, 2 for numbers, 3 for boolean, 4 for arrays)

Returning to our main topic. Dynamic SQL and JSON can be used together to create dynamic queries that return JSON data. For example, a dynamic SQL query could be used to build an INSERT statement that inserts data from the JSON string into a database table.

Here’s a small piece of a “home-made” SQL script that helps us to insert values from a JSON string into the database table using the OPENJSON command:

The whole script can be found here 😉(i used cursors, we will talk about them in another article).

Conclusion

Overall, when dynamic SQL and JSON use their superpowers together we can create the most flexible and adaptable application that can handle any data change and user requirements.

P.S. Here you’ll find another script that performs an UPDATE action on a specified database table with values from a provided JSON.

If you liked my articles, let’s spend our coffee break together here 😉. Thank you for your support!