Archive for July, 2020

Working with JSON data in SQL Server

July 26, 2020 1 comment

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.
select *
from Employee
  • 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.
select *
from Employee
  • 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
Street1 as [Address.Street1], Street2 as [Address.Street2],City as [Address.City], ZipCode as [Address.ZipCode]
from Employee

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 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 function extracts complex sub-object such as arrays or objects that are placed in JSON text.
    • Example ‘Tags’ column in above SQL Table.
Query records whose Color is ‘Yellow’

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 Price for records where Color is ‘Yellow’
Update Products
set JSONData = JSON_MODIFY(JSONData,'$.Price',1000)
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.
    • 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.
Convert JSON data to Table format

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


Categories: SQL Tags: ,

Power Apps | CDS | Track deleted records using ‘Change Tracking’

For unversed, Change Tracking in CDS allows data to be synchronized by detecting what data has changed since the data was initially extracted or last synchronized.

Its useful in Integration scenarios, where you have to keep data between CDS and external systems in sync.

Assume that your customer has a LOB application along with CDS and you want sync the LOB application (i.e., When record gets Created/Updated/Deleted in CDS), this can be achieved using ‘Change Tracking’ feature.

In this article, lets understand how ‘Change Tracking’ works in CDS using ‘Organization Service’, to:

  • Get the Created or Updated records from last sync.
  • Get Deleted records from last sync.

Enabling Change Tracking for an Entity:

  • To use ‘Change Tracking’ make sure that the change tracking feature is enabled for that entity.
  • This feature can be enabled by using the customization user interface.
Enable Change Tracking

‘Change Tracking’ using the Organization Service:

  • When change tracking is enabled for an entity, RetrieveEntityChangesRequest message can be used to retrieve the changes for that entity.
  • In my example, I am using OOB ‘Account’ entity which has 10 records.
  • When ‘RetrieveEntityChangesRequest’ triggered for the first time, we would get all 10 records in ‘Changes’ object and a ‘Data Token’.
  • Copy the ‘Data Token’.
  • Now lets delete an Account and add a new Account in CDS.
  • Trigger ‘RetrieveEntityChangesRequest’ again by passing the copied ‘Data Token’.
    • Passing ‘Data Token’ in ‘RetrieveEntityChangesRequest’, fetches only data for those changes that occurred since that version will be returned.
  • This time we would get 2 records in ‘Changes’ object.
    • 1 NewOrUpdatedItem
    • 1 RemovedOrDeletedItem.
  • ‘NewOrUpdatedItem’ is object of type ‘Entity’ with all the properties. ‘RemovedOrDeletedItem’ is object of type ‘EntityReference’ contain only GUID of deleted record.
  • Following is the code snippet to track changes and display in console.
// Created or Updated records will be returned as 'Entity' object.
var createdorUpdateRecords = new List<Entity>();
// Deleted records will be returned as 'Entity Reference' object.
var deletedRecords = new List<EntityReference>();

// Retrieve records by using Change Tracking feature.
var request = new RetrieveEntityChangesRequest
    EntityName = "account",
    Columns = new ColumnSet("name"),
    PageInfo = new PagingInfo() { 
       Count = 5000, 
       PageNumber = 1, 
       ReturnTotalRecordCount = false 
    // DataVersion is an optional attribute
    // If not passed system returns all the records. 
    // Dont pass this attribute when calling for the first time.
     DataVersion = "706904!07/24/2020 08:00:32"

while (true)
  var response = (RetrieveEntityChangesResponse)_serviceProxy.Execute(request);

  if (response != null && 
      response.EntityChanges != null &&   response.EntityChanges.Changes != null)
    foreach (var record in response.EntityChanges.Changes)
      // Read NewOrUpdatedItem in to 'createdorUpdateRecords' list. 
    if (record is NewOrUpdatedItem)
     createdorUpdateRecords.Add((record as NewOrUpdatedItem).NewOrUpdatedEntity);

    // Read RemovedOrDeletedItem in to 'deletedRecords' list.
    if (record is RemovedOrDeletedItem) 
     deletedRecords.Add((record as RemovedOrDeletedItem).RemovedItem);
    // Display Created or Updated records
    createdorUpdateRecords.ForEach(x => Console.WriteLine("Created or Updated record id:{0}", x.Id));
    // Display Deleted records
    deletedRecords.ForEach(x => Console.WriteLine("Deleted record id:{0}", x.Id));

    // Logic for Pagination
    if (!response.EntityChanges.MoreRecords)
      //Store token for later query
      token = response.EntityChanges.DataToken;
   // Increment the page number to retrieve the next page.
   // Set the paging cookie to the paging cookie returned from current results.
   request.PageInfo.PagingCookie = response.EntityChanges.PagingCookie;
  • Run the code and you would get response as below


Categories: CRM Tags: , ,