0

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.

Improve this question
3
  • 1
    have you tried it with correct spelling of ColmunValue?
    – Martin Smith
    Commented May 8, 2020 at 19:37
  • 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
    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
    Commented May 8, 2020 at 20:12

1 Answer 1

Reset to default
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
Improve this answer
3
  • 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
    – user3191894
    Commented May 11, 2020 at 13:59
  • @user3191894 I understand, you need an appropriate WHERE clause. The answer is updated. Thanks.
    – Zhorov
    Commented May 11, 2020 at 14:11
  • Beautiful! Thank You!
    – user3191894
    Commented May 11, 2020 at 15:12