0

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.

1

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.