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:


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.