Get a value extracted from JSON_VALUE of single row and select the same for all the rows in a tsql Select statement

  Kiến thức lập trình

I am preparing a Source view for my SQL to Dynamodb migration.

SELECT TOP (1000) [Id]      
      ,[AggregateId]        
      ,[eventType]      
      ,CAST(DECOMPRESS (CompressedJson) AS NVARCHAR(MAX)) decompessedJSONdata       
  ,JSON_VALUE((CAST(DECOMPRESS (CompressedJson) AS NVARCHAR(MAX))),'$.RetentionDate') AS 'RetentionDate'        
  FROM mytable

Sample output:

enter image description here

My problem is for each aggregateid, only RetentionDate eventtype has the retention date value in compressed json and I need to get that and assign that as RetentionDate for all the events of an aggregateid.

Expected Output:

enter image description here

How to achieve this. Table size is 6 TB and has ~13b records and Id is clustered index, AggregateId is unique index and no index on the eventtype column.

Retentiondate is readily available in another table but the view I created by joining two tables takes ages to run because of the sheer dataset size( which needs another post).

  • sql
  • t-sql

LEAVE A COMMENT