Archive
Working with JSON data in SQL Server
In this article, lets see how to work with JSON data in SQL Server using SSMS. Following features will be covered in this article.
- Format SQL table data in JSON format (Using FOR JSON PATH clause)
- Store JSON data in SQL table
- Query JSON data in SQL server (Using JSON_VALUE and JSON_QUERY functions)
- Update JSON data in SQL table (Using JSON_MODIFY function)
- Convert JSON data in to SQL table format (Using OPENJSON function)
- Validate JSON data format (Using ISJSON function)
Format SQL table data in JSON format:
Lets see how to format SQL query tabular results in JSON format using FOR JSON clause.
- Create a simple ‘Employee’ table in SQL server.
- When you query ‘Employee’ table you would get the result in tabular format.
- Lets convert the result set in to JSON format using FOR JSON clause.
-- JSON PATH
select *
from Employee
FOR JSON PATH
- Use WITHOUT_ARRAY_WRAPPER to get a single JSON object instead of an array. Use this option if the result of query is single object.
-- JSON PATH and WITHOUT_ARRAY_WRAPPER
select *
from Employee
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
- We can also form inline JSON object format. Lets see how to show ‘Address’ as a separate object.
-- JSON PATH, WITHOUT_ARRAY_WRAPPER and 'Address' as seperate object
select
ID,Name,
Street1 as [Address.Street1], Street2 as [Address.Street2],City as [Address.City], ZipCode as [Address.ZipCode]
from Employee
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
Store JSON data in SQL table:
- JSON is a textual format that can be used like any other string type in SQL Database. JSON data can be stored as a standard NVARCHAR.
- In the example below SQL table has ‘JSONData’ column which is NVARCHAR(MAX).
Query JSON data in SQL server:
- Lets query the JSON data using JSON_VALUE and JSON_QUERY.
- JSON_VALUE:
- JSON_VALUE function extracts a value from JSON text stored in the SQL column.
- The extracted value can be used in any part of SQL query.
- JSON_QUERY:
- JSON_QUERY function extracts complex sub-object such as arrays or objects that are placed in JSON text.
- Example ‘Tags’ column in above SQL Table.
Update JSON data in SQL table:
- JSON_MODIFY function can be used to update the JSON text without re-parsing the entire structure.
- Below is the query to update the ‘Price’ of all ‘Yellow’ color products to 1000.
Update Products
set JSONData = JSON_MODIFY(JSONData,'$.Price',1000)
where
JSON_VALUE(JSONData,'$.Color') = 'Yellow'- Post query execution, ‘Price’ updated to 1000 as shown below.
Convert JSON data in to SQL table format
- Using OPENJSON function JSON data can be transformed in to SQL table format.
- OPENJSON
- Its a table-value function that parses JSON text, locates an array of JSON objects, iterates through the elements of the array, and returns one row in the output result for each element of the array.
Validate JSON data format
- Since JSON is stored in a standard text, there are no guarantees that the values stored in text columns are properly formatted using ISJSON function.
- ISJSON function returns the value 1 if the data properly formatted in JSON.
ISJSON(SQL_Cell) > 0🙂
Power Platform – Pass json collection from Canvas App to Power Automate
Refer my previous article for steps to pass json collection from Power Automate to Canvas App.
Now lets see how to pass json collection from a Canvas app to Power Automate flow.
Before we start lets make sure to meet all the prerequisites.
Prerequisites:
- Subscribe to 30 days trial and get Office 365 account.
- Connect to Power Automate portal using Office 365 account to build Power Automate flow.
- Connect to Power Apps maker portal using Office 365 account to build the Canvas App.
Once all prerequisites are met, here is the high level design:
- Create a new Canvas app.
- Prepare a json collection.
- Create a new Power Automate flow.
- Trigger Power automate flow from Canvas App by passing the collection as parameter.
- Read the json collection from Power Automate flow.
- Create ‘Contact’ records in CDS.
Lets get started.
Steps to configure Canvas App:
- Connect to Power Apps maker portal using the Office 365 account.
- Create a new Canvas App.
- Note: Make sure the same Environment used for Power Automate is selected.
- Add a new button ‘Generate Sample Collection’.
- On button’s ‘OnSelect’ prepare a json collection using ‘ClearCollect’ function as below.
- Once your run the App and click ‘Generate Sample Collection’ button, you should see the collection from ‘File -> Collections’ tab.
Steps to configure the Power Automate Flow:
- Connect to Power Automate portal using the same office 365 credentials used to connect to Power App portal.
- Create a new ‘Instant flow’ by selecting the right Environment.
- Provide a name ‘GetCollectionFromPowerApp’ and select trigger as ‘PowerApps’.
- Now, we need to read the collection passed from Canvas app to a variable. Lets add a new ‘Initialize Variable’ action.
- In the ‘Initialize Variable’ action,
- Provide variable name by setting ‘Name’ field. I set it as ‘collPersons’.
- Type: String
- Select ‘Value’ and click on ‘Ask in PowerApps’. A auto generated name would appear and select the same.
- Once we receive the collection and stored in a string variable ‘collPersons’, next we need to parse in to json collection.
- Add a new ‘Parse JSON’ action.
- In ‘Parse JSON’ action,
- set Content as ‘collPersons’ from ‘Initialize Variable’ action.
- Next we need to provide the json schema coming from Canvas app. To do that, click on ‘Generate from sample’.
- Copy the json collection formed in Canvas app using ClearCollect function and paste as below.
- If no warnings, your ‘Parse JSON’ pane should look as below.
- Now we got json collection parsed and we are good start final step, creating records to CDS.
- Add ‘Create a new record’ CDS action and select the ‘Environment’ and ‘Entity Name’
- Set the attributes (Last Name and Email) from ‘Parse JSON’ action.
- Once you set variables in ‘Create a new record’ CDS action, flow would automatically wrap the action in ‘Apply to each’.
Trigger the Power automate flow from Canvas App:
Now we have Power automate flow with the logic to read and parse the json collection and add them to CDS as Contact records, lets trigger the flow from Canvas App.
- Open the Canvas app created in above sections.
- Add a new button ‘Share Collection To Flow’
- Select the button, from the ribbon menu, click on ‘Action -> Power Automate’.
- From the pane select the ‘GetCollectionFromPowerApp’ Power app flow.
- After few seconds, you would see the Run() command auto populated. Now pass the ‘collPersonalInfo’ collection wrapped in JSON() function.
- JSON() function is required to convert the ‘collPersonalInfo’ collection to JSON string as the Power Automate flow expects ‘String’ variable as first action.
- Finally ‘OnSelect’ should look as below.
- Now run the App and click ‘Share Collection To Flow’ button, post run, go to Power automate flow’s history and you should see it ran successfully.
🙂
‘Failed during http send request’ error – Triggering Power automate from Canvas App
While configuring Canvas app for my previous article ,I encountered following error while selecting ‘Power Automate’ from Canvas app.
Reason:
- In my ‘Power Automate’, I was reading collection received from Canvas app to ‘Array’ type variable.
- There seems to be issue with ‘Array’ datatype when triggering ‘Power Automate’ flow Canvas App.
Fix:
- In the Power Automate, changed the Type from ‘Array’ to ‘String’ and used ‘Parse JSON’ action to convert String to json collection.
- In Canvas App, used JSON() function to convert the Collection to json string.
🙂
Retrieve record using OData and JQuery in CRM 2011
Hi,
Below is the script to read a record using OData & JQuery
function retrieveRecord(id, odataSetName) {
// Get Server URL
var serverUrl = Xrm.Page.context.getServerUrl();
//The OData end-point
var ODATA_ENDPOINT = “/XRMServices/2011/OrganizationData.svc”;
//Asynchronous AJAX function to Retrieve a CRM record using OData
$.ajax({
type: “GET”,
contentType: “application/json; charset=utf-8”,
datatype: “json”,
url: serverUrl + ODATA_ENDPOINT + “/” + odataSetName + “(guid'” + id + “‘)”,
beforeSend: function (XMLHttpRequest) {
//Specifying this header ensures that the results will be returned as JSON.
XMLHttpRequest.setRequestHeader(“Accept”, “application/json”);
},
success: function (data, textStatus, XmlHttpRequest) {
readRecord(data, textStatus, XmlHttpRequest)
},
error: function (XmlHttpRequest, textStatus, errorThrown) {
alert(“Error – ” + errorThrown)
}
});
}
function readRecord(data, textStatus, XmlHttpRequest) {
alert(“Record read successfully!!”);
var account = data;
alert(“Name – ” + account.d.Name);
alert(“Id – ” + account.d.AccountId);
}
How do I call this method :-
- Below is the sample function to read Account
var accountId = “”; // Assign account GUID
var odataSetName = “AccountSet”;
retrieveRecord(accountId,odataSetName );
Hope it helps 🙂
Rajeev Pentyala - Microsoft Power Platform 