MariaDB: How to return JSON array instead of string
0
votes
0
answers
1754
views
I am trying to get MariaDB(v.10.3.34) to return a JSON array, but it's returned as a string instead.
I'm reading the returned value in Node.js, where the returned array is a string instead of an array. If I want to pass it as an array to the frontend, I need to do JSON.parse() on each JSON column of each row, which I would preferably not do, and instead let MariaDB return the value as an JSON array to begin with.
So for an example, if I have a table like this:
CREATE TABLE method (
id int(11) NOT NULL,
blocks JSON DEFAULT NULL,
PRIMARY KEY (id)
);
INSERT INTO method VALUES (1,JSON_ARRAY(1,2)),(2,JSON_ARRAY(3,4));
If I then do:
SELECT id, blocks FROM method;
I get the following object back to Node.js:
[{"id":1, "blocks":"[1,2]"},{"id":2, "blocks":"[3,4]"}]
When instead I would want the arrays unquoted:
[{"id":1, "blocks":[1,2]},{"id":2, "blocks":[3,4]}]
Is there a way to do this?
In a newer version of MariaDB (v.10.6.8) I got this working with:
SELECT id, JSON_EXTRACT(blocks, '$[*]') AS blocks FROM method;
But unfortunately our production server is stuck with the older version for now at least, and on that older version the returned value was still a string.
Asked by TTT
(1 rep)
Sep 2, 2022, 01:28 PM