Convert table to JSON array with longtext column

Question

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

(1) If you have just 1 row, what do you expect `GROUP_CONCAT` to accomplish? (2) Please add the desired result to your question. (Use [Edit]). — PM 77-1, Dec 05 '20 at 17:26
You are right, for simplicity I just showed 1 row, but 2 are minimum for this question to make sense. I also added desired result. — friltase, Dec 05 '20 at 17:43
OK. Do I understand correctly that escaped parenthesize (`\"`) is the only thing that separates you from success? Or have I missed something? — PM 77-1, Dec 05 '20 at 17:47
And additional quotation marks (") at the begging and at the end of the parameters entry. — friltase, Dec 05 '20 at 17:53

Barbaros Özhan · Accepted Answer · 2020-12-05T20:23:18.477

1

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

Demo

edited Dec 05 '20 at 20:23

answered Dec 05 '20 at 19:45

Barbaros Özhan

59,113
10
31
55

Thanks, this does what i need. – friltase Dec 05 '20 at 21:08
you're welcome @friltase , welcome to contribution for SO too. – Barbaros Özhan Dec 05 '20 at 21:09

score 0 · Answer 2 · answered Jul 16 '23 at 08:39

0

You can use JSON_OBJECT , JSON_ARRAYAGG(expr) concept to get your result. ref: https://www.tutorialspoint.com/mysql/mysql_aggregate_functions_json_arraygg.htm

I used this for one of my project work

answered Jul 16 '23 at 08:39

Sudhir Gaurav

107
1
4

Convert table to JSON array with longtext column

2 Answers2