Post reply

JSON building from configuration table

  • January 19, 2022 at 6:42 pm

    #3974821

    The attached file shows

     

    the json schema - defined levels

    and the bottom section shows the JSON needed to be built

    I want to write a TSQL query which can read the JSON schema and built the JSON shown

    Please advise

    Thanks

     

    Attachments:
    You must be logged in to view attached files.

  • January 19, 2022 at 9:11 pm

    #3974972

    This to me looks like a homework type assignment or possibly an interview question, but I'll try to help without giving the answer away.

    So my first step would be to validate your input.  The first bit you indicated should be table data so we can ignore that.  The second bit though is still not valid JSON, there is no work for SQL to do.

    Now, assuming the JSON WAS in valid format, Microsoft has a pretty good writeup on how to parse JSON here - https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

    Assuming that the invalid JSON for the answer is expected and you are expected to provide the corrected JSON output, I would start by coding the first bit up as an INSERT statement which is trivial to do.  Create the table, then copy-paste the remainder into an INSERT statement.  Next, do a SELECT to confirm that the table contains the EXACT same data as the top part of the text.  1/2 the battle is done.

    Next step, use the link I provided to look up how to SELECT to a JSON object and you are done.

    Now, if you need this to be more automated, that is going to be a bit more work to do but is entirely possible.  Since I suspect this is a homework assignment, I doubt automation is required...

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • January 20, 2022 at 1:36 pm

    #3975207

    Deleted, wrong solution

    • This reply was modified 2 years, 10 months ago by  Mark Cowne.
    • This reply was modified 2 years, 10 months ago by  Mark Cowne.
    • This reply was modified 2 years, 10 months ago by  Mark Cowne.
    • This reply was modified 2 years, 10 months ago by  Mark Cowne. Reason: Problems with square brackets
    • This reply was modified 2 years, 10 months ago by  Mark Cowne.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • January 21, 2022 at 2:12 pm

    #3975631

    your assumption was wrong - I was trying to hide medical data , make it simpler to understand for the forum

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply