SQL Server parse JSON to update another table

Question

I have a table with JSON data in one of the columns and i'm trying to parse the JSON data and insert into a temp table

DECLARE @TEMPTABLE
( 
     ID INT, 
     Status NVARCHAR(50), 
     Cost DECIMAL(20, 0)
)


INSERT INTO @TEMPTABLE
    SELECT 
        ID, 
        JSON_VALUE(mydata, '$.Status') AS Status,
        JSON_VALUE(mydata, '$.Cost') AS Cost 
    FROM Expense

I get this error:

Error Converting data type nvarchar to numeric

The same works fine if I comment out the Cost column.

Sample JSON data in Cost table

 | ID | mydata 
 +----+-------------------------------------
 | 1  | {"Status":"Shipped","Cost":"$10.50"}

Just curious why decimal(20,0) and not decimal(20,2)

John Cappelletti
– John Cappelletti

2020-12-17 17:24:57 +00:00
Commented Dec 17, 2020 at 17:24 — John Cappelletti
– John Cappelletti, Commented Dec 17, 2020 at 17:24

John Cappelletti · Accepted Answer · 2020-12-17 17:16:42Z

5

You can convert the value to MONEY. It is a little more forgiving than decimal()

Example

Declare @Expense Table ([ID] int,mydata varchar(50))
Insert Into @Expense Values 
 (1,'{"Status":"Shipped","Cost":"$10.50"}')
 
SELECT ID
      ,JSON_VALUE(mydata,'$.Status') as Status
      ,try_convert(money,JSON_VALUE(mydata,'$.Cost'))  as Cost 
 FROM @Expense

Returns

ID  Status  Cost
1   Shipped 10.50

answered Dec 17, 2020 at 17:16

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RData Over a year ago

Thanks but the result comes out as $10 and not $10.50

John Cappelletti Over a year ago

@RData As I commented in your question, your table variable has decimal(20,0) not decimal(20,2) The (20,0) is zero decimal places

Hector Diaz Contreras · Accepted Answer · 2020-12-18 20:15:04Z

-1

You probably need to cast the string into a decimal value, give the following a try:

DECLARE @TEMPTABLE
( ID INT , Status nvarchar(50), Cost DECIMAL(20,0))


INSERT INTO @TEMPTABLE
(
    ID,
    Status,
    Cost
)
SELECT 
    ID, 
    JSON_VALUE(mydata,'$.Status') AS [Status],
    TRY_CAST(REPLACE(JSON_VALUE(mydata,'$.Cost'), '$', '') AS DECIMAL(20,0)) AS [Cost]
FROM Expense

edited Dec 18, 2020 at 20:15

answered Dec 17, 2020 at 17:15

Hector Diaz Contreras

444 bronze badges

2 Comments

RData Over a year ago

Returning NULL value

Hector Diaz Contreras Over a year ago

Sorry about that, I forgot to remove the "$" money symbol from the string before the cast. It should work now.

Collectives™ on Stack Overflow

SQL Server parse JSON to update another table

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related