SQL Server Index on JSON containing dynamic fields or array

2k Views Asked by At

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 ?

0

There are 0 best solutions below