Ideas to create custom reports for serialized carts

Siddharth Gupta

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

Quan Mai

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

Paul Gruffydd

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

Siddharth Gupta

Vote:

Thank you guys!

#205703

Jul 19, 2019 17:10

Stefan Holm Olsen

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!