Querying Custom Data Columns in SQL

[QMcKay]

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

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

• Consume & Query JSON in SQL Server with T-SQL | Simple Talk

• http://www.codeproject.com/Articles/1000953/JSON-for-Sql-Server-Part

• (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)

@QMcKay

This uses the 2nd library that I quoted.

I made this routine as flexible as I could for now - it should work for Tags or States.

Using V5 methods

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