I'm trying to group all occurrences of an ID's data into a JSON payload as an array

Viewed 18

I'm new to KSQL and I feel like there should be a way to group and add data into an array I'm getting these individual objects from the stream, for example

{
  "ENRLMT_ID": "I12345",
  "STUS_CD": "06",      
  "STUS_RSN_CD": "081",
  "STUS_RSN_DESC": "APPROVED FOR REVALIDATION",
  "STUS_DESC": "APPROVED"  
}

{
  "ENRLMT_ID": "I12345",
  "STUS_CD": "13",
  "SRC_ENRLMT_STUS_HSTRY_SK": "OxdP6jOQnr/o+UfE4q0zr5p7lMvK0Fh9N",
  "STUS_RSN_CD": "029",
  "STUS_RSN_DESC": "THE PROVIDER OR SUPPLIER IS VOLUNTARILY WITHDRAWING",
  "STUS_DESC": "DEACTIVATED"  
}

the results I'm looking to get is:

 {     
          "ENRLMT_ID": "I12345",
          PAYLOAD: [ 
              {
              "STUS_CD": "06",              
              "STUS_RSN_CD": "081",
              "STUS_RSN_DESC": "APPROVED FOR REVALIDATION",
              "STUS_DESC": "APPROVED"
              },
              
              {
              "STUS_CD": "13",              
              "STUS_RSN_CD": "029",
              "STUS_RSN_DESC": "THE PROVIDER OR SUPPLIER IS VOLUNTARILY WITHDRAWING",
              "STUS_DESC": "DEACTIVATED"  
            }
        ]
    }

This is the KSQL I've used to get as close as I could:

    CREATE STREAM ENROLLMENT_STATUS_STREAM AS SELECT
  ENRLMT_ID AS ENRLMT_ID,
  STRUCT(
  "STUS_CD":= ESJ.STUS_CD,
  "STUS_RSN_CD" := ESJ.STUS_RSN_CD,
  "STUS_RSN_DESC":= ESJ.STUS_RSN_DESC,  
  "STUS_DESC":= ESJ.STUS_DESC    
  ) AS PAYLOAD
  FROM ENROLLMENT_STATUS_DATA ESJ;

Resulting in this output from the stream:

{
  "ENRLMT_ID": "I12345",
  "PAYLOAD": {
    "STUS_RSN_CD": "029",
    "STUS_RSN_DESC": THE PROVIDER OR SUPPLIER IS VOLUNTARILY WITHDRAWING",
    "STUS_CD": "13",
    "STUS_DESC": "DEACTIVATED"    
  }
}
0 Answers
Related