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
– PM 77-1, Commented Dec 5, 2020 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
– friltase, Commented Dec 5, 2020 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
– PM 77-1, Commented Dec 5, 2020 at 17:47
And additional quotation marks (") at the begging and at the end of the parameters entry. — friltase
– friltase, Commented Dec 5, 2020 at 17:53

Barbaros Özhan · Accepted Answer · 2020-12-05 20:23:18Z

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 5, 2020 at 20:23

answered Dec 5, 2020 at 19:45

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

friltase Over a year ago

Thanks, this does what i need.

Barbaros Özhan Over a year ago

you're welcome @friltase , welcome to contribution for SO too.

Sudhir Gaurav · Accepted Answer · 2023-07-16 08:39:24Z

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, 2023 at 8:39

Sudhir Gaurav

1071 silver badge4 bronze badges

Collectives™ on Stack Overflow

Convert table to JSON array with longtext column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related