0

I have a few thousand rows in my table (SQL Server 2016).

One of the columns stores JSON documents (NVARCHAR(max)).

The JSON documents are quite complex in therms of nesting etc.. also they can be very different one to another.

My goal is to search each document for a certain match. Say: "MagicNo":"999000".

So if the document has a property "MagicNo" and if the value is 999000 then it's a match.

I know you can navigate through the document using the JSON_VALUE $. followed by the path, but since those docs can be very different the "MagicNo" property may appear pretty much everywhere in the document (a lot nesting). So xpathing is out of question here.

Is there some kind of wild card I could use with JSON_VALUE to say search the entire doc and return it if the match is found?

The simple 

like '%999000%'

and 

CONTAINS

searches on the VARCHAR column are out of question here due to the poor performance.

Any thoughts?

Thanks.

tom33pr
  • 853
  • 2
  • 12
  • 30
  • Possible duplicate of [Is it possible to use wildcards and ranges with JSON\_VALUE?](https://stackoverflow.com/questions/28551909/is-it-possible-to-use-wildcards-and-ranges-with-json-value) – Paul Karam Mar 01 '18 at 09:46
  • Poor performance on a few thousand rows? How long does it take? I would've though a raw text search would be quicker than shredding then searching. – Nick.Mc Mar 01 '18 at 10:11
  • No it is not a duplicate. The other answer has a wildcard set on a specific array and I am after a general wildcard that would allow traversing through the entire document. – tom33pr Mar 01 '18 at 10:11
  • Nick - it takes up to 10 seconds. Way too long for what I need. – tom33pr Mar 01 '18 at 10:13

0 Answers0