Reading Json File

Question

--Here I use the below script to read the .json file But finally, i have got the result jobTitle with data as null

DECLARE @json VARCHAR(MAX)

SELECT @json = BulkColumn
FROM OPENROWSET(BULK 'D:\DBBBACKUP\Jobtitle.json', SINGLE_cLOB) AS j

SELECT @json

SELECT ISJSON(@json )

---Here file size is 320kb

SELECT Jobtitle
FROM OPENJSON(@json ) WITH (Jobtitle VARCHAR(MAX) '$.Jobtitle')

Answer 1

Hi,

>SELECT Jobtitle FROM OPENJSON(@json ) WITH (Jobtitle VARCHAR(MAX) '$.Jobtitle')

The '$.Jobtitle' is the problem, the Jobtitle key may not exist, or there may be an sequence problem. It is difficult to judge without looking at your json file.

It is recommended that you check the following documents to learn more about OPENJSON.
https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15
https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15#convert-json-collections-to-a-rowset