I'm using mariaDB 10.3, I have a table:
CREATE TABLE user(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, parameters longtext, PRIMARY KEY(id));
With rows:
INSERT INTO user VALUES (1, 'name1', '{"number": 1, "text": "some text"}'), (2, 'name2', '{"number": 2, "text": "some more text"}');
I'm trying to write query that returns the table as JSON
object.
So far I have
SELECT CONCAT(
'[',
GROUP_CONCAT(JSON_OBJECT('id',id,'name',name,'parameters', parameters)),
']'
)
FROM user;
But this returns:
[
{"id": 1,
"name": "name1",
"parameters": "{\"number\": 1, \"text\": \"some text\"}"
},
{
"id": 2,
"name": "name2",
"parameters": "{\"number\": 2, \"text\": \"some more text\"}"
}
]
which is not a proper JSON. What should I change to get parameters
properly formatted?
What I would like to get is:
[
{
"id": 1,
"name": "name1",
"parameters": {
"number": 1,
"text": "some text"
}
},
{
"id": 2,
"name": "name2",
"parameters": {
"number": 2,
"text": "some more text"
}
}
]
Thanks
Just JSON_COMPACT
function, which's proper to MariaDB and does not exists in MySQL, might be applied for the parameters
column
SELECT CONCAT(
'[',
GROUP_CONCAT(JSON_OBJECT('id',id,
'name',name,'parameters',
JSON_COMPACT(parameters))),
']'
) AS "JSON Value"
FROM user