使用JSON数据在SQL Server 2016中加入两个集合

问题描述：

目前，我正在使用带有JSON的SQL Server 2016，并且想要将集合加入到一起。到目前为止，我创建了两个类别：使用JSON数据在SQL Server 2016中加入两个集合

CREATE TABLE collect_person(person...) 
CREATE TABLE collect_address(address...)

的JSON文件看起来像这样第一个集合（collection_person）：

{ 
    "id" : "P1", 
    "name" : "Sarah", 
    "addresses" : { 
     "addressId" : [ 
     "ADD1", 
     "ADD2" 
     ] 
    } 
}

的JSON文件看起来像下面这些第二集合（collect_address ）：

{ 
    "id" : "ADD1", 
    "city" : "San Jose", 
    "state" : "CA" 
} 

{ 
    "id" : "ADD2", 
    "city" : "Las Vegas" 
    "state" : "NV" 
}

我想获得一个名为“莎拉”的人的地址，所以输出会是这样的：

{ 
    {"city" : "San Jose", "state" : "CA"}, 
    {"city" : "Las Vegas", "state" : "NV"} 
}

我不想将JSON转换为SQL和SQL到JSON。这是可能在SQL Server 2016中使用JSON来做，请告诉我怎么做？先谢谢你。

答

以前从未真正使用JSON，但我不知道可以使用它加入集合。 JSON设计用于与服务器之间的数据交换，并充当传输方法。

答

我有点迟到的问题，但它可以通过交叉申请，我也用公用表表达式。根据表的大小，我建议在每个表的id字段上创建一个持久化的计算列，假设数据不会改变，并且每个记录有一个addressId或添加一些其他可以索引和使用的键值限制需要转换为JSON的记录。这是一个简单的例子，它没有经过性能测试，所以“YMMV”。

建筑物中的表

DECLARE @collect_person AS TABLE 
(Person NVARCHAR(MAX)) 

DECLARE @collect_address as TABLE 
([Address] NVARCHAR(MAX)) 

INSERT INTO @collect_person (Person) 
SELECT N'{ 
    "id" : "P1", 
    "name" : "Sarah", 
    "addresses" : { 
     "addressId" : [ 
     "ADD1", 
     "ADD2" 
     ] 
    } 
}' 

INSERT INTO @collect_address ([Address]) 
VALUES 
(N'{ 
    "id" : "ADD1", 
    "city" : "San Jose", 
    "state" : "CA" 
}') 
,('{ 
    "id" : "ADD2", 
    "city" : "Las Vegas", 
    "state" : "NV" 
}')

查询表

;WITH persons AS ( 
SELECT --JP.* 
    JP.id 
    ,JP.name 
,JPA.addressId -- Or remove the with clause for JPA and just use JPA.value as addressId  
FROM @collect_person 
CROSS APPLY OPENJSON([person]) 
    WITH (
    id varchar(50) '$.id' 
    ,[name] varchar(50) '$.name' 
    ,addresses nvarchar(max) AS JSON 
) as JP 
    CROSS APPLY OPENJSON(JP.addresses, '$.addressId') 
    WITH (
     addressId varchar(250) '$' 
    ) AS JPA 
) 
,Addresses AS (
    SELECT A.* 
    FROM @collect_address AS CA 
    CROSS APPLY OPENJSON([Address]) 
     WITH (
     id varchar(50) '$.id' 
     ,city varchar(50) '$.city' 
     ,state varchar(2) '$.state' 
    ) as A 
) 

SELECT * FROM persons 
    INNER JOIN Addresses 
    ON persons.addressId = Addresses.id

再次，这是不是这样做的理想方式，但它会工作，如前所述，你或许应该每个表上都有一个关键字段，这些关键字段被索引以限制在表上完成的扫描和JSON解析。

有本地编译，但它对我来说是新的，我不熟悉的优点和缺点。

Optimize JSON processing with in-memory OLTP

使用JSON数据在SQL Server 2016中加入两个集合

相关推荐