Tip: Built-in JSON Functions in SQL

Did you know that SQL has some very handy built-in JSON functions?

by Hannes DuPreez

Mar 17, 2017

Did you know that SQL has built-in JSON functions? These can come in very handy!

Instead of creating a very complicated query that exports XML and then translate it to JSON through a third party, you can use the following JSON SQL functions to identify valid JSON strings, extract JSON values and more.

 Function   Description
ISJSON

Tests whether a string contains valid JSON.

 JSON_VALUE

Extracts a scalar value from a JSON string.

 JSON_QUERY

Extracts an object or an array from a JSON string.

 JSON_MODIFY

Updates the value of a property in a JSON string and returns the updated JSON string.

Visit the DevX Tip Bank

Hannes du Preez is a self-taught developer who started learning to program in the days of QBasic. He has written several articles over the years detailing his programming quests and adventures. .NET is his second love, just after his wife and kid. He has always been an avid supporter of .NET since the beginning and is an expert in VB and C#. He was given the Microsoft Most Valuable Professional Award for .NET (2008–2017). He has worked as a moderator and an article reviewer on online forums and currently works as a C# developer and writes articles for CodeGuru.com, Developer.com, DevX.com, and the Database journal.
His first book Visual Studio 2019 In Depth is currently on sale on Amazon and Bpb Publications.

You could reach him at: ojdupreez1978[at]gmail[dot]com