{"id":3480,"date":"2024-03-12T16:06:52","date_gmt":"2024-03-12T09:06:52","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3480"},"modified":"2024-03-13T22:36:23","modified_gmt":"2024-03-13T15:36:23","slug":"sql-server-json-functions","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/","title":{"rendered":"SQL Server JSON Functions"},"content":{"rendered":"\n<p>This page provides you with a comprehensive guide to the most commonly used SQL Server JSON functions, providing insights into validating JSON text, creating JSON data, querying JSON objects, and modifying JSON data.<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\" id='json-path-expressions'>Section 1. JSON Path Expressions <a href=\"#json-path-expressions\" class=\"anchor\" id=\"json-path-expressions\" title=\"Anchor for Section 1. JSON Path Expressions\">#<\/a><\/h2>\n\n\n\n<p>This section focuses on navigation JSON structures using JSON paths and shows you how to check if a JSON path exists in a JSON string.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/sql-server-json-path-expressions\/\">JSON Path Expressions in SQL Server<\/a> &#8211; Show how to use JSON path expression to locate elements within a JSON document.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/sql-server-json_path_exists\/\">JSON_PATH_EXISTS()<\/a> &#8211; Determine if a specified JSON path exists in a JSON document.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\" id='validating-json'>Section 2. Validating JSON <a href=\"#validating-json\" class=\"anchor\" id=\"validating-json\" title=\"Anchor for Section 2. Validating JSON\">#<\/a><\/h2>\n\n\n\n<p>This section demonstrates the function of validating JSON documents to ensure their integrity and correctness.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/sql-server-isjson\/\">ISJSON()<\/a> &#8211; Determine if a JSON string is valid, which can be useful in data validation processes.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\" id='constructing-json-objects-and-arrays'>Section 3. Constructing JSON objects and arrays <a href=\"#constructing-json-objects-and-arrays\" class=\"anchor\" id=\"constructing-json-objects-and-arrays\" title=\"Anchor for Section 3. Constructing JSON objects and arrays\">#<\/a><\/h2>\n\n\n\n<p>This section introduces JSON functions for constructing JSON structures, including objects and arrays.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/sql-server-json_object\/\">JSON_OBJECT()<\/a> &#8211; construct JSON object text from zero or more key\/value pairs.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/sql-server-json_array\/\">JSON_ARRAY()<\/a> &#8211; construct JSON array text from zero or more values.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\" id='querying-json-elements'>Section 4. Querying JSON elements <a href=\"#querying-json-elements\" class=\"anchor\" id=\"querying-json-elements\" title=\"Anchor for Section 4. Querying JSON elements\">#<\/a><\/h2>\n\n\n\n<p>This section explores techniques for extracting data from JSON strings, enabling efficient retrieval of scalar values, arrays, and objects.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/sql-server-json_value\/\">JSON_VALUE()<\/a> &#8211; Extract a scalar value from a JSON string.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/sql-server-json_query\/\">JSON_QUERY()<\/a> &#8211; Extract a JSON object or array from a JSON document.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/sql-server-openjson\/\">OPENJSON()<\/a> &#8211; Convert a JSON document to rows and columns.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\" id='modifying-json'>Section 5. Modifying JSON <a href=\"#modifying-json\" class=\"anchor\" id=\"modifying-json\" title=\"Anchor for Section 5. Modifying JSON\">#<\/a><\/h2>\n\n\n\n<p>This section covers the function that allows you to delete, update, and insert a property into a JSON string.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/sql-server-json_modify\/\">JSON_MODIFY()<\/a> &#8211; Modify the value in a JSON string and return the updated JSON data.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/sql-server-for-json\/\">FOR JSON<\/a> &#8211; Show you how to convert JSON documents to relational views.<\/li>\n<\/ul>\n<\/div><\/div>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"3480\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/\"\n\t\t\t\tdata-post-title=\"SQL Server JSON Functions\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"3480\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/\"\n\t\t\t\tdata-post-title=\"SQL Server JSON Functions\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>This page provides you with a comprehensive guide to the most commonly used SQL Server JSON functions, providing insights into validating JSON text, creating JSON data, querying JSON objects, and modifying JSON data. Section 1. JSON Path Expressions # This section focuses on navigation JSON structures using JSON paths and shows you how to check [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":13,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-3480","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Server JSON Functions<\/title>\n<meta name=\"description\" content=\"This page covers the most commonly used SQL Server JSON functions for validating JSON text, modifying JSON data, and extracting JSON values.\" \/>\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.sqlservertutorial.net\/sql-server-json-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server JSON Functions\" \/>\n<meta property=\"og:description\" content=\"This page covers the most commonly used SQL Server JSON functions for validating JSON text, modifying JSON data, and extracting JSON values.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Server Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-13T15:36:23+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-json-functions\\\/\",\"url\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-json-functions\\\/\",\"name\":\"SQL Server JSON Functions\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/#website\"},\"datePublished\":\"2024-03-12T09:06:52+00:00\",\"dateModified\":\"2024-03-13T15:36:23+00:00\",\"description\":\"This page covers the most commonly used SQL Server JSON functions for validating JSON text, modifying JSON data, and extracting JSON values.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-json-functions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-json-functions\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-json-functions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server JSON Functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/\",\"name\":\"SQL Server Tutorial\",\"description\":\"The Practical SQL Server Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server JSON Functions","description":"This page covers the most commonly used SQL Server JSON functions for validating JSON text, modifying JSON data, and extracting JSON values.","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.sqlservertutorial.net\/sql-server-json-functions\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server JSON Functions","og_description":"This page covers the most commonly used SQL Server JSON functions for validating JSON text, modifying JSON data, and extracting JSON values.","og_url":"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/","og_site_name":"SQL Server Tutorial","article_modified_time":"2024-03-13T15:36:23+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/","url":"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/","name":"SQL Server JSON Functions","isPartOf":{"@id":"https:\/\/www.sqlservertutorial.net\/#website"},"datePublished":"2024-03-12T09:06:52+00:00","dateModified":"2024-03-13T15:36:23+00:00","description":"This page covers the most commonly used SQL Server JSON functions for validating JSON text, modifying JSON data, and extracting JSON values.","breadcrumb":{"@id":"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlservertutorial.net\/sql-server-json-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlservertutorial.net\/"},{"@type":"ListItem","position":2,"name":"SQL Server JSON Functions"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlservertutorial.net\/#website","url":"https:\/\/www.sqlservertutorial.net\/","name":"SQL Server Tutorial","description":"The Practical SQL Server Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlservertutorial.net\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/3480","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/comments?post=3480"}],"version-history":[{"count":5,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/3480\/revisions"}],"predecessor-version":[{"id":3564,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/3480\/revisions\/3564"}],"wp:attachment":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/media?parent=3480"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}