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

Question

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)
);

No repro, the code works - it updates all rows in the table, assuming there are any. I'm pretty sure that's not what you want. You should probably use INSERT instead, or add a WHERE clause that identifies the correct row to update — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 2, 2019 at 8:01
OPENJSON returns one row with your JSON example. What does exactly @JSON contain after OPENROWSET() call (what does PRINT @JSON output)? — Zhorov
– Zhorov, Commented Aug 2, 2019 at 8:04
@Zhorov It prints out the Content in the JSON File however I see it stops accepting the content after a specific line. Why would this be? — GrahamNorton
– GrahamNorton, Commented Aug 2, 2019 at 8:27

Zhorov · Accepted Answer · 2019-08-13 07:23:00Z

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

DarkRob · Accepted Answer · 2019-08-02 07:46:08Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related