CODEWITHSUNDEEP
jsonsql-server
GrahamNorton
GrahamNorton

Reputation: 121

SQL Server OPENROWSET not pulling JSON Data

I am trying so save the JSON file Data 'WhitelistedOrigins' in a SQL table however I keep receiving NULL Entries. I have used the same method to import attributes into a table and this has worked before although the JSON was formatted differently

JSON

"Client": {
"whiteListedOrigins": [
      "file://",
      "https://mobile.gtent.eu",
      "https://mobile.assists.co.uk",
      "https://valueadds3.active.eu",
      "https://flash3.active.eu",
      "https://valueadds3.assists.co.uk"
    ]
}

SQL

DECLARE @JSON VARCHAR(MAX)
SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK 'C:\config.json', SINGLE_CLOB) 
AS A

UPDATE dbo.CommonBaseline
SET CommonBaseline.whiteListedOrigins=  whiteListedOrigins
     FROM OPENJSON (@JSON, '$.Client')
        WITH ( 
            whiteListedOrigins Varchar (MAX))

RESULT

enter image description here

Upvotes: 2

Views: 722

Answers (1)

Zhorov
Zhorov

Reputation: 29993

You need to use OPENJSON() with explicit schema and AS JSON option in a column definition.

If you want to return a nested JSON fragment from a JSON property, you have to provide the AS JSON flag. Without this option, if the property can't be found, OPENJSON returns a NULL value instead of the referenced JSON object or array, or it returns a run-time error in strict mode.

Statement:

DECLARE @Json nvarchar(max) = N'{
"Client": {
"whiteListedOrigins": [
      "file://",
      "https://mobile.gtent.eu",
      "https://mobile.assists.co.uk",
      "https://valueadds3.active.eu",
      "https://flash3.active.eu",
      "https://valueadds3.assists.co.uk"
    ]
}
}'

SELECT *
FROM OPENJSON (@JSON, '$.Client') WITH ( 
   whiteListedOrigins nvarchar (MAX) AS JSON
)

Output:

--------------------
whiteListedOrigins
--------------------
[
      "file://",
      "https://mobile.gtent.eu",
      "https://mobile.assists.co.uk",
      "https://valueadds3.active.eu",
      "https://flash3.active.eu",
      "https://valueadds3.assists.co.uk"
    ]

Notes:

Your JSON (without surrounding { and }) is not valid.

Upvotes: 3

Related Questions