首页 › 数据库综合论坛 › SQL Server中OPENJSON + WITH 解析JSON数据的示例

香菜有毒 發表於 2025-8-18 14:19:03

SQL Server中OPENJSON + WITH 解析JSON数据的示例

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、概念</a></li><li><a href="#_label1">二、语法</a></li><li><a href="#_label2">三、使用示例</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_0">示例1：解析简单的 JSON 对象</a></li><li><a href="#_lab2_2_1">示例2：处理 JSON 数组</a></li><li><a href="#_lab2_2_2">示例3：处理嵌套的 JSON 对象</a></li><ul class="third_class_ul"><li><a href="#_label3_2_2_0">重要概念解释</a></li></ul></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>一、概念</h2>
<p>OPENJSON 是 SQL Server（2016 及更高版本） 中引入的一个<strong>表值函数</strong>，它将 <strong>JSON 文本转换为行和列的关系型数据结构</strong>。通过添加 WITH 子句，可以明确指定返回数据的结构和类型，实现 JSON 数据到表格数据的精确映射。</p>
<ul><li>OPENJSON 函数</li><li>OPENJSON 函数用于将 JSON 文本解析为关系型数据，即将 JSON 数据转换为一张表。默认情况下，OPENJSON 返回三列：<ul><li>key：JSON 的键值</li><li>value：对应的值</li><li>type：值的数据类型（例如：字符串、整数、对象、数组等标记为数字）</li></ul></li><li>WITH 子句</li><li>使用 WITH 子句可以<strong>将 JSON 中的数据映射为指定的列</strong>，并<strong>定义其数据类型</strong>与 JSON 路径。这样不仅可以对 JSON 进行解析，还能以传统的关系型数据方式进行查询和处理。</li></ul>
<p class="maodian"><a name="_label1"></a></p><h2>二、语法</h2>
<div class="jb51code"><pre class="brush:sql;">SELECT column_list
FROM OPENJSON(json_expression)
WITH (
column1 data_type '$.path1',
column2 data_type '$.path2',
...
);</pre></div>
<p>说明：</p>
<ul><li>json_expression：可以是一个<strong>包含 JSON 字符串的变量</strong>、<strong>列</strong>或<strong>直接的 JSON 文本</strong>。</li><li>WITH 子句中指定了需要<strong>映射的列名</strong>、<strong>数据类型</strong>以及<strong> JSON 路径</strong>。</li><li>$.path 表示从根（$）开始的 JSON 路径。例如：$.id、$.customer.name 等。</li></ul>
<p>这样，OPENJSON 会把解析的结果返回为一张虚拟表，通过 SELECT 语句可以直接查询。</p>
<p class="maodian"><a name="_label2"></a></p><h2>三、使用示例</h2>
<p class="maodian"><a name="_lab2_2_0"></a></p><h3>示例1：解析简单的 JSON 对象</h3>
<div class="jb51code"><pre class="brush:sql;">DECLARE @json NVARCHAR(MAX) = N'{"id": 1, "name": "张三", "age": 30, "isActive": true}';
SELECT *
FROM OPENJSON(@json)
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    age INT '$.age',
    isActive BIT '$.isActive'
);</pre></div>
<p class="maodian"><a name="_lab2_2_1"></a></p><h3>示例2：处理 JSON 数组</h3>
<ul><li><strong>将整个数组转换为表行</strong>：使用 OPENJSON 将数组中的每个元素转换为结果集中的一行。</li><li><strong>提取数组元素的特定属性</strong>：结合 WITH 子句指定需要提取的属性及其数据类型。</li><li><strong>处理嵌套数组</strong>：使用 CROSS APPLY 配合多层 OPENJSON 调用。</li></ul>
<p>关键点：</p>
<ul><li>对数组元素使用 AS JSON 选项保持 JSON 格式以便进一步处理</li><li>使用 CROSS APPLY 连接多个 OPENJSON 调用来处理多层嵌套</li></ul>
<div class="jb51code"><pre class="brush:sql;">DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "name": "张三", "skills": ["SQL", "C#", "Python"]},
    {"id": 2, "name": "李四", "skills": ["Java", "JavaScript"]}
]';
SELECT id, name, skills
FROM OPENJSON(@json)
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    skills NVARCHAR(MAX) '$.skills' AS JSON
);</pre></div>
<p><strong>输出结果</strong></p>
<p>这个查询从JSON数组中提取基本信息并保留skills数组为JSON格式：</p>
id	name	skills
1	张三	["SQL", "C#", "Python"]
2	李四	["Java", "JavaScript"]
<div class="jb51code"><pre class="brush:sql;">--处理用户及其标签的 JSON 数组
DECLARE @json NVARCHAR(MAX) = N'[
    {"userID": 1, "username": "user1", "tags": ["前端", "JavaScript", "React"]},
    {"userID": 2, "username": "user2", "tags": ["后端", "Python", "Django"]},
    {"userID": 3, "username": "user3", "tags": ["全栈", "JavaScript", "Node.js", "MongoDB"]}
]';
-- 提取用户基本信息（保留标签数组为 JSON）
SELECT userID, username, tags
FROM OPENJSON(@json)
WITH (
    userID INT '$.userID',
    username NVARCHAR(50) '$.username',
    tags NVARCHAR(MAX) '$.tags' AS JSON
) AS users;
-- 展开每个用户的标签到单独的行（一对多关系）
SELECT
    u.userID,
    u.username,
    JSON_VALUE(t.value, '$') AS tag
FROM OPENJSON(@json)
WITH (
    userID INT '$.userID',
    username NVARCHAR(50) '$.username',
    tags NVARCHAR(MAX) '$.tags' AS JSON
) AS u
CROSS APPLY OPENJSON(u.tags) AS t;</pre></div>
<p><strong>第一部分输出结果</strong></p>
<p>这个查询提取用户基本信息，保留标签数组为JSON格式：</p>
userID	username	tags
1	user1	["前端", "JavaScript", "React"]
2	user2	["后端", "Python", "Django"]
3	user3	["全栈", "JavaScript", "Node.js", "MongoDB"]
<p><strong>第二部分输出结果</strong></p>
<p>这个查询使用CROSS APPLY展开每个用户的标签到单独的行，实现了一对多的关系展示：</p>
userID	username	tag
1	user1	前端
1	user1	JavaScript
1	user1	React
2	user2	后端
2	user2	Python
2	user2	Django
3	user3	全栈
3	user3	JavaScript
3	user3	Node.js
3	user3	MongoDB
<p class="maodian"><a name="_lab2_2_2"></a></p><h3>示例3：处理嵌套的 JSON 对象</h3>
<p>这个例子展示了 SQL Server 中 JSON 路径表达式的使用，特别是 <code>$.path</code> 格式如何从根（$）开始导航嵌套的 JSON 结构。</p>
<p class="maodian"><a name="_label3_2_2_0"></a></p><h4>重要概念解释</h4>
<ul><li><strong>$ 符号</strong>：始终表示&quot;当前上下文的根&quot;，不一定是整个 JSON 文档的根</li><li><strong>上下文切换</strong>：OPENJSON 的第二个参数改变了解析上下文，所有 WITH 子句中的路径都相对于这个新上下文</li></ul>
<div class="jb51code"><pre class="brush:sql;">DECLARE @json NVARCHAR(MAX) = N'{
    "employee": {
      "id": 101,
      "name": "王五",
      "contact": {
            "email": "wangwu@example.com",
            "phone": "13800138000"
      }
    }
}';
SELECT id, name, email, phone
FROM OPENJSON(@json, '$.employee')
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    email NVARCHAR(100) '$.contact.email',
    phone NVARCHAR(20) '$.contact.phone'
);</pre></div>
<p>在这个示例中：</p>
<ol><li><strong>OPENJSON 的第二个参数</strong> <code>&#39;$.employee&#39;</code>：
<ul><li><code>$</code> 表示整个 JSON 文档的根</li><li><code>.employee</code> 表示从根访问名为 &quot;employee&quot; 的对象</li><li>这个参数将查询的上下文（或&quot;基准点&quot;）设置为 employee 对象内部</li></ul></li><li><strong>WITH 子句中的路径</strong>：<ul><li><code>&#39;$.id&#39;</code> 和 <code>&#39;$.name&#39;</code> 从 employee 对象（当前上下文）直接访问属性</li><li><code>&#39;$.contact.email&#39;</code> 和 <code>&#39;$.contact.phone&#39;</code> 表示从当前上下文（employee 对象）开始，先访问 contact 对象，然后获取其中的 email 或 phone 属性</li></ul></li></ol>

查看完整版本: SQL Server中OPENJSON + WITH 解析JSON数据的示例