Field type for keywords search

Data Service
1

Hi,

I would like to know what is the most efficient way to store keywords for searching.

  • space-separated keywords stored as string (eg. “dog cat”)
  • comma-separated keywords stored as string (eg. “dog,cat”)
  • array of keywords stored as JSON (eg. [“dog”,“cat”])

Will searching in JSON be faster using “jsoncontains” as compared to searching in string using “like %keyword%”

Is it possible to do a partial keyword search in JSON? eg searching with “do” to return the result for “dog”

2

Hi @Francis_Thong ,

In all cases described by you you will have almost the same performance.
If to talk about like, due to %keyword% pattern for like database will not use column index and will perform fullscan.
If to talk about JSON, database does not support indexes for JSON columns so gain fullscan will be performed.
At the same time with jsoncontains you will be unable to perform “partial” search since this function returns true only for full match.
If it is important for you to support “partial” search by keywords I would advice you go use like and space-separated keywords (since condition will be simpler).

Regards, Andriy