{"id":14661,"date":"2016-01-07T09:30:00","date_gmt":"2016-01-07T17:30:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/"},"modified":"2024-01-22T22:52:28","modified_gmt":"2024-01-23T06:52:28","slug":"json-in-sql-server-2016-part-3-of-4","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/","title":{"rendered":"JSON in SQL Server 2016: Part 3 of 4"},"content":{"rendered":"

Transform JSON Text to Relational Table – OPENJSON<\/h1>\n

OPENJSON is a table-value function (TVF) that looks into JSON text, locates an array of JSON objects, iterates through the elements of the array, and for each element returns one row in the output result.<\/p>\n

\"<\/a><\/p>\n

In the example above, we can specify where to locate the JSON array that should be opened (i.e. in the $.Orders path), what column should be returned as result, and where in the JSON objects values can be located that will be returned as cells.<\/p>\n

OPENJSON can be used in any query that works with data. As an example, we can transform a JSON array in the @orders variable into a set of rows and insert them into a standard table:<\/p>\n

INSERT INTO<\/span> Orders(<\/span>Number,<\/span> Date<\/span>,<\/span> Customer,<\/span> Quantity)<\/span><\/span>
SELECT<\/span> Number,<\/span> Date<\/span>,<\/span> Customer,<\/span> Quantity<\/span>
\u00a0OPENJSON<\/span> (<\/span>@orders)<\/span><\/span>
\u00a0WITH<\/span> (<\/span><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Number varchar<\/span>(<\/span>200),<\/span> <\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Date datetime<\/span>,<\/span><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Customer varchar<\/span>(<\/span>200),<\/span><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Quantity int<\/span><\/span>
\u00a0)<\/span> AS<\/span> OrdersArray<\/span><\/p>\n

Four columns in the result set that is returned by OPENJSON are defined in the WITH clause. OPENJSON will try to find the properties Number, Date, Customer, and Quantity in each JSON object and convert their values into columns in the result set. By default, NULL will be returned if the property is not found. The assumption in the query above is that the @orders variable contains the following JSON array:<\/p>\n

‘[<\/span>
\u00a0\u00a0 {“Number”:1, “Date”: “8\/10\/2012”, “Customer”: “Adventure works”, “Quantity”: 1200},<\/span>
\u00a0\u00a0 {“Number”:4, “Date”: “5\/11\/2012”, “Customer”: “Adventure works”, “Quantity”: 100},<\/span>
\u00a0\u00a0 {“Number”:6, “Date”: “1\/3\/2012”, “Customer”: “Adventure works”, “Quantity”: 250},<\/span>
\u00a0\u00a0 {“Number”:8, “Date”: “12\/7\/2012”, “Customer”: “Adventure works”, “Quantity”: 2200}<\/span>
]’<\/span><\/p>\n

As you can see, the transformation from a JSON text to a relational form is simple. You just need to specify column names and types and OPENJSON will find properties in JSON that match these columns. In this example, plain JSON is used; however, OPENJSON can handle any nested\/hierarchical structure of JSON objects.<\/p>\n

Also, OPENJSON can be used to combine relational and JSON data in the same query. If we assume that the JSON array shown in the previous example is stored in the Orders column, the following query can combine the columns and JSON fields:<\/p>\n

SELECT<\/span> Id,<\/span> FirstName,<\/span> LastName,<\/span> Number,<\/span> Date<\/span>,<\/span> Customer,<\/span> Quantity<\/span>
\u00a0FROM<\/span> Person<\/span>
\u00a0\u00a0\u00a0 CROSS APPLY<\/span> OPENJSON<\/span> (<\/span>OrdersJson)<\/span><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WITH<\/span> (<\/span><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Number varchar<\/span>(<\/span>200), <\/span><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Date datetime<\/span>,<\/span><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Customer varchar<\/span>(<\/span>200),<\/span><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Quantity int<\/span> )<\/span> AS<\/span> OrdersArray<\/span><\/p>\n

OPENJSON will open an array in each cell and return one row for each JSON object (i.e. element) in the array. CROSS APPLY OPENJSON syntax is used to \u201cjoin\u201d rows in the table with the child inner table that will be materialized from a JSON array in the JSON cell.<\/p>\n

Indexing JSON data<\/h1>\n

Although values in JSON are formatted as text, you can index them the same way as any other values in table columns. You can use either standard NON CLUSTERED or full text search indexes.<\/p>\n

If you want to create an index on some JSON property that is frequently used in queries, you can create a non-persisted computed column that references the value and creates a standard index on that column. In the following example, we will optimize queries that filter rows using the $.Company property in the InfoJSON column:<\/p>\n

ALTER TABLE<\/span> Person<\/span>
ADD<\/span> vCompany AS<\/span> JSON_VALUE<\/span>(<\/span>InfoJSON,<\/span> ‘<\/span>$.Company’<\/span>)<\/span><\/span><\/p>\n

CREATE INDEX<\/span> idx_Person_1<\/span>
\u00a0\u00a0\u00a0 ON<\/span> Person(<\/span>vCompany)<\/span><\/span><\/p>\n

As you can see, SQL Server provides a hybrid model where you can put values from JSON either in key or included columns and uses both JSON values and standard columns in the same index.<\/p>\n

Since JSON is regular text, you can use standard full text index. Full text indexes can be created on arrays of values. You create a full text index on a column that contains a JSON array, or you can create a computed column that references some array in the existing column and create a full text search index on that column:<\/p>\n

ALTER TABLE<\/span> Person<\/span>
ADD<\/span> vEmailAddresses AS<\/span> JSON_QUERY<\/span>(<\/span>InfoJSON,<\/span> ‘$.Contact.Emails’)<\/span><\/span><\/p>\n

CREATE FULLTEXT INDEX ON<\/span> Person(<\/span>vEmailAddresses)<\/span><\/span>
\u00a0\u00a0\u00a0 KEY INDEX<\/span> PK_Person_ID ON<\/span> jsonFullTextCatalog;<\/span><\/p>\n

Full text index is useful if you need to optimize queries that try to find rows where the JSON array contains some value:<\/p>\n

SELECT<\/span> PersonID,<\/span> FirstName,<\/span>LastName,<\/span>vEmailAddresses<\/span>
FROM<\/span> Person<\/span>
WHERE<\/span> CONTAINS<\/span>(<\/span>vEmailAddresses,<\/span> ‘john@mail.microsoft.com’<\/span>)<\/span><\/span><\/p>\n

This query will return Person rows where the email array contains the value \u2018john@mail.microsoft.com.\u2019 Full text index doesn\u2019t have any special rules for parsing JSON. It will split a JSON array using separators (i.e. double quotes, commas, brackets) and index values in an array. Full text index is applicable on arrays of numbers and simple string values. If you have more complex objects in a JSON array, a full text index cannot be directly applied because the system does not know the difference between keys and values.<\/p>\n

As you can see, the same indexing methods are used both on JSON values and relational columns.<\/p>\n

Check out the other posts in this four-part series in the links below (as they become available), or\u00a0learn more in the SQL Server 2016 blogging series<\/a>.<\/p>\n

JSON in SQL Server 2016: Part 1 of 4<\/a><\/p>\n

JSON in SQL Server 2016: Part 2 of 4<\/a><\/p>\n

JSON in SQL Server 2016: Part\u00a04 of 4<\/a><\/p>\n

\"Try<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Transform JSON Text to Relational Table – OPENJSON OPENJSON is a table-value function (TVF) that looks into JSON text, locates an array of JSON objects, iterates through the elements of the array, and for each element returns one row in the output result. In the example above, we can specify where to locate the JSON<\/p>\n","protected":false},"author":1457,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ep_exclude_from_search":false,"_classifai_error":"","footnotes":""},"post_tag":[],"product":[5227,2403],"content-type":[2424],"topic":[],"coauthors":[],"class_list":["post-14661","post","type-post","status-publish","format-standard","hentry","product-sql","product-sql-server-2016","content-type-best-practices","review-flag-1593580770-633","review-flag-1593580427-503","review-flag-1-1593580431-15","review-flag-2-1593580436-981","review-flag-3-1593580441-293","review-flag-4-1593580446-456","review-flag-5-1593580452-31","review-flag-6-1593580457-144","review-flag-7-1593580462-294","review-flag-8-1593580467-480"],"yoast_head":"\nJSON in SQL Server 2016: Part 3 of 4 - Microsoft SQL Server Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"JSON in SQL Server 2016: Part 3 of 4 - Microsoft SQL Server Blog\" \/>\n<meta property=\"og:description\" content=\"Transform JSON Text to Relational Table – OPENJSON OPENJSON is a table-value function (TVF) that looks into JSON text, locates an array of JSON objects, iterates through the elements of the array, and for each element returns one row in the output result. In the example above, we can specify where to locate the JSON\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/\" \/>\n<meta property=\"og:site_name\" content=\"Microsoft SQL Server Blog\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/sqlserver\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-07T17:30:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-23T06:52:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/03\/OPENJSON.png\" \/>\n<meta name=\"author\" content=\"SQL Server Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SQLServer\" \/>\n<meta name=\"twitter:site\" content=\"@SQLServer\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"SQL Server Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 min read\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/\"},\"author\":[{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/author\/sql-server-team\/\",\"@type\":\"Person\",\"@name\":\"SQL Server Team\"}],\"headline\":\"JSON in SQL Server 2016: Part 3 of 4\",\"datePublished\":\"2016-01-07T17:30:00+00:00\",\"dateModified\":\"2024-01-23T06:52:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/\"},\"wordCount\":843,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/03\/OPENJSON.png\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/\",\"name\":\"JSON in SQL Server 2016: Part 3 of 4 - Microsoft SQL Server Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/03\/OPENJSON.png\",\"datePublished\":\"2016-01-07T17:30:00+00:00\",\"dateModified\":\"2024-01-23T06:52:28+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#primaryimage\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/03\/OPENJSON.png\",\"contentUrl\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/03\/OPENJSON.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"JSON in SQL Server 2016: Part 3 of 4\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\",\"name\":\"Microsoft SQL Server Blog\",\"description\":\"Official News from Microsoft\u2019s Information Platform\",\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\",\"name\":\"Microsoft SQL Server Blog\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png\",\"contentUrl\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png\",\"width\":259,\"height\":194,\"caption\":\"Microsoft SQL Server Blog\"},\"image\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"http:\/\/www.facebook.com\/sqlserver\",\"https:\/\/x.com\/SQLServer\",\"https:\/\/www.youtube.com\/user\/MSCloudOS\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"JSON in SQL Server 2016: Part 3 of 4 - Microsoft SQL Server Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/","og_locale":"en_US","og_type":"article","og_title":"JSON in SQL Server 2016: Part 3 of 4 - Microsoft SQL Server Blog","og_description":"Transform JSON Text to Relational Table – OPENJSON OPENJSON is a table-value function (TVF) that looks into JSON text, locates an array of JSON objects, iterates through the elements of the array, and for each element returns one row in the output result. In the example above, we can specify where to locate the JSON","og_url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/","og_site_name":"Microsoft SQL Server Blog","article_publisher":"http:\/\/www.facebook.com\/sqlserver","article_published_time":"2016-01-07T17:30:00+00:00","article_modified_time":"2024-01-23T06:52:28+00:00","og_image":[{"url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/03\/OPENJSON.png"}],"author":"SQL Server Team","twitter_card":"summary_large_image","twitter_creator":"@SQLServer","twitter_site":"@SQLServer","twitter_misc":{"Written by":"SQL Server Team","Est. reading time":"3 min read"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#article","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/"},"author":[{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/author\/sql-server-team\/","@type":"Person","@name":"SQL Server Team"}],"headline":"JSON in SQL Server 2016: Part 3 of 4","datePublished":"2016-01-07T17:30:00+00:00","dateModified":"2024-01-23T06:52:28+00:00","mainEntityOfPage":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/"},"wordCount":843,"commentCount":0,"publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization"},"image":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#primaryimage"},"thumbnailUrl":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/03\/OPENJSON.png","inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/","name":"JSON in SQL Server 2016: Part 3 of 4 - Microsoft SQL Server Blog","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#primaryimage"},"image":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#primaryimage"},"thumbnailUrl":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/03\/OPENJSON.png","datePublished":"2016-01-07T17:30:00+00:00","dateModified":"2024-01-23T06:52:28+00:00","breadcrumb":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#primaryimage","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/03\/OPENJSON.png","contentUrl":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/03\/OPENJSON.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2016\/01\/07\https://www.microsoft.com/json-in-sql-server-2016-part-3-of-4\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/"},{"@type":"ListItem","position":2,"name":"JSON in SQL Server 2016: Part 3 of 4"}]},{"@type":"WebSite","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/","name":"Microsoft SQL Server Blog","description":"Official News from Microsoft\u2019s Information Platform","publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization","name":"Microsoft SQL Server Blog","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png","contentUrl":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png","width":259,"height":194,"caption":"Microsoft SQL Server Blog"},"image":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/"},"sameAs":["http:\/\/www.facebook.com\/sqlserver","https:\/\/x.com\/SQLServer","https:\/\/www.youtube.com\/user\/MSCloudOS"]}]}},"_links":{"self":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts\/14661"}],"collection":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/users\/1457"}],"replies":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/comments?post=14661"}],"version-history":[{"count":0,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts\/14661\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/media?parent=14661"}],"wp:term":[{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/post_tag?post=14661"},{"taxonomy":"product","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/product?post=14661"},{"taxonomy":"content-type","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/content-type?post=14661"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/topic?post=14661"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/coauthors?post=14661"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}