Format only part of the query results as JSON in SQL Server
FOR JSON PATH
applies to the entire result of the query so you cannot combine JSON-like data and non JSON-like data in a single SELECT
statement. Use a subquery in order to create the JSON specifically and then match it with the other columns using aliases.
This method requires a UNIQUE
/PRIMARY KEY
constraint on the Id
columns so that the WHERE
clause returns only one result at a time.
SELECT T1.[Name], T1.[Id], ( SELECT HasLogLevel = CASE WHEN JSON_VALUE(JsonVal,'$."LogLevel"') IS NOT NULL THEN 'True' ELSE 'False' END, LogLevel = CASE WHEN JSON_VALUE(JsonVal,'$."LogLevel"') IS NULL THEN 'NO-VALUE' ELSE JSON_VALUE(JsonVal,'$."LogLevel"') END, HasInfoLevel = CASE WHEN JSON_VALUE(JsonVal,'$."InfoLevel"') IS NOT NULL THEN 'True' ELSE 'False' END FROM TestTable AS T2 WHERE T2.[Id] = T1.[Id] FOR JSON PATH) AS FeaturesFROM TestTable AS T1;