Using JSON in SQL Quicktipp

Posted byahmeduzejnovicPosted inAllgemeinTags:

I had a situation recently where I needed to filter specific offboarding tasks from my database. Sounds simple at first, but the challenge was that the relevant information was not stored in its own column, it was inside a JSON field.

In my case, the table stores requests from au2mator. Every request can have different parameters, so instead of creating a new column for every possible value, everything is stored inside one column called RequestData as JSON.

At first, this looks messy. But actually, it makes things much easier.

The problem

I wanted to find all pending offboarding requests for a specific user. The user was stored inside the JSON, not as a normal column.

So instead of overcomplicating things, I used a simple SQL function.

The solution

 SELECT * FROM [au2mator_HELP].[dbo].[Tasks] WHERE ExecutionStatus = 'Pending' AND RequestName = 'Offboarding' AND JSON_VALUE(RequestData, '$.zuLoeschenderBenutzer') = '$UserDN';

That is it. One line, and I can directly read a value from the JSON and use it like a normal column.

Why I like this approach

In automation, things change all the time. Today you have one parameter, tomorrow you have five more. If you try to model everything with fixed columns, your database becomes messy very quickly.

With JSON, I just store everything in one place. And when I need something, I pull it out with JSON_VALUE.

  • No need to change the database every time something new comes in
  • No complex parsing outside of SQL
  • Still very easy to filter and work with the data

Where this helps a lot

I use this a lot in my automation flows. Especially in systems like au2mator or when working with APIs, where every request can look a bit different.

Instead of trying to force everything into a strict structure, I keep it flexible and only extract what I really need.

Final thought

This is one of those small things that makes a big difference. Once you start using JSON in SQL together with JSON_VALUE, you will notice how much simpler your queries and your overall automation design becomes.

Leave a comment