Error while accessing json array stored in MySQL DB

Question

In one of my sql table's row there's a column stores JSON array. I can retrieve it via node JS but when I try to do json array stuff I get errors all the time. My Json array is as follows in sql DB.

{ characters: [{ code: "45", col: "", rem: "" },{ code: "", col: "", rem: "" },{ code: "", col: "", rem: "" },{ code: "", col: "", rem: "" }]}

I tried like this after getting via async functions

console.log(result[0].init_characters)

Sure data shows. But when I try to do,

console.log(result[0].init_characters.characters[1].code)

it gives errors like '1' is not defined. How can I get 1st arrays 'code' which is '45'?

Tried several methods like below. STill no luck

const myJSON = JSON.stringify(tt); 
const obj = JSON.parse(myJSON);
console.log(obj.characters[1].code);

Still no luck

Edit:Code that retrievs data

await connection.query( charSessSearch_query, async (err, result) => { if (err) throw err; console.log("------> done Results 1"); console.log(result.length); var tt = result[0].init_characters; const myJSON = JSON.stringify(tt); const obj = JSON.parse(myJSON); )

Yes this is mysql

Hi there. You should add the code that retrieves data. Also try logging the results of retrieval that'll help you understand issue better. — Omkar76
– Omkar76, Commented Apr 20, 2023 at 12:22
can you share the actual result data or result[0].init_characters response? and to get 45 it would be characters[0].code — Craicerjack
– Craicerjack, Commented Apr 20, 2023 at 12:33
@Omkar76 when result or result[0].init_characters is console logged it shows the array. But when result[0].init_characters..characters[1].code it says undefined — Cutting_edge
– Cutting_edge, Commented Apr 20, 2023 at 12:38
1/ Provided string with { characters: ... is not valid JSON - Make sure you quote the keys 2/ The provided string is converted again with JSON.stringify into yet another string 3/ Finally you should parse it again but it's string again (not JSON object) — vladaman
– vladaman, Commented Apr 20, 2023 at 12:52
@vladaman 1. as suggested in 1st answer i've fixed it like this {"characters":[{"code":"45","col":"","rem":""},{"code":"","col":"","rem":""},{"code":"","col":"","rem":""},{"code":"","col":"","rem":""}]} But after that I don't get to convert it to json object. Can you help me with that? — Cutting_edge
– Cutting_edge, Commented Apr 20, 2023 at 13:00

Soso · Accepted Answer · 2023-04-20 12:42:05Z

0

I want to let you know about 2 things.

Correctly, the json array should be like this in the sql db. now the format is a bit wrong I think.

{"characters":[{"code":"45","col":"","rem":""},{"code":"","col":"","rem":""},{"code":"","col":"","rem":""},{"code":"","col":"","rem":""}]}

And to get "45" as you want, the index should be "0" not "1"!

hope this would be helpful to you.

answered Apr 20, 2023 at 12:42

Soso

701 silver badge5 bronze badges

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

6 Comments

Soso Over a year ago

if it still doesn't work, please try to put the index to variable and use that instead. ex: const index = 0; console.log(obj.characters[index].code);

Cutting_edge Over a year ago

When i run as that it shows following error "TypeError: Cannot read properties of undefined (reading '0')"

Soso Over a year ago

@Cutting_edge, can you show me the log for result[0].init_characters.characters please?

Cutting_edge Over a year ago

TypeError: Cannot read properties of undefined (reading '0') at Query.<anonymous> (<location>.js:449:54) at Query.<anonymous> (<location>\node_modules\mysql\lib\Connection.js:526:10) at Query._callback (D<location>\node_modules\mysql\lib\Connection.js:488:16) at Sequence.end (<location>\node_modules\mysql\lib\protocol\sequences\Sequence.js:83:24) at Query._handleFinalResultPacket (<location>node_modules\mysql\lib\protocol\sequences\Query.js:149:8) at Query.EofPacket (<location>\node_ ...... Node.js v18.15.0

Soso Over a year ago

@Cutting_edge, let's keep your code then.

I tried like this after getting via async functions console.log(result[0].init_characters) Sure data shows. But when I try to do, console.log(result[0].init_characters.characters[1].code) it gives errors like '1' is not defined.

it means, the array is empty when you console log, but filled later. so maybe, if you try console.log(JSON.stringify(result[0].init_characters)), it won't show as you expected. could you have a check please? you need to await for the result and operate with them when it's ready exactly.

|

Collectives™ on Stack Overflow

Error while accessing json array stored in MySQL DB

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related