I need create some custom reports for marketing purposes, this involves querying into the order group and line item tables. But now we are moving to serialized carts and the SQL stored procs will be useless as all the cart info is stored in a single table and in a JSON format. Upon looking at alternatives I see that Service API can be used to create the marketing reports. Is that the correct approach to do so? Or are there any other suggestions/ways by which we can fetch cart related information to generate reports.
Thanks in advance!
The only option for now is to load the carts into memory using IOrderRepository and generate the report from that data. if you load and process by batches it should not be too slow.
If you're looking for a like-for-like replacement in your stored procedures, I wonder whether you could get what you want from the data by using the inbuilt JSON querying capabilities in newer versions of SQL server, for example:
SELECT TOP 10 [CartId]
,JSON_VALUE([Data],'$.Forms.AuthorizedPaymentTotal') AS AuthorizedPaymentTotal
ORDER BY AuthorizedPaymentTotal DESC
Thank you guys!
I was about to write something similar to Paul's answer. In fact, I have done so before. Very useful if, for instance, you need to make a report of the value of unconverted carts.
If you need to read more than a single property, have a look at the OPENJSON function instead. I would wrap the call to that function (and its parameters) in a CROSS APPLY with the JSON data as the input. By doing that you can also use the result set of that APPLY for filtering, grouping and ordering.
Generally, JSON handling in SQL is not very fast. But it is definitely faster than loading many carts from DB and do aggregation in the application.