Format only part of the query results as JSON in SQL Server

Question

I have a SQL Table X which has a JSON Column 'JsonVal' & few other Columns

Name      Id    Group    JsonVal
----------------------------------------------------------
Name1      2     gg1     {"LogLevel":"2", "InfoLevel":"3"}
Name2      3     gg5     {"LogLevel":"4"}

After parsing and making sense of the data in JsonVal column the way i want, I want to read from table X such that i get a result like this:

Name      Id    Features
----------------------------------------------------------
Name1      2     {"HasLogLevel":"True", "LogLevel":"2", "HasInfoLevel":"True"}
Name2      3     {"HasLogLevel":"True", "LogLevel":"4", "HasInfoLevel":"False"}

I am not able to find a way to convert just part of my columns to JSON. If i do FOR JSON in a query like this:

SELECT Name,Id,
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 X FOR JSON PATH

It converts all the columns (Including Name, Id ) into JSON. But i only want the 3 columns HasLogLevel,LogLevel and HasInfoLevel to be formatted to JSON in the result

Is there a way to query and get results the way i want in SQL Server in a single query?

What is your scenario? Maybe you can query the Azure SQL Database with Python and generate new JSON strings from there. — hokkaidi
– hokkaidi, Commented Oct 11, 2017 at 22:32
@EstienneGranet I can use only T-SQL for this due to limitations. Do you know of a way to achieve this by query? — Alfan
– Alfan, Commented Oct 13, 2017 at 17:16

hokkaidi · Accepted Answer · 2017-10-14 01:29:30Z

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 Features
FROM TestTable AS T1;

Collectives™ on Stack Overflow

Format only part of the query results as JSON in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related