Can't do JSON.parse on data from SQL Server

Question

I have a query that gets data from SQL Server, and in one of the fields we are storing JSON as a varchar(max). The issue is that when I try to get the data out in my Node app and do a JSON.parse, it's not working. Here's what I've tried:

console.log(data.attr);
    // "{ firstName: 'Preston', lastName: 'Lamb' }"
JSON.parse(data.attr) 
    // Invalid expression: unexpected end of input (get this on one item)
    // invalid expression: unexpected token f in json at position 2 (and this on the other)
newJsonStr = JSON.stringify(data.attr)
    // ""{ firstName: 'Preston', lastName: 'Lamb' }""
newJson = JSON.parse(newJsonStr)
    // "{ firstName: 'Preston', lastName: 'Lamb' }"

None of this is really complicated stuff...it should be easy to JSON.parse and/or JSON.stringify, but it doesn't work. Any ideas at all?

That is not JSON. In JSON both the keys and string values are wrapped in double quotes. — trincot
– trincot, Commented May 4, 2017 at 20:08
From the JSON spec: 'A string is a sequence of zero or more Unicode characters, wrapped in double quotes, using backslash escapes' — Jared Smith
– Jared Smith, Commented May 4, 2017 at 20:12
You can try to eval it instead: eval("(" + data.attr + ")"); — Joseph Marikle
– Joseph Marikle, Commented May 4, 2017 at 20:12
You should fix the code that fills in the database so it's correct JSON. — Barmar
– Barmar, Commented May 4, 2017 at 20:48

Community · Accepted Answer · 2017-05-23 12:02:22Z

1

What the other commentator meant by it's not JSON is that your keys need to be wrapped in quotes to be parsed as JSON.

JSON Spec - does the key have to be surrounded with quotes?

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered May 4, 2017 at 20:11

chairmanmow

6507 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Steven Stark Over a year ago

Whenever I encounter a JSON error like this, I will always verify the data using JSON Lint, available: Website version: jsonlint.com NPM CLI version: npmjs.com/package/jsonlint

pjlamb12 Over a year ago

Okay, when I inserted manually into the database I forgot that. But putting double quotes around attributes and values, and even escaping them, still causes issues. Just trying to parse the response is where the invalid expression: unexpected end of json input error came from, and when you JSON.stringify it first it adds doesn't do anything.

pjlamb12 Over a year ago

@StevenStark After using JSON lint, I could see that one of those fields was missing a double quote, and so it wasn't parsing correctly. I'll have to work on the other field to make sure to add it properly to the database. Thanks!

Barmar Over a year ago

@pjlamb12 Don't try to create JSON by hand. Most languages have library functions to create JSON, you should use it.

Collectives™ on Stack Overflow

Can't do JSON.parse on data from SQL Server

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related