3

We have an AuditEntity table where all changes are stored. The table has an OldValues and NewValues column, which contains json of the altered fields.

enter image description here

I want to query this table so it gives me all rows where a particular field was modified, for example the field 'projectName'. How can I do this in an efficient manner where the field name search is indexed ?

SELECT * FROM AuditEntity
WHERE EntityName = 'Project'
  AND EntityId = 100
  AND ChangeType = 'Modified'
  AND ??NewValues contains the field 'projectName'??

I know we can define index on computed json property, but here the json contains dynamic field names.

Or would it be better to have a single json field with the following structure instead ? 

[
    {fieldName:'projectName', oldValue:'abc', newValue:'abcd'},
    {fieldName:'customerId', oldValue:'1', newValue:2}
]

Is it possible to index a json property when it contains an array ?

Guillaume Morin
  • 3,910
  • 6
  • 25
  • 40
  • 1
    If you want to use the JSON functionality built into SQL Server then I would change your JSON to the array you propose. Then you can use OPENJSON to query it. Otherwise you could use a `LIKE` query since your JSON data is fairly small. (Assuming you don't have millions of records to search). Unfortunately, I don't think you can index it (i.e. create a computed column for the JSON) – Rick S Oct 04 '17 at 15:34
  • 1
    If you really need speed then you could create a FULL TEXT catalog. But then it's database size increase vs. speed increase. – Rick S Oct 04 '17 at 15:37
  • I'd personally do a EAV model instead, where each changed field gets its own row. Then if you index on the column with the field name that was modified, you can seek instead of scan. – Jeffrey Van Laethem Oct 04 '17 at 16:00
  • 1
    Yes it is possible to index JSON arrays, please see: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/09/indexing-json-arrays-using-full-text-search-indexes/ (however it uses full text search, FTS) – Jay Dubal Mar 20 '18 at 11:42
  • 1
    From the blog: 'Full-text indexes can be used if JSON in your column is formatted as a simple array of scalar values.' My JSON is an array of complex objects, with a property 'fieldName' – Guillaume Morin Mar 20 '18 at 14:42
  • I went with a EAV model instead – Guillaume Morin Mar 20 '18 at 14:42
  • When each change is a row, as per EAV. Then what is the need of JSON column itself? you can then have three permanent columns in the table... "FieldName", "OldValue" & "NewValue".... or do give more details of EAV with JSON that worked for you. – Jay Dubal Mar 21 '18 at 07:38
  • Sorry, when I said I used EAV instead, it meant that I didn't use JSON in the end, but columns like you say – Guillaume Morin Mar 21 '18 at 12:12

0 Answers0