Pulling floats to sum data in array structure using SQL

January 27, 2022

I’m trying to pull numbers from an array structure and then I want to sum them.

Example row entry:

{"DBA":50.0},{"RST":132.0},{"ZIT":752}

I would want to sum all of the number values so 50 + 132 + 752 = 934

What I have tried: col = column name

SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX (':', col))) + 1, LEN(col) - LEN(LEFT(col, 
    CHARINDEX (':', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('}', Benefit))) - 1)    
FROM table

This works to grab the first value (so 50.0) in the above example, but will not grab each value. Any idea how I can make this query grab multiple values and then sum them together?

>Solution :

The content is almost a valid JSON, so you may try to fix it and parse it with built-in JSON support using OPENJSON() (a valid JSON content is [{"DBA":50.0},{"RST":132.0},{"ZIT":752}]):

SELECT 
  t.[Column],
  [Sum] = (
     SELECT SUM(CONVERT(numeric(10, 1), j2.value)) 
     FROM OPENJSON(CONCAT('[', t.[Column], ']')) j1
     CROSS APPLY OPENJSON(j1.[value]) j2
  )
FROM (VALUES
   ('{"DBA":50.0},{"RST":132.0},{"ZIT":752}')
) t ([Column])