JSON - What am I doing wrong

Question

JSON - What am I doing wrong

NicHopper

SSCrazy Eights

Points: 9090
More actions
July 7, 2020 at 3:54 pm
Go to Answer
#3768089
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 4 years, 5 months ago by NicHopper. Reason: added file as zip
Attachments:
You must be logged in to view attached files.
Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/
Mr. Brian Gale

SSC-Insane

Points: 24800
More actions
July 7, 2020 at 8:36 pm
Answer
#3768200
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?
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.
July 8, 2020 at 12:03 pm

This was removed by the editor as SPAM

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

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

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 1

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

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

JSON - What am I doing wrong

Attachments: