Querying Custom Data Columns in SQL

I would appreciate some help in doing a query on a custom data fields.
I have my Driver entity with custom data as such I’ll skio the cut and paste and give a few fields

Custom data= Address, Email Address, Hourly Rate, Rate Per Delivery - as an example

So it would be something like

Select ??? from entities where EntityId =3 and Name = ‘Joe’;
Not looking for a tutorial just an example of how to query these custom data columns
Thank You in Advance

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

  • 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

There is also Custom Report Tags for this as well. They are version 5 only however.

2 Likes

Thank you! To both of you for your help.