Get value from a string as JSON data with TS-SQL in MSSQL (using JSON_QUERY or JSON_VALUE)

json-query-json-value-sqlserver.sql

--------------------------------------------------------------------------------
-- Example JSON_QUERY JSON_VALUE
--------------------------------------------------------------------------------
DECLARE @data VARCHAR(4000)
SET @data=N'[
				{
					"Name": "Yoda",
					"Id": "001"
				},
				{
					"Name": "Obi-Wan Kenobi",
					"Id": "002"
				},
				{
					"Name": "Luke Skywalker",
					"Id": "003"
				}
			]'

SELECT 
	JSON_QUERY(@data, '$[1]') AS 'JediDataJson',
	JSON_VALUE(@data, '$[1].Name') AS 'Name';

JSON_QUERY: Extracts an object or an array from a JSON string.
https://learn.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql
JSON_VALUE: Extracts a scalar value from a JSON string
https://learn.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql
Compare JSON_VALUE and JSON_QUERY
https://learn.microsoft.com/en-us/sql/relational-databases/json/validate-query-and-change-json-data-with-built-in-functions-sql-server?view=sql-server-ver16#JSONCompare