CODEWITHSUNDEEP
jsonsql-server
GrahamNorton
GrahamNorton

Reputation: 121

Receiving 'NULL' return from OPENJSON() when reading JSON data

I need to pull the data from a seperate JSON File into my SQL table however , I cant seem to come right with a result. It keeps returning 'NULL' and I dont quite understand why - I suspect it is the JSON path listed in the OPENJSON() but cant seem to come right.

JSON

{
    "isMoneyClient": false,
    "showPower": true,
    "removeGamePlay": true,
    "visualTweaks": {
      "0": {
        "value": true,
        "name": "Clock"
      },
      "1": {
        "value": true,
        "name": "CopperIcon"
      }
   }
}

SQL

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

UPDATE dbo.CommonBaseline
SET CommonBaseline.Config_isMoneyClient =  isMoneyClient ,
    CommonBaseline.Config_showPower =  showPower , 
    CommonBaseline.Config_removeGamePlay =  removeGamePlay
     FROM OPENJSON (@JSON)
        WITH ( 
            isMoneyClient Varchar (50),
            showPower Varchar (50),
            removeGamePlay Varchar (50)
            )

AS REQUESTED I HAVE BELOW THE COMMON BASELINE SCHEME

CREATE TABLE CommonBaseline (
    ServerID int NOT NULL PRIMARY KEY,
    Config_isMoneyClient varchar(255) ,
    Config_showPower varchar(255) ,
    Config_removeGamePlay varchar(255)
);

Upvotes: 1

Views: 6579

Answers (2)

Zhorov
Zhorov

Reputation: 29943

Update:

This is an attempt to improve this answer. When you miss a column path definition in OPENJSON() call using WITH clause, matches between the keys in JSON expression and the column names are case sensitive. This is another possible reason for unexpected NULL results, because you don't use column paths in your OPENJSON() call.

Example:

DECLARE @json nvarchar(max) = N'{
    "isMoneyClient": false,
    "showPower": true,
    "removeGamePlay": true,
    "visualTweaks": {
      "0": {
        "value": true,
        "name": "Clock"
      },
      "1": {
        "value": true,
        "name": "CopperIcon"
      }
   }
}'

-- NULL results. 
-- The column names (IsMoneyClient) and 
-- the keys in JSON expression (isMoneyClient) don't match.
SELECT *
FROM OPENJSON (@json) WITH ( 
   IsMoneyClient Varchar (50),
   ShowPower Varchar (50),
   RemoveGamePlay Varchar (50)
)

-- Expected results. The correct column paths are used
SELECT *
FROM OPENJSON (@json) WITH ( 
   IsMoneyClient Varchar(50) '$.isMoneyClient',
   ShowPower Varchar(50) '$.showPower',
   RemoveGamePlay Varchar(50) '$.removeGamePlay'
)

Original answer:

One possible explanation for your unexpected result is the fact, that in some cases, even if your JSON content is invalid, OPENJSON() reads only part of this content. I'm able to reproduce this with the next example:

Statement:

-- JSON
-- Valid JSON is '[{"name": "A"},{"name": "B"}]'
DECLARE @json nvarchar(max) = N'
   {"name": "A"},
   {"name": "B"}
'
-- Read JSON content
SELECT * FROM OPENJSON(@json, '$')
SELECT * FROM OPENJSON(@json, '$') WITH (
   [name] nvarchar(100) '$.name'
)

Output (OPENJSON() reads only {"name": "A"} part of the JSON input):

----------------
key value   type
----------------
name    A   1

----
name
----
A

One solution here is to check your JSON content with ISJSON:

IF ISJSON(@json) = 1 PRINT 'Valid JSON' ELSE PRINT 'Not valid JSON';

If it is possible, try to fix the input JSON:

Statement:

-- JSON
-- Valid JSON is '[{"name": "A"},{"name": "B"}]'
DECLARE @json nvarchar(max) = N'
   {"name": "A"},
   {"name": "B"}
'
-- Read JSON content    
SELECT * FROM OPENJSON(CONCAT('[', @json, ']'), '$') WITH (
   [name] nvarchar(100) '$.name'
)

Output:

---- 
name
---- 
A
B

Upvotes: 2

DarkRob
DarkRob

Reputation: 3833

Please check your table schema of CommonBaseline. Datatype of columns Config_isMoneyClient, Config_showPower, Config_removeGamePlay

As per below scenario it is working fine

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

or 

SELECT @JSON = '{
"isMoneyClient": false,
"showPower": true,
"removeGamePlay": true,
"visualTweaks": {
  "0": {
    "value": true,
    "name": "Clock"
  },
  "1": {
    "value": true,
    "name": "CopperIcon"
   }
 }
}'

declare @CommonBaseline as Table 
( id int,
Config_isMoneyClient bit,
Config_showPower bit,
Config_removeGamePlay bit )

insert into @CommonBaseline ( id ) values ( 1 )  ---- please check your table CommonBaseline as atleast 1 record to update the same

UPDATE @CommonBaseline 
SET Config_isMoneyClient =  isMoneyClient ,
    Config_showPower =  showPower , 
    Config_removeGamePlay =  removeGamePlay
     FROM OPENJSON (@JSON)
        WITH ( 
            isMoneyClient Varchar (50),
            showPower Varchar (50),
            removeGamePlay Varchar (50)
            )
select * from @CommonBaseline

Note: Please check you already have your row in CommonBaseline. Since you are using update.

Upvotes: 0

Related Questions