Skip to content
Advertisement

How to generalize the JSON in stored procedure?

In my database, I have table called library which have columns like dependencies, email, alert, skipped and values are stored as

I want to prepare JSON of these values which will look like as

I have written have code as

And my JSON doesn’t look like desired JSON

I am not able to achieve correct json . Can someboby please help me out here ?

Thank you

Advertisement

Answer

There are a number of problems with your existing query:

  • dependencies contains the whole coordinates data, you can’t just select the whole thing. Instead you need break it out and rebuild it. You can use OPENJSON as I have, or JSON_VALUE
  • email is not valid JSON, it is a comma-separated list. Unfortunately, SQL Server does not have JSON_AGG which would have made this simpler. So break it open using STRING_SPLIT and rebuild using a combination of STRING_ESCAPE (to escape any problematic characters) and STRING_AGG (to aggregate it again) and JSON_QUERY (to prevent double-escaping).
  • true and false appear to be varchar, you need a bit value instead

db<>fiddle

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement