SQL Server JSON 数据操作-Toy模板网

这篇具有很好参考价值的文章主要介绍了SQL Server JSON 数据操作。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

使用内置函数 (SQL Server) 验证、查询和更改 JSON 数据
使用 OPENJSON 分析和转换 JSON 数据 (SQL Server)

1、JSON 内置函数
ISJSON 测试字符串是否包含有效 JSON。
JSON_VALUE 从 JSON 字符串中提取标量值。
JSON_QUERY 从 JSON 字符串中提取对象或数组。
JSON_MODIFY 更新 JSON 字符串中属性的值，并返回已更新的 JSON 字符串。

2、示例 JSON 文本

{
  "id": "WakefieldFamily",
  "parents": [
    {
      "familyName": "Wakefield",
      "givenName": "Robin"
    },
    {
      "familyName": "Miller",
      "givenName": "Ben"
    }
  ],
  "children": [
    {
      "familyName": "Merriam",
      "givenName": "Jesse",
      "gender": "female",
      "grade": 1,
      "pets": [
        {
          "givenName": "Goofy"
        },
        {
          "givenName": "Shadow"
        }
      ]
    },
    {
      "familyName": "Miller",
      "givenName": "Lisa",
      "gender": "female",
      "grade": 8
    }
  ],
  "address": {
    "state": "NY",
    "county": "Manhattan",
    "city": "NY"
  },
  "creationDate": 1431620462,
  "isRegistered": false
}

3、新建表，插入测试数据

CREATE TABLE Families (
   id int identity constraint PK_JSON_ID primary key,
   doc nvarchar(max)
)

INSERT INTO [dbo].[Families] ([doc]) VALUES ('{"id":"WakefieldFamily","parents":[{"familyName":"Wakefield","givenName":"Robin"},{"familyName":"Miller","givenName":"Ben"}],"children":[{"familyName":"Merriam","givenName":"Jesse","gender":"female","grade":1,"pets":[{"givenName":"Goofy"},{"givenName":"Shadow"}]},{"familyName":"Miller","givenName":"Lisa","gender":"female","grade":8}],"address":{"state":"NY","county":"Manhattan","city":"NY"},"creationDate":1431620462,"isRegistered":false}')
GO

INSERT INTO [dbo].[Families] ([doc]) VALUES ('{"id":"WakefieldFamily","parents":[{"familyName":"Wakefield","givenName":"Robin"},{"familyName":"Miller","givenName":"Ben"}],"children":[{"familyName":"Merriam","givenName":"Jesse","gender":"female","grade":1,"pets":[{"givenName":"Goofy"},{"givenName":"Shadow"}]},{"familyName":"Miller","givenName":"Lisa","gender":"female","grade":8}],"address":{"state":"NY","county":"Manhattan","city":"NY"},"creationDate":1431620462,"isRegistered":false}')
GO

INSERT INTO [dbo].[Families] ([doc]) VALUES ('{"id":"WakefieldFamily","parents":[{"familyName":"Wakefield","givenName":"Robin"},{"familyName":"Miller","givenName":"Ben"}],"children":[{"familyName":"Merriam","givenName":"Jesse","gender":"female","grade":1,"pets":[{"givenName":"Goofy"},{"givenName":"Shadow"}]},{"familyName":"Miller","givenName":"Lisa","gender":"female","grade":8}],"address":{"state":"NY","county":"Manhattan","city":"NY"},"creationDate":1431620462,"isRegistered":false}')
GO

4、使用 JSON_VALUE 函数从 JSON 文本中提取值

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC, JSON_VALUE(f.doc, '$.address.state') ASC

5、使用 JSON_QUERY 函数从 JSON 文本中提取对象或数组

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
       JSON_QUERY(f.doc, '$.parents') AS Parents,
       JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'

*
*
*文章来源地址https://www.toymoban.com/news/detail-566891.html

到了这里，关于SQL Server JSON 数据操作的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！