MSSQL Query JSON displays Null value

Question

I have a table PublicRelations with a column called Students in a SQL Server database called Subjects.

[
    { "Label": "Name",     "ColumnValue": "Trudie" },      
    { "Label": "Class",    "ColumnValue": "PublicRelations" },      
    { "Label": "Room",     "ColumnValue": "8049" },      
    { "Label": "HttpPath", "ColumnValue": "https://www.google.com/" }
]

I only get NULL when I run the below query using the Json_value. I'd like to get it to display the value from the array. I believe this may have to do with the 4000 character limit?

SELECT      [StuduentID], 
            [Students],
            --JSON_VALUE([Students],'$.ColumnValue') AS Name --Only returns NULL
FROM        [Subjects].[dbo].[PublicRelations] c
CROSS APPLY OPENJSON(c.Students)
  WITH (    Name int '$.Name',
            Value nvarchar(255) '$.ColmunValue'
  ) AS      jsonValues
WHERE       jsonValues.ColumnValue = 'Trudie'

The query works and I can find what I need, but again, I only get NULL when I want to display that part of the JSON column in my results.

Yes on original; I retyped it all over in a hurry to practice typing; as stated that works; the issue I want to resolve is how to display the individual values from the json array in my tsql results — user3191894
– user3191894, Commented May 8, 2020 at 19:42
TBH I'm not sure what you are trying to do. Apart from the misspelling you also seem to be selecting a property that does not exist. The JSON only has 2 properties and neither of them are called Name — Martin Smith
– Martin Smith, Commented May 8, 2020 at 20:12

Zhorov · Accepted Answer · 2020-05-11 14:13:10Z

1

The statement is wrong and you has the following issues (as @MartinSmith already mentioned):

Syntax error - '$.ColmunValue' should be '$.ColumnValue'.
Wrong schema definition (the WITH clause) - I can't see Name key in the input JSON.
Wrong use of JSON_VALUE() - this function extracts scalar value from a JSON string, so JSON_VALUE([Students],'$.ColumnValue') returns NULL with this JSON input in lax mode.

You may try with the following statement (based on the statement in the question):

Table:

CREATE TABLE PublicRelations (
   StudentID int,
   Students nvarchar(1000))
INSERT INTO PublicRelations (StudentID, Students) 
VALUES (1, N'[
    { "Label": "Name",     "ColumnValue": "Trudie" },      
    { "Label": "Class",    "ColumnValue": "PublicRelations" },      
    { "Label": "Room",     "ColumnValue": "8049" },      
    { "Label": "HttpPath", "ColumnValue": "https://www.google.com/" }
]')

Statement:

SELECT p.StudentID, j.*
FROM [PublicRelations] p
CROSS APPLY OPENJSON(p.Students) WITH (
   Name nvarchar(50) '$.Label',
   Value nvarchar(255) '$.ColumnValue'
) j
WHERE EXISTS (
   SELECT 1 
   FROM OPENJSON(p.Students) WITH (Value nvarchar(255) '$.ColumnValue')
   WHERE Value = N'Trudie'
) AND (j.Name IN ('Name', 'Class', 'Room'))

Result:

StudentID   Name     Value
1           Name     Trudie
1           Class    PublicRelations
1           Room     8049

edited May 11, 2020 at 14:13

answered May 9, 2020 at 5:43

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3191894 Over a year ago

Thanks, I want to query on name but also show the values; in my sample there are 4 but there are a total of over 25 Keys i.e. firstname lastname etc... So in your sample, If search Trudy, I see: StudentID = 1; Name = Name; Value = Trudie, I want to Also add Room as a results display column but not ALL the columns

Zhorov Over a year ago

@user3191894 I understand, you need an appropriate WHERE clause. The answer is updated. Thanks.

user3191894 Over a year ago

Beautiful! Thank You!

Collectives™ on Stack Overflow

MSSQL Query JSON displays Null value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related