0

I am trying to create some input data for an ApexChart (bar-Chart) from SQL query (of type: return single value). The bar chart would accept data input in the following form:

[ {x:"1 author", y:15 }, {x:"3 authors", y:55}, {x:"15 authors", y:25} ]

The SQL return similar to following result:

"[{x:"1", y:14},{x:"1", y:15},{x:"1", y:19}]"

If I directly copy SQL result from SQL Developer console and paste it in my script, it works fine, but that is not the cleanest way to do this task. Ideally, the data return from this query would better be directly read from file item in Apex and automatically get parsed in required input data format (i.e., array of objects). Let say, the hidden item name is "P3_ITEM".

P3_ITEM.value

will give us single value string concatenated result of SQL query.

"[{x:"1", y:14},{x:"1", y:15},{x:"1", y:19}]"

removing starting and ending double quotes wont solve, because it is still of type string. and converting this string to array of objects also doesn't work, because it is then array of strings not objects.

So, the question is, how to create an array of objects derived from this string value (single value sql result)?

mGm
  • 264
  • 2
  • 12
  • 1
    What version of the database are you using? You already have access to APEX_JSON, but you might have access to native JSON generation functions too if you're using 12.2+. – Dan McGhan Apr 28 '20 at 20:34

1 Answers1

0

JSON.parse will turn a stringified json object to a javascript object.

As discussed in the comments, your output is not valid JSON so it can't be interpreted as such.

The right way to fix this:

Recommended way would be to store your data in a valid JSON format (this would also allow the use of SQL JSON functions) using double quotes around keys.

[{"x":1, "y":14},{"x":1, "y":15},{"x":1, "y":19}]

The wrong way to fix this:

You could use an evaluation to directly evaluate your output as javascript code using eval:

eval('[{x:"1", y:14},{x:"1", y:15},{x:"1", y:19}]');

And this is why it's bad. But also your system simply won't be standard.

J Dubuis
  • 492
  • 5
  • 13
  • JSON.parse(P3_ITEM.value) is giving me error: "SyntaxError: JSON.parse: expected property name or '}' at line " – mGm Apr 27 '20 at 08:40
  • It seems your keys are not quoted. Recommended way would be to store your data as actual JSON using proper JSON formatting. I'll edit my answer. – J Dubuis Apr 27 '20 at 12:52