Change Datatype of json_value during select into so I can sum column

Question

I have a column in a table that is json. It contains several columns within it.

Example:

Row1: "sTCounts":[{"dpsTypeTest":"TESTTRIAL","cnt":3033244.0}
Row2: "sTCounts":[{"dpsTypeTest":"TESTTRIAL","cnt":3.3}

I need to sum the cnt value for all rows in table. For instance, the above would produce a result of 3033247.3

I'm not familiar with stored procs enough to master. I thought the easiest route would be to create a temp table and extract the value into a column, and then write a query to sum the column values.

The problem is that it creates a column with datatype nvarchar(4000). It won't let me sum that column. I thought of changing the datatype but not sure how. I am trying CAST without luck.

 select CAST(json AS varchar) AS JSON_VALUE(jsontext, 
'$.sTCounts.cnt') AS PerfCount, TitleNumber 
INTO dbo_Testing_Count0
from PerformanceTest

select sum(PerfCount)
from dbo_Testing_Count
Group by PerfCount

The error message is:

Incorrect syntax near 'jsontext'.

Any ideas? I am open to another method to sum the column or changing the datatype whichever the experts can aid on. I appreciate it.

The error is at JSONTEXT, not your SUM. You have CAST(json AS varchar) AS JSON_VALUE(jsontext, '$.serviceTierCounts.cnt') AS PerformanceCount, which makes little sense. It looks like you forgot the alias after the first AS. Also, never declare a varchar and not declare it's length. — Thom A
– Thom A ♦, Commented Sep 6, 2019 at 14:26
Also, if '$.serviceTierCounts.cnt' returns a integer value, either CAST/CONVERT it in the SELECT or use OPENJSON with a WITH to define the structure of your JSON data. — Thom A
– Thom A ♦, Commented Sep 6, 2019 at 14:34
Thanks. I'll work on these and if I figure it out, post for others. Appreciate it — Kat Isenberg
– Kat Isenberg, Commented Sep 6, 2019 at 15:02

Gottfried Lesigang · Accepted Answer · 2019-09-06 16:58:00Z

The JSON you provide in your question is not valid... This seems to be just a fragment of a larger JSON. As your data starts with a [ you have to think of it as an array, so the simple json path '$.serviceTierCounts.cnt' won't work probably...

Try this, I've added the opening { and the closing brackets at the end:

DECLARE @mockupTable TABLE(ID INT IDENTITY, YourJson NVARCHAR(MAX));
INSERT INTO @mockupTable VALUES
 (N'{"serviceTierCounts":[{"dpsType":"TRIAL","cnt":3033244.0}]}')
,(N'{"serviceTierCounts":[{"dpsType":"TRIAL","cnt":3.3}]}');

--You can read one scalar value using JSON_VALUE directly with a cast. But in this case I need to add [0]. This will tell the engine to read the first (zero-based index!) object's cnt property.

SELECT CAST(JSON_VALUE(YourJson,'$.serviceTierCounts[0].cnt') AS DECIMAL(14,4))
FROM @mockupTable

--But I think, that it's this what you are looking for:

SELECT *
FROM @mockupTable 
CROSS APPLY OPENJSON(YourJson,'$.serviceTierCounts')
WITH(dpsType varchar(100)
    ,cnt decimal(14,4));

The WITH clause will return the object in typed columns side-by-side.

For easy proceeding, you can wrap this as a CTE and continue with the set in the following SELECT.

Collectives™ on Stack Overflow

Change Datatype of json_value during select into so I can sum column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related