0

I'm considering adding a full text index for a column whose values are json strings. These jsons usually contain Guid (or UUID) values.

The index goal is to allow fast search over both "normal" words (e.g., some user generated text) and guids.

Some more info about this column, called RequestContent:

  • I actually have another column, ResponseContent, which the following applies for as well, so that I presume that an answer for this question would apply for it as well.

This column is essentially used as a log for HTTP Post requests' body, so that each incoming POST request body is logged into that column. There are hundreds of web services whose requests bodies are logged, and there is no relationship between two different web services' requests.

For example, one request could look like:

{ "orderId": "607bbc9b-9c0e-4921-b5c3-55f68891e619", "status": 0, "createdOn": 16011560804216 ... }

While another could look like:

{ "claimStatus": 1, "actionStatus": 8, "senderId": "807acc23-9dde-4aaa-b5c3-55df5431e619", "documents": [ { "documentTitle": "some free text" }] ... }

Any json may contain fields of any kind: number, short string fields (like firstName, or Guids, etc.) or long string fields (usually containing hundreds of words of free text, but sometimes even large Base64 encoded data), boolean, nested objects, arrays. If it matters, it would be safe to say that most requests (I'd say 80%) contain number and short string fields.

This column's type is ntext (yes, it's old), but I can recreate it as any other type that I'd like.

Is it a good idea to build a full text index for this column? My concern is that the index would contain a row for each unique Guid, which would result in consuming an unfeasible disk space.

EDIT: @David Spillett asked:

Could you extract the parts that you want indexed into a persisted computed column that you can then add a full text index for?

It's technically feasible, but I'm looking for an answer for the current use case.

3
  • How tight are you for space? Could you extract the parts that you want indexed into a persisted computed column that you can then add a full text index for? You might need to state what other content is in the JSON to get decent answers: by JSON requests do you mean HTTP requests formatted as JSON? What data are you including? (request URI, POST data, response body, request/response headers?) If including the response, what is that going to be? (mainly natural language text, numbers, ...). A full text index may not be appropriate at all depending on some of those details. Oct 1, 2020 at 14:54
  • @DavidSpillett, thank you for your comments, I'll edit my question and add additional info.
    – HeyJude
    Oct 1, 2020 at 14:58
  • @DavidSpillett, editted.
    – HeyJude
    Oct 1, 2020 at 15:27

2 Answers 2

0

... a full text index for a column whose values are json ... usually contain Guid (or UUID) values.

Is it a good idea to build a full text index for this column?

I would say "No".

A full text index makes all of the words in the field available for searching. I don't think that's what you want. You want to be able to search for those Guid/Uuid values and, possibly, specific Request Types.

My preference would be to pull these values out into separate fields in their own right and index them.

Failing that, use a JSON-aware index type, which will be better, but still slower than the separate fields.

1
  • I actually want to be able to search for both "normal" words and Guids, I should have clarified that. I'm not sure whether it would change your answer.
    – HeyJude
    Oct 2, 2020 at 12:32
0

It will work just fine. Whether the size and performance is worth it? That depends on too many things. Why not just create the darned thing on a test server?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.