#Json query in sql server code

#Json query in sql server code

This returns the following: Detroit Lions Code language: plaintext ( plaintext )Īs you can see, you can use JSON_VALUE() in the WHERE clause. WHERE JSON_VALUE(People, '$.') = 'Matthew Stafford' Code language: SQL (Structured Query Language) ( sql )

This query answers the question: “Which team has Matthew Stafford as the starting quarterback?” SELECT TeamName Filter rows based on values in the JSON – JSON_VALUE() in the WHERE clause JSON_QUERY(ColumnWithJSON, PathToJSONObjectYouWant) returns a JSON object. This query answers the question: “Who plays for the Detroit Lions?” SELECT JSON_QUERY(People, '$.Players') JSON_VALUE(ColumnWithJSON, PathToJSONPropertyYouWant) returns a single value. This returns the following value: Sheila Ford Hamp Code language: plaintext ( plaintext ) WHERE TeamName = 'Detroit Lions' Code language: SQL (Structured Query Language) ( sql ) This query answers the question: “Who is the owner of the Detroit Lions?” SELECT JSON_VALUE(People, '$.Owner.Name') To insert it into a table, pass in the JSON string parameter just like you would any other string parameter, like this: INSERT INTO Code language: SQL (Structured Query Language) ( sql ) Get a single value with JSON_VALUE() 'Notes': 'Promoted from OC to interim HC when Lions fired Matt Patricia' },Ĭode language: JSON / JSON with Comments ( json ) I have the following table definition: CREATE TABLE dbo.(Ĭode language: SQL (Structured Query Language) ( sql ) You can store it in a nvarchar column (specify a size or use MAX). Add a computed column so the client code doesn’t have to call JSON_VALUE().Filter rows based on values in the JSON – JSON_VALUE() in the WHERE clause.