Iterating over list of JSON in Javascript from MySQL

Question

My JSON is stored in MySQL as this....

{'profile':'sweet', 'count':38},{'profile':'bitter', 'count':31},{'profile':'green', 'count':22}

When it's being returned as JSON from Express, it looks likes this...

[{"JSON":"{'profile':'sweet', 'count':38},{'profile':'bitter', 'count':31},{'profile':'green', 'count':22}"}]

which is valid JSON according to JSONLint.com

What throws me is iterating over it in a HTML javascript...

I have this in Javascript ....

   fProfiles_JSON = JSON.parse(xhr.responseText);
   console.log('Type of '+ typeof fProfiles_JSON); //yields "object"
   console.log('My object', fProfiles_JSON);
   console.log('LENGTH ', fProfiles_JSON.length); // Yields "1"

I get that I have to somehow iterate over this object type to get at the "profile" and "count" values but honestly, I'm not sure how since the length value is "1". I know this is probably dead simple and I'm just not seeing it. Can someone point me in the right direction?

That's not valid JSON. If you have multiple objects they need to be in an array. — Barmar
– Barmar, Commented Aug 30, 2021 at 21:58
Well it is valid JSON because it is packed into a string ... but obviously a single string. — Gwang-Jin Kim
– Gwang-Jin Kim, Commented Aug 30, 2021 at 22:01

Gwang-Jin Kim · Accepted Answer · 2021-08-31 14:29:08Z

1

var obj = [{"JSON":"{'profile':'sweet', 'count':38},{'profile':'bitter', 'count':31},{'profile':'green', 'count':22}"}]

// in this case we have to extract the string:
var string = obj[0]["JSON"]
var fProfiles = JSON.parse("[" + string + "]");
// as @Barmar pointed out, the string's content is not valid JSON.
// so we add at beginning and end square brackets to get a list
// of objects.
FProfiles.length // should be 3
// and you can access the `count` and `profile` attributes.

edited Aug 31, 2021 at 14:29

answered Aug 30, 2021 at 22:07

Gwang-Jin Kim

11.1k20 silver badges39 bronze badges

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

3 Comments

Jane Wilkie Over a year ago

Terrific Gwang-Jin Kim! Solved my problem. Extra thanks for explaining why JSONLint.com saw it as valid.

Gwang-Jin Kim Over a year ago

@JaneWilkie welcome! ;) - He was right in seeing there is a problem. But he was not right in that it is completely not valid. (as it is a string, it was valid - but the content was not valid JSON ;) ).

Barmar Over a year ago

This won't work with single quotes around the nested strings.

LMC · Accepted Answer · 2021-08-31 16:20:16Z

1

Json does not accept single quotes so they must be replaced

//xhr.responseText contents simulated with var
resp = "[{\"JSON\":\"{'profile':'sweet', 'count':38},{'profile':'bitter', 'count':31},{'profile':'green', 'count':22}\"}]";

j = JSON.parse(resp);
inner = j[0]['JSON'].replaceAll("'","\"");

objs = JSON.parse("[" + inner +"]");
objs[0]

Result:

Object { profile: "sweet", count: 38 }

As @barmar pointed out, "fixing" json with a custom parser is always a risk.
A slightly better attempt could be to replace single quotes with more specific regular expressions

# added possible 'key': 'value' at the end
resp = "[{\"JSON\":\"{'profile':'sweet', 'count':38},{'profile':'bitter', 'count':31},{'profile':'green', 'count':22},{'key99': 'value99'}\"}]";
j = JSON.parse(resp);

re3 = /[{]'/ig;
re4 = /'[}]/ig;
re1 = /'(, *|: *)'/ig;
re2 = /' *: *([0-9])/ig;
inner = j[0]['JSON'].replaceAll(re3,"{\"").replaceAll(re4, "\"}").replaceAll(re1, "\"$1\"").replaceAll(re2,"\":$1");
// "{\"profile\":\"sweet\", \"count\":38},{\"profile\":\"bitter\", \"count\":31},{\"profile\":\"green\", \"count\":22},{\"key99\": \"value99\"}"

objs = JSON.parse("[" + inner +"]");
// Array(3) [ {…}, {…}, {…} ]

edited Aug 31, 2021 at 16:20

answered Aug 30, 2021 at 22:20

LMC

14.4k3 gold badges34 silver badges62 bronze badges

1 Comment

Barmar Over a year ago

This won't work if there are any single quotes that should be treated literally. In general, ad hoc methods of "fixing" broken JSON are not robust. The problem should be fixed at the source.

Collectives™ on Stack Overflow

Iterating over list of JSON in Javascript from MySQL

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related