OPENJSON syntax error

Question

I have a problem while using the below

ALTER PROCEDURE [SaveGlobalFines]
@JSON NVARCHAR(MAX)
AS
BEGIN
  DECLARE @ChargeID       INT
  DECLARE @FineTypeID       INT
  DECLARE @AmountDB       MONEY
  DECLARE @AmountJSON     MONEY
  DECLARE @userID       INT
  DECLARE @Counter      INT


SELECT @ChargeID=Fine.Charge_Id, @FineTypeID=Fine.FineType_Id, @AmountDB = Fine.Amount , @AmountJSON = JSON.Amount, @userID = JSON.user_id
   FROM Fine  
   CROSS APPLY OPENJSON(@JSON,'$.amounts') 
   WITH(Charge_Id int, Amount money, FineType_Id int, user_id int)AS JSON
   WHERE Fine.Charge_Id =JSON.Charge_Id and Fine.FineType_Id= JSON.FineType_Id and Fine.DateExpired IS NULL


     IF @ChargeID IS NULL AND @FineTypeID IS NULL  --if fine type and charge id are returned null insert new row
     BEGIN
       INSERT INTO  Fine (Charge_Id,Amount,FineType_Id,user_id)
       SELECT *
       FROM OPENJSON(@JSON,'$.amounts') 
       WITH(Charge_Id int , Amount money, FineType_Id int, user_id int )AS JSON
     END 
    ELSE-- Check if amount is the same if yes do not update current record and enter a new one
      IF @AmountDB <> @AmountJSON
       BEGIN
          UPDATE Fine set DateExpired= GETDATE(), ExpiryDescription ='Updated by'+ CONVERT(NVARCHAR(10), JSON.user_id)
            FROM OPENJSON(@JSON,'$.amounts') 
            WITH(Charge_Id int , Amount money, FineType_Id int, user_id int )AS JSON
            WHERE Fine.Charge_Id =JSON.Charge_Id and Fine.FineType_Id=JSON.FineType_Id

            INSERT INTO  Fine (Charge_Id,Amount,FineType_Id,user_id)
            SELECT *
            FROM OPENJSON(@JSON,'$.amounts') 
            WITH(Charge_Id int, Amount money, FineType_Id int, user_id int)AS JSON
     END
END  

Abviously the vairables example @chargeID is being overwritten by the last element in the json file. This works

SELECT *
FROM OPENJSON(@JSON,'$.amounts[0]') 
WITH(Charge_Id int, Amount money, FineType_Id int, user_id int)AS JSON

I wish to do something like the below but with a counter instead since the JSON file is not always the same. It might have only one element and teh above will work correctly but if it has 4 elements it does not work

SELECT *
FROM OPENJSON(@JSON,'$.amounts[+'@Counter'+]') 
WITH(Charge_Id int, Amount money, FineType_Id int, user_id int)AS JSON

But this fire an error. JSON being passed is like the below

>  "amounts": [
>     {
>       "Charge_Id": "368",
>       "Amount": "800",
>       "FineType_Id": 3,
>       "user_id": 2
>     },
>     {
>       "Charge_Id": "368",
>       "Amount": "600",
>       "FineType_Id": 4,
>       "user_id": 2
>     }   ]

Please help

Answer 1

If I understand your comments correctly, you don't actually want to pull out a specific item from your json, you want to do something to every record that is returned.

If this is correct, you just need to use SQL in the set based way that it was designed. For example, this script returns an additional column that checks if the value in the Amount column is less than 700 or not:

declare @JSON nvarchar(max)
declare @counter int

set @JSON ='{ 
     "amounts": [
 {
  "Charge_Id": "368",
  "Amount": "800",
  "FineType_Id": 3,
  "user_id": 2
 },
{
  "Charge_Id": "368",
  "Amount": "400",
  "FineType_Id": 4,
  "user_id": 2
 }
 ] 
}';

select j.*
        ,case when j.Amount < 700
                then 'Less than £700'
                else 'Not less than £700'
                end as col
from openjson(@JSON,'$.amounts') with (Charge_Id int, Amount money, FineType_Id int, user_id int) as j;

Which outputs:

+-----------+--------+-------------+---------+-------------------+
| Charge_Id | Amount | FineType_Id | user_id |        col        |
+-----------+--------+-------------+---------+-------------------+
|       368 | 800.00 |           3 |       2 | Not less than 700 |
|       368 | 400.00 |           4 |       2 | Less than 700     |
+-----------+--------+-------------+---------+-------------------+

If you are looking to do some update, insert or delete operations based on the json data, you can also do these in the normal SQL set based way.

---

If you need to do some additional filtering on your json data, you can just put a where clause in as you would any other query. Changing your script to the following will only return a row for the records with a FineType_id of 3:

select j.*
        ,case when j.Amount < 700
                then 'Less than 700'
                else 'Not less than 700'
                end as col
from openjson(@JSON,'$.amounts') with (Charge_Id int, Amount money, FineType_Id int, user_id int) as j
where j.FineType_id = 3;