Query complex JSON in SQL Server - filter array of objects

Question

I'm having problems meshing together the JSON functions in Msft Sql Server. I have a table that stores complex JSON structures and need to pull out a subset of an array of objects.

As an example, I made a simple script that creates a table and populates it with a few records:

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"}]}]}');

I'm trying to get a list of the Vehicles for Company B that are Fords. My thought was to grab the record for Company B, then parse the json to get the array of Vehicles where Make = 'Ford'. This script works, but it's really clunky.

It seems to me this should all be rolled up into one single statement.

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';

First problem I see is the JSON_QUERY function returns my array of objects as a string, but it's not pure JSON. I manually prefix and suffix that string to make it a true JSON format.
I tried using the For JSON PATH to output true JSON, but that doesn't allow me to assign the output into a variable.

Next problem is the OPENJSON is working on a string variable, which follows all of the Msft examples I found. I find the examples odd since I assume most actual implementations would store the JSON in a table of some sorts.

Disclaimer: the JSON structure I used in the example above is much simpler than the format I have to work with. I am considering breaking the structure down into simpler components, then building up the final complete structure within the Select queries. Performance may also dictate the breaking up of this complex structure. My intent is to create Views that help "denormalize" the JSON into more traditional SQL data formats for debugging and filtering.

Answer 1

The query will obviously change depending on your structure, but I'm taking your sample.

OPENJSON can shred your JSON back to relational form so you can more easily filter and select. You do that using the optional WITH clause:

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'
;

You can go a long way without using JSON_QUERY or JSON_VALUE. And the above can be used in a view as you said, so the Json stuff is completely hidden.

The case of the string variable can be easily solved by CROSS APPLYing your data to the OPENJSON function as I've shown in the code above.

Does this take you closer to where you need to be?