You’ll have to decide if adding this column to an index makes sense in your situation.In this article, we will explore JSON_QUERY() functions in SQL Server to extract JSON objects and array from the Now when I execute the SELECT query, according to the execution plan, it’s doing an index lookup instead of a table scan. ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON The following adds an index on the QB1 computed column: CREATE UNIQUE NONCLUSTERED INDEX ON. This returns the following: Detroit Lions Code language: plaintext ( plaintext ) Add an index on the computed columnīesides making your queries simpler, adding a PERSISTED computed column also makes it so you can include this column in an index, which can improve performance. WHERE QB1 = 'Matthew Stafford' Code language: SQL (Structured Query Language) ( sql ) Now let’s answer this question again: “Which team has Matthew Stafford as the starting quarterback?” SELECT TeamName FROM NFLTeams If you want to simplify your queries, you can add a computed column that extracts out JSON values into a separate column.Īdd the computed column like this: ALTER TABLE ADD QB1 AS JSON_VALUE(People, '$.') PERSISTED Add a computed column so the client code doesn’t have to call JSON_VALUE() At the time of this writing, several teams don’t have General Managers (due to firing them in the middle of the season for poor performance). Note: My sample data only has the Detroit Lions, so it only returns them in this query. WHERE JSON_VALUE(People, '$.GeneralManager') IS NULL Code language: SQL (Structured Query Language) ( sql ) This query answers the question: “Which team doesn’t have a general manager right now?” SELECT TeamName 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |