sql-server - T-SQL JSON : How do i search for a value in a JSON array

Question

我们正在使用 Azure SQL - 并有一个名为 Companies 的表，其中其中一列包含 JSON。 JSON 字段的结构为:

{
  "DepartmentLink": "https://company.com",
  "ContactName": "John Doe",
  "ContactTitle": "CEO",
  "ContactEmail": "john.doe@company.com",
  "ContactPhone": "xx xx xx xx xx",
  "ContactImage": "https://company.com/xyz.jpg",
  "ZipCodes": [
    "7000",
    "7007",
    "7017",
    "7018",
    "7029"
  ]
}

表的结构如下所示

[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NULL,
[JsonString] [nvarchar](max) NULL,
-- other fields --

其中 [JsonString] 具有以下结构:

{
  "DepartmentLink": "https://company.com",
  "ContactName": "John Doe",
  "ContactTitle": "CEO",
  "ContactEmail": "john.doe@company.com",
  "ContactPhone": "xx xx xx xx xx",
  "ContactImage": "https://company.com/xyz.jpg",
  "ZipCodes": [
    "7000",
    "7007",
    "7017",
    "7018",
    "7029"
  ]
}

给定一个邮政编码，例如7018 我需要找到在 Json Array ZipCodes 中具有此 ZipCode 的公司 - 并从记录中返回元素(这些元素都以普通“字段”形式出现 - 因此我不需要返回 JSON。)。

我在寻找如何执行此操作时遇到问题。有什么建议么？我对 SQL 中的 JSON 很陌生。

Answer 1

使用OPENJSON和CROSS APPLY来分解邮政编码数组，然后在过滤器中添加一个WHERE子句，如下所示:

SELECT *, JSON_VALUE( yourJSON, '$.DepartmentLink' ) AS DepartmentLink
FROM dbo.Companies 
    CROSS APPLY OPENJSON( yourJSON, '$.ZipCodes') x
WHERE x.value = '7000';