Selecting two columns from a json file into SQL Server

Question

I'm trying to insert two columns from a json file into a SQL Server table. The problem is that I don't know how to reach the relevant columns in the json file using the FROM OPENJSON() clause.

I want to insert the YYYYMMDD and FN column from the JSON file into the SQL Server table. But only if the date from does not already exists in the table (Weather).

The problem is that this does not work in the SQL code:

OPENJSON(@WEATHER, '$.rows.value.doc')

You can see the JSON and the SQL code below. What am I doing wrong?

JSON file(with two example rows):

{
    "total_rows": 24882,
    "offset": 0,
    "rows": [{
        "id": "FFF271C4-C061-49C4-94D1-F8E067AB8D46",
        "key": "FFF271C4-C061-49C4- 
    94D1-F8E067AB8D46",
        "value": {
            "rev": "1- 
    9853570e768e8b39a0b86ff81faeb86d"
        },
        "doc": {
            "_id": "FFF271C4-C061-49C4-94D1- 
    F8E067AB8D46",
            "_rev": "1- 
 9853570e768e8b39a0b86ff81faeb86d",
            "STN": 275,
            "YYYYMMDD": 19550601,
            "DDVEC": 25,
            "F 
HVEC": 21,
            "FG": 26,
            "FHX": 51,
            "FHXH": 14,
            "FHN": 0,
            "FHNH": 21,
            "TG": 151,
            "TN": 79,
            "TNH": 4,
            "TX": 219,
            "TXH": 12,
            "PG": 10179,
            "PX": 10208,
            "PN": 10155,
            "VVN": 60,
            "VVNH": 4,
            "VVX": 80,
            "VVXH": 16,
            "NG": 6
        }
    },
    {
        "id": "FFF83E49-F3E9-4584-A8DA-54EB0A60ACFB",
        "key": "FFF83E49-F3E9-4584-A8DA-54EB0A60ACFB",
        "value": {
            "rev": "1-c2085207ba02d355cb3a1d4d9f05f0bf"
        },
        "doc": {
            "_id": "FFF83E49-F3E9-4584-A8DA-54EB0A60ACFB",
            "_rev": "1-c2085207ba02d355cb3a1d4d9f05f0bf",
            "STN": 275,
            "YYYYMMDD": 19730725,
            "DDVEC": 301,
            "FHVEC": 41,
            "FG": 46,
            "FHX": 82,
            "FHXH": 15,
            "FHN": 5,
            "FHNH": 3,
            "FXX": 123,
            "FXXH": 14,
            "TG": 135,
            "TN": 45,
            "TNH": 3,
            "TX": 192,
            "TXH": 12,
            "T10N": 31,
            "SQ": 76,
            "SP": 48,
            "PG": 10083,
            "PX": 10107,
            "PN": 10067,
            "VVN": 3,
            "VVNH": 3,
            "VVX": 80,
            "VVXH": 13,
            "NG": 5,
            "UG": 79,
            "UX": 99,
            "UXH": 4,
            "UN": 51,
            "UNH": 12
        }
    }]
}

T-SQL code:

DECLARE @WEATHER VARCHAR(MAX)

SELECT @WEATHER = BulkColumn
FROM OPENROWSET 
(BULK 'C:\WeatherData.json', SINGLE_CLOB) AS j

BEGIN TRY
    IF (ISJSON(@WEATHER)=1)
    BEGIN
        INSERT INTO WEATHER(DATE, DDVEC, LOADDATETIME, SOURCE)
            SELECT  
                J.[DATE], J.DDVEC, CURRENT_TIMESTAMP, 'KN'
            FROM 
                OPENJSON(@WEATHER, '$.rows.value.doc')
            WITH 
                ([DATE] INT      '$.YYYYMMDD',
                 DDVEC  SMALLINT '$.DDVEC') J
            WHERE 
                NOT EXISTS (SELECT DATE FROM WEATHER H 
                            WHERE H.DATE = J.DATE)
    END
    ELSE
        RAISERROR ('Unvalid JSON file', 16, 1);
    END TRY
    BEGIN CATCH
        THROW
    END CATCH

Answer 1

You have error in JSON path. Correct from clause looks like this

from openjson(@WEATHER,'$.rows')--path to array
with (
[date] int '$.doc.YYYYMMDD',--path in array element
ddvec smallint '$.doc.DDVEC'
)