OPENJSON Convert Value Column to Multiple Rows does not work

Question

I have a JSON file with a simple structure. I try to extract data into rows out of this JSON File.

The JSON File starts with:

[{"result":
[{"country":"Germany",
  "parent":"xxxx",
"city":"Reitbrook",
"latitude":"",

I tried this code, all successfully. Look at the last 3 statement and their results. I would Expect multiple records at the SELECT last statement. What am I doing wrong?

DECLARE @details VARCHAR(MAX)

Select @details  =BulkColumn FROM OPENROWSET 
 (BULK 'folder/cmn_location',   DATA_SOURCE='blogstorage',    SINGLE_CLOB) as JSON;

IF (ISJSON(@details) = 1)
    BEGIN  PRINT 'Imported JSON is Valid'     END
ELSE
    BEGIN  PRINT 'Invalid JSON Imported'      END



SELECT @details as SingleRow_Column

--delivers one row Where

--SingleRow_Column=[{"result":[{country":"Germany","parent":.....


SELECT * FROM OPENJSON(@details, '$')

--delivers one row. Where 

--Key=0, value={"result":[{"country":"Germany","parent":"xxx".....


SELECT * FROM OPENJSON(@details, '$.result')

--delivers no row at all

Now error messages, but just no data

Shnugo · Answer 1 · 2019-09-27T08:30:33.527

Try it like this

Hint: I had to add some closing brackets...

DECLARE @YourJSON NVARCHAR(MAX)=
N'[{"result":
[{"country":"Germany",
  "parent":"xxxx",
"city":"Reitbrook",
"latitude":""}]}]';

SELECT B.*
FROM OPENJSON(@YourJson)        WITH(result NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.result)  WITH(country NVARCHAR(1000)
                                    ,parent NVARCHAR(1000)
                                    ,city NVARCHAR(1000) ) B;

The idea in short:

Your JSON is an array, containing at least one object result. (There might be more objects, but you did not show enough).

This object result is an array itself. Therefore we use the WITH in combination with AS JSON and another APPLY OPENJSON using the nested array returned as A.result.

OPENJSON Convert Value Column to Multiple Rows does not work

1 Answers1