How best to work with data in JSON?

Question

NoSQL

A service has been launched on SQL Server 2012, which receives a huge JSON as an input, which is an array of 500-800K products with a dozen attributes.
This JSON is compressed in Zip and then wrapped in Base64 (yes, yes, this is such a savings!). Each such data clod comes regularly from different remote clients, is unpacked and added line by line to a DBMS table with a predetermined structure. And at the same time, all these records of some client must be completely thrown out and replaced with new ones when a new JSON arrives. It turns out such a sickly I / O in connection with the record. Often you need to search for a product using LIKE on those records, which is very inefficient.
An alternative is to write the packed data as it is in the form of a BLOB and, for each product search, unpack the entire JSON structure in memory and search for it already.
What do you think is more efficient in terms of time, storage and I / O?
Or are there other options?

Answer 1

It is better to insert a large array of data into SQL using bulk insert, but, as far as I remember, this operation does not understand json (need to be clarified). You can convert from json to csv (or to something else that bulk insert works with), and then insert it into sql. JSON can be converted to csv with a fairly simple javascript script.
To make the search work quickly, you need to make the appropriate indexes.
The proposed alternative can be considered working only if there are no remote clients with not too fast communication channels (and in general there are not very many of them), in addition, in order not to carry this huge BLOB for each request, you can pull it out once at the start of the client, and then search only locally.
But, as for me, the option with loading into SQL is quite working.

Answer 2

Take elasticsearch and do a search in it, in fact, you can store the data itself in it.