在SQL Server中查询复杂的JSON-过滤对象数组

马修·艾伦

我在将Msft Sql Server中的JSON函数啮合在一起时遇到问题。我有一个表,用于存储复杂的JSON结构,并且需要提取对象数组的子集。

例如,我制作了一个简单的脚本,该脚本创建一个表并用一些记录填充该表:

CREATE TABLE JsonData ( CompanyId int IDENTITY(1,1) NOT NULL, Name varchar(50) NOT NULL, Json varchar(max) NOT NULL)

INSERT INTO JsonData (Name, Json) VALUES ('Company A', '{"Sector":"Food/Bev","EmployeeCount":105,"Address":{"Address1":"88 Oak Ave","Address2":"","City":"Madison","State":"WI","Zip":"11223"},"Vehicles":[{"Make":"Toyota","Model":"Camry","Year":2013,"Maintenance":[{"Desc":"Oil change","PerformedOn":"2017-04-01"},{"Desc":"Oil change","PerformedOn":"2017-08-01"}]},{"Make":"Ford","Model":"F150","Year":2010,"Maintenance":[{"Desc":"Oil change","PerformedOn":"2015-01-01"}]},{"Make":"Honda","Model":"Odyssey","Year":2010,"Maintenance":[{"Desc":"Oil change","PerformedOn":"2013-01-01"},{"Desc":"Oil change","PerformedOn":"2014-01-01"}]}]}');
INSERT INTO JsonData (Name, Json) VALUES ('Company B', '{"Sector":"Plastics","EmployeeCount":853,"Address":{"Address1":"100 Main St","Address2":"","City":"Anchorage","State":"AK","Zip":"56432"},"Vehicles":[{"Make":"Ford","Model":"F150","Year":2003,"Maintenance":[{"Desc":"Oil change","PerformedOn":"2017-01-01"},{"Desc":"Tire rotation","PerformedOn":"2017-01-01"},{"Desc":"Brake inspection","PerformedOn":"2017-02-01"}]},{"Make":"Ford","Model":"F150","Year":2008,"Maintenance":[{"Desc":"Oil change","PerformedOn":"2017-01-01"}]},{"Make":"Volkswagen","Model":"Jetta","Year":2010,"Maintenance":[]}]}');
INSERT INTO JsonData (Name, Json) VALUES ('Company C', '{"Sector":"Plastics","EmployeeCount":50,"Address":{"Address1":"99 Pine St","Address2":"","City":"Dallas","State":"TX","Zip":"33443"},"Vehicles":[{"Make":"Pontiac","Model":"Fiero","Year":1998,"Maintenance":[{"Desc":"Oil change","PerformedOn":"2010-04-01"},{"Desc":"Oil change","PerformedOn":"2000-08-01"}]},{"Make":"Chevy","Model":"Silverado","Year":2008,"Maintenance":[{"Desc":"Oil change","PerformedOn":"2010-01-01"}]},{"Make":"Honda","Model":"Odyssey","Year":2014,"Maintenance":[{"Desc":"Oil change","PerformedOn":"2015-04-01"},{"Desc":"Oil change","PerformedOn":"2015-09-01"}]}]}');

我正在尝试获取福特公司B公司车辆的清单。我的想法是获取公司B的记录,然后解析json以获取Make ='Ford'的Vehicles数组。该脚本有效,但是确实很笨拙。

在我看来,这应该全部汇总为一个声明。

DECLARE @vehicJson varchar(max);
SELECT @vehicJson = '{ "Vehicles": ' + JSON_QUERY(json, '$.Vehicles') + '}' FROM JsonData WHERE Name = 'Company B';
SELECT @vehicJson;
SELECT * FROM OPENJSON(@vehicJson, '$.Vehicles') WHERE JSON_VALUE(value, '$.Make') = 'Ford';

我看到的第一个问题是JSON_QUERY函数将我的对象数组作为字符串返回,但这不是纯JSON。我手动对该字符串添加前缀和后缀以使其成为真正的JSON格式。
我尝试使用For JSON PATH输出真实的JSON,但这不允许我将输出分配到变量中。

下一个问题是OPENJSON正在处理字符串变量,该变量遵循我发现的所有Msft示例。我发现这些示例很奇怪,因为我假设大多数实际实现都会将JSON存储在某种类型的表中。

免责声明:我在上面的示例中使用的JSON结构比我必须使用的格式简单得多。我正在考虑将结构分解为更简单的组件,然后在Select查询中构建最终的完整结构。性能可能还决定了这种复杂结构的破裂。我的意图是创建视图,以帮助将JSON“非规范化”为更传统的SQL数据格式,以进行调试和过滤。

基希纳

该查询显然会根据您的结构而改变,但是我正在取样。

OPENJSON可以将JSON切回关系形式,以便您更轻松地进行过滤和选择。您可以使用可选WITH子句来做到这一点

SELECT j.*, j2.*
FROM JsonData j
CROSS APPLY OPENJSON(Json, '$.Vehicles') WITH (
    Make VARCHAR(10),
    Model VARCHAR(10),
    Year INT
) j2
WHERE j.Name = 'Company B'
    AND j2.Make = 'Ford'
;

您无需使用JSON_QUERY就可以走很长一段路JSON_VALUE而且上述内容可以在您所说的视图中使用,因此Json的东西被完全隐藏了。

OPENJSON上面的代码所示,通过将数据交叉应用到函数可以轻松解决字符串变量的情况

这会使您更接近需要去的地方吗?

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章