Ideas to create custom reports for serialized carts

Vote:
 

Hi,

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!

#205663
Jul 18, 2019 17:38
Vote:
 

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. 

#205692
Jul 19, 2019 11:45
Vote:
 

Hi,

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]
      ,[CustomerId]
      ,[Name]
      ,[MarketId]
      ,[Created]
      ,[Modified]
      ,[Data]
      ,JSON_VALUE([Data],'$.Forms[0].AuthorizedPaymentTotal') AS AuthorizedPaymentTotal
FROM [SerializableCart]
ORDER BY AuthorizedPaymentTotal DESC
#205701
Jul 19, 2019 16:31
Vote:
 

Thank you guys!

#205703
Jul 19, 2019 17:10
Vote:
 

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.

#205705
Jul 19, 2019 17:58
Siddharth Gupta - Jul 19, 2019 21:01
Thanks Stefan!
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.