Querying Custom Data Columns in SQL

Version 4
2

Entity Custom Data is stored in JSON format. There is no simple method to parse this data using pure SQL.

Pasted image856×422 14.9 KB

SELECT
[Id]
--,[EntityTypeId]
,[Name]
,[CustomData]
--,[Notes]
--,[AccountId]
--,[WarehouseId]
--,[LastUpdateTime]
--,[SearchString]
FROM [Entities]
WHERE [EntityTypeId] IN (SELECT [Id] FROM [EntityTypes] WHERE [Name] = 'Customers')
ORDER BY [Name]

You have a few options:

  • (V4 or V5) install a Stored Proc into your Database that facilitates reading JSON data

  • Consuming JSON Strings in SQL Server - Simple Talk

  • JSON for SQL Server. Part 1 - CodeProject

  • (V5) JScript helper JSON.parse(<field>) to parse out the data after sql.Query(<query>) helper pulls the data from the DB

  • (V5) JScript helpers api.EntityType(name).Fields(name).Exists() and api.Entity(name).Data(field).Get() while looping through Entities

  • (V5) {REPORT SQL DETAILS:x} which contains JSON Parsing functionality

Using Stored Proc (function library)

Using V5 methods

… nothing to say here yet. If you upgrade to V5, we can go into more detail

2 Likes