JSON - What am I doing wrong

  • NicHopper

    SSCrazy Eights

    Points: 9034

    Hi,

    So I'm having a little play with JSON as I need to store some data as JSON in a new db we have.

    I created a little sample, where I use tempdb, create a table, but some JSON in there and read it back out.

    I've attached the script to this, my issues are;

    1. Why do I not get 2 rows back, the JSON contains to sets of data so I would expect to records back.
    2. I get 1 record back (for John) but the ISJSON value is 0 so why is do I get data back if its not JSON?

    I've no doubt the issue is the formatting of the JSON but I cant for the life of me see what I've missed.

    Can anyone help me out and tell me what I'm missing.

    Thanks,

    Nic

    • This topic was modified 1 month ago by  NicHopper. Reason: added file as zip
    Attachments:
    You must be logged in to view attached files.
  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    So, to fix your JSON, you need [ ] around the whole thing as it is an array.

    Something like this:

    SET @SampleJSON = '[
    {
    "name": "John",
    "surname": "Doe",
    "skills": ["SQL", "C#", "Azure"]
    },
    {
    "name": "Jane",
    "surname": "Doe",
    "skills": ["SQL", "C#", "Azure"]
    }
    ]'

    If you do that, then your ISJSON will return 1 like what you want as it is now valid JSON format.  This will break the SELECT for the value, but we can fix that as it wasn't working right anywas. JSON_VALUE returns a single value, not a set.  So if you want the first value, you would use $[0].name.  For example:

    USE [Admin]

    DECLARE @Response TABLE
    (
    ResponseJSON NVARCHAR(MAX)
    )


    DECLARE @SampleJSON NVARCHAR(MAX)
    SET @SampleJSON = '[
    {
    "name": "John",
    "surname": "Doe",
    "skills": ["SQL", "C#", "Azure"]
    },
    {
    "name": "Jane",
    "surname": "Doe",
    "skills": ["SQL", "C#", "Azure"]
    }
    ]'

    --Put the sample data in
    INSERT INTO @Response
    SELECT @SampleJSON

    --Check if the JSON is valid
    SELECT ResponseJSON,ISJSON(ResponseJSON)
    FROM @Response


    SELECT JSON_VALUE(ResponseJSON,'$[0].name') AS Name
    FROM @Response
    --WHERE ISJSON(ResponseJSON) > 0

    If you want ALL of the "names" from that JSON object, you will need to do it with a CROSS APPLY like so:

    SELECT [JSONData].[name]
    FROM @response
    CROSS APPLY
    OPENJSON(ResponseJSON) WITH (name VARCHAR(255) '$.name') JSONData;

    Does that help?

  • NicHopper

    SSCrazy Eights

    Points: 9034

    Thank you very much.

    This is perfect, it not only gives me the desired result but also explains the process behind it, saving me asking more questions later on.

    Thank you for taking the time to formulate such a well written response.

    Nic

  • This was removed by the editor as SPAM

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

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