Parse JSON in SQL Server 2016

You can now parse JSON in SQL Server 2016. This is useful for UiPath as the transaction information is stored in JSON within the QueueItems table and would be helpful when creating reports from the database.

5 Likes

Here is an example of extracting First Name from the SpecificData field using JSON_VALUE

JSON String

{"DynamicProperties":{"Type":"ADD","Employee ID":"","First Name":"Lonnie","Last Name":"Cruz","Email Address":"lonnie.cruz@genfour.net"}}

SELECT Id,
     JSON_VALUE(SpecificData, '$."Dynamic Properties"."First Name"') as Town
FROM test AS t 
WHERE ISJSON(SpecificData) > 0

Should be easy from here on in… :smiley:

2 Likes

Hello! I have a problem with this request.Use this in Execute query activity. Example -
SELECT [Id]
,JSON_VALUE([Data],β€˜$.β€œ@Number”’) AS [Number]
,JSON_VALUE([Data],β€˜$.β€œ@Name”’) AS [Name]
FROM [section].[subsection]
Error

I try to fix this, it gives an error on the following query element

Have you tried running it in Management Studio - easier to get the query running there by breaking it down and then importing into UiPath. This is a SQL syntax error - I’ll have a look if I get time.

RD