Querying Custom Data Columns in SQL

Version 4
1

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

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
3

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

2 Likes
4

Thank you! To both of you for your help.