We help IT Professionals succeed at work.

SQL to JSON

226 Views
Last Modified: 2017-04-12
I currently have an sql that returns data in JSON format. It works when it is one record that is returned.
This is my current query:

SELECT
'{
  "first_name": "' + FirstNm + '",
  "last_name": "' + LastNm + '",
  "email_addresses": [
    {
      "email_address": "' + email + '"
    }
  ]
}' as json_result
FROM dbo.Users
WHERE UserType = 'contact' AND Archivedcont <> 1  AND Email IS NOT NULL AND UserId = 30638

Open in new window


It returns the data in the correct JSON format

[{
	"first_name": "Jhon3",
	"last_name": "Smith3",
	"email_addresses": [{
		"email_address": "js3@systems.com"
	}]
}]

Open in new window


What I now need to do is to do the same for an array. The problem I have is that if I do my SQL it returns multiple records instead of one record in the correct JSON format.

This is my current SQL

SELECT
'[
{
  "first_name": "' + FirstNm + '",
  "last_name": "' + LastNm + '",
  "email_addresses": [
    {
      "email_address": "' + email + '"
    }
  ]
}
]' as json_result
FROM dbo.Users
WHERE UserType = 'contact' AND Archivedcont <> 1  AND Email IS NOT NULL

Open in new window


It returns over 1000 records. It shows each record separately so I can't pass the data in the correct format, which should be (Example with only 2 records)

[{
"first_name": "Jhon3",
"last_name": "Smith3",
"email_addresses": 
[
 {
 "email_address": "js3@systems.com"
 }
 ]
}
,
{
"first_name": "Jhon5",
"last_name": "Smith5",
"email_addresses": 
[
 {
 "email_address": "js5@systems.com"
 }
 ]
}
]

Open in new window


How can I tweal the sql so that it returns the records in the above format ?
Comment
Watch Question

Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
Try following:

declare @json_result [nvarchar](max) = ''

SELECT @json_result += case when len(@json_result) = 0 then '' else ',
' +
'{
  "first_name": "' + FirstNm + '",
  "last_name": "' + LastNm + '",
  "email_addresses": [
    {
      "email_address": "' + email + '"
    }
  ]
}' 
FROM dbo.Users
WHERE UserType = 'contact' AND Archivedcont <> 1  AND Email IS NOT NULL

select '[
' + @json_result + '
]'

Open in new window


Not tested in absence of data. Feel free to do the minor tweaking, if required.

Author

Commented:
I get a syntax error on like 15 near contact

Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'contact'.

Open in new window

Author

Commented:
Attaching error. I think it has to do with the ' before FROM dbo.Users
Screenshot_1.png
Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
You will also got to be careful with double quotes, single quotes, etc. embedded in your data as in D'Souza!

Single quote is not an issue, I suppose with json. And double quotes are not likely to be there.

What do you think?

Author

Commented:
I tried to parse the one line I get as a result but I get an error in the JSON formatting.

Error: Parse error on line 1:
...s": [      {        "email
-----------------------^
Expecting 'STRING', '}', got 'undefined'

I am using this tool:  http://www.jsoneditoronline.org
I will try and post data I can have here in a few minutes or tomorrow morning, its pretty late on this time zone.

Author

Commented:
Its possible the tool I use to check the JSON is truncating the data. I will try with less data.
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
Okay...also is any of your data is null?

Author

Commented:
That seems to be the issue. I added another 'WHERE' clause and I only got 8 results this time instead of 1000 and checked the JSON formatting and its valid. Thanks so much!

Author

Commented:
I updated the query to make sure NULL was not the issue, it was being truncated by the JSON validation tool which only allows a number of characters.  Thanks for the help !
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
No. The select statement in SQL server management studio can show only about 8K of data max. So it's the SSMS issue.

Author

Commented:
Worked perfectly!

Author

Commented:
Ah ... ok got it!  Good to know. Although when I run the query on the ASP it will bring all the data back right ?
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
Yes! Definitely. ASP.NET String data type max limit 2GB. That is double of nvarchar(max) which is 1GB (I guess!).

More than sufficient for the purpose, hopefully!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.