SQL Server OPENJSON解析JSON时无法识别重复地址值问题

我需要将销售记录解析并导入数据库。部分客户会购买多件商品，因此在JSON数组中存在多条对应不同商品、但收货地址完全相同的订单。

在向Addresses表存储地址数据时出现问题：业务要求每个唯一地址仅保留1条记录，因此我对地址字段计算Hash值，通过与表中已存储的Hash值比对实现去重。

实际执行时确认，查询仅会在初始阶段校验Hash是否已存在，若初始不存在，会插入与订单总数一致的重复地址行。该问题是SQL Server的语句执行逻辑导致：INSERT ... SELECT语句的校验逻辑只会读取语句执行前表中已存在的数据，同批次SELECT返回的重复行不会被LEFT JOIN ... IS NULL逻辑过滤，因此会批量插入重复数据，和OPENJSON本身的解析行为无关。

declare @json nvarchar(max)=N'[
   {
      "id": 21660,
      "currency": "USD",
      "total": "15.00",
      "shipping": {
         "first_name": "Charles",
         "last_name": "Leuschke",
         "address_1": "3121 W Olive Ave",
         "city": "Burbank",
         "state": "CA",
         "postcode": "91505",
         "country": "US"
      },
      "line_items": [
         {
            "id": 1052
         }
      ]
   },
   {
      "id": 21659,
      "currency": "USD",
      "total": "38.00",
      "shipping": {
         "first_name": "Charles",
         "last_name": "Leuschke",
         "address_1": "3121 W Olive Ave",
         "city": "Burbank",
         "state": "CA",
         "postcode": "91505",
         "country": "US"
      },
      "line_items": [
         {
            "id": 1050
         }
      ]
   },
   {
      "id": 21658,
      "currency": "USD",
      "total": "38.00",
      "shipping": {
         "first_name": "Charles",
         "last_name": "Leuschke",
         "address_1": "3121 W Olive Ave",
         "city": "Burbank",
         "state": "CA",
         "postcode": "91505",
         "country": "US"
      },
      "line_items": [
         {
            "id": 1048
         }
      ]
   }
]'

Insert Into @Addresses
(
    orderId,
    fullName,
    addressLine1,
    city,
    stateOrProvince,
    postalCode,
    countryCode,
    addressCode
)
SELECT
    o.orderId,
    concat(s.firstName,' ',s.lastName),
    s.addressLine1,
    s.city,
    s.stateOrProvince,
    s.postalCode,
    s.countryCode,
    convert(nvarchar(64),hashbytes('SHA1',concat(s.firstName, ' ', s.lastName, s.addressLine1, s.city, s.stateOrProvince, s.postalCode, s.countryCode)),2)
FROM OPENJSON(@json) 
    WITH  (
            orderId                     nvarchar(64)    '$.id',
            shipping                    nvarchar(max)   '$.shipping' AS JSON
          ) o  
    CROSS APPLY OPENJSON(shipping) 
    WITH  (
            firstName                   nvarchar(128)   '$.first_name',
            lastName                    nvarchar(128)   '$.last_name',
            addressLine1                nvarchar(128)   '$.address_1',
            city                        nvarchar(128)   '$.city',
            stateOrProvince             nvarchar(64)    '$.state',
            postalCode                  nvarchar(64)    '$.postcode',
            countryCode                 nvarchar(4)     '$.country'
         ) s

left join @Addresses a on a.addressCode=convert(nvarchar(64),hashbytes('SHA1',concat(s.firstName,' ', s.lastName, s.addressLine1, s.city, s.stateOrProvince, s.postalCode, s.countryCode)),2)
where a.addressCode is null

上述查询执行后会插入3条重复地址记录，不符合每个唯一地址仅保留1条记录的要求。

在SELECT解析JSON的阶段，先对同批次的地址按Hash值做分组去重，再关联已有表做存在性校验，即可保证插入的地址唯一。修改后的查询如下：

Insert Into @Addresses
(
    orderId,
    fullName,
    addressLine1,
    city,
    stateOrProvince,
    postalCode,
    countryCode,
    addressCode
)
SELECT
    MIN(o.orderId) AS orderId, -- 同地址取最小订单ID关联即可，可根据业务需求调整取值逻辑
    concat(s.firstName,' ',s.lastName) AS fullName,
    s.addressLine1,
    s.city,
    s.stateOrProvince,
    s.postalCode,
    s.countryCode,
    convert(nvarchar(64),hashbytes('SHA1',concat(s.firstName, ' ', s.lastName, s.addressLine1, s.city, s.stateOrProvince, s.postalCode, s.countryCode)),2) AS addressCode
FROM OPENJSON(@json) 
    WITH  (
            orderId                     nvarchar(64)    '$.id',
            shipping                    nvarchar(max)   '$.shipping' AS JSON
          ) o  
    CROSS APPLY OPENJSON(shipping) 
    WITH  (
            firstName                   nvarchar(128)   '$.first_name',
            lastName                    nvarchar(128)   '$.last_name',
            addressLine1                nvarchar(128)   '$.address_1',
            city                        nvarchar(128)   '$.city',
            stateOrProvince             nvarchar(64)    '$.state',
            postalCode                  nvarchar(64)    '$.postcode',
            countryCode                 nvarchar(4)     '$.country'
         ) s
-- 排除表中已存在的地址
WHERE NOT EXISTS (
    SELECT 1 FROM @Addresses a 
    WHERE a.addressCode = convert(nvarchar(64),hashbytes('SHA1',concat(s.firstName,' ', s.lastName, s.addressLine1, s.city, s.stateOrProvince, s.postalCode, s.countryCode)),2)
)
-- 同批次解析结果按地址维度分组去重
GROUP BY 
    concat(s.firstName,' ',s.lastName),
    s.addressLine1,
    s.city,
    s.stateOrProvince,
    s.postalCode,
    s.countryCode,
    convert(nvarchar(64),hashbytes('SHA1',concat(s.firstName, ' ', s.lastName, s.addressLine1, s.city, s.stateOrProvince, s.postalCode, s.countryCode)),2)

注意：如果业务需要保留所有订单和地址的关联关系，不建议把orderId字段直接存在地址主表中，应单独新建订单-地址关联表存储映射关系，避免违反地址表唯一地址单条记录的设计要求。如果是生产环境永久表，建议给addressCode字段加唯一约束，从数据库层面兜底避免重复地址写入。

内容的提问来源于stack exchange，提问作者Joe