SQL JSON WORK

json.sql

SET NOCOUNT ON 

DECLARE @json NVARCHAR(MAX) =
'{ 
  "reading": {
      "source": "A01", 
      "values": [
          { "date": "2022-12-12", "value": 123.456 }
        , { "date": "2022-12-13", "value": 234.567 }
      ]
    } 
}' 
SET @json = REPLACE(@json, CHAR(10), '')
SET @json = REPLACE(@json, CHAR(13), '')
SET @json = REPLACE(@json, ' ', '')

SELECT ISJSON(@json) AS ISJSON, @json AS JSON 

DECLARE @data TABLE
(
    [Source] VARCHAR(5) 
  , [Date] DATE
  , [Value] DECIMAL(6, 3)
);

INSERT INTO @data
SELECT 
    [Source]
  , [Date]
  , [Value]
FROM OPENJSON(@json) WITH 
(
    [Source] VARCHAR(5) '$.reading.source'
  , [Values] NVARCHAR(MAX) '$.reading.values' AS JSON
)
OUTER APPLY OPENJSON([Values]) WITH
(
    [Date] DATE '$.date'
  , [Value] REAL '$.value'
)

DECLARE @body NVARCHAR(MAX) = 
(
  SELECT DISTINCT
      [A].[Source] 'source'
    , [values].[Date] 'date'
    , [values].[Value] 'value'
  FROM @data A 
  JOIN @data [values] 
    ON A.Source = [values].Source
  FOR JSON AUTO
  , WITHOUT_ARRAY_WRAPPER 
  , INCLUDE_NULL_VALUES
)

DECLARE @new_json NVARCHAR(MAX) = 
(
  SELECT 
    JSON_QUERY(@body) 'reading'
  FOR JSON PATH
  , WITHOUT_ARRAY_WRAPPER 
)

IF (@json = @new_json)
BEGIN
  SELECT 'Success' AS TEST
END
ELSE
BEGIN
  SELECT 'Fail' AS TEST
END

SELECT JSON_PATH_EXISTS(@new_json, '$.reading.source') AS JSONPATHEXISTS

SET @new_json = JSON_MODIFY(@new_json, '$.reading.source', 'Jerry Nixon')

SELECT @new_json AS JSONMODIFY

SELECT JSON_VALUE(@new_json, '$.reading.source') AS JSONVALUE

Right now, result is missing "reading":

{
  "source": "A01",
  "values": [
    {
      "date": "2022-12-12",
      "value": 123.456
    },
    {
      "date": "2022-12-13",
      "value": 234.567
    }
  ]
}