0

I Have this JSON colunm in my MYSQL (one row):

[{"motivo": {"id": 60}, "documento": {"id": 3}, "motivoStr": "Comprovante de Endereco"},
{"motivo": {"id": 61}, "documento": {"id": 4}, "motivoStr": "Comprovante de Renda"}]

I need format this in mysql Query, to concat all "motivoStr" with delimiter, example:

select ANY_COMMAND from a;

Output:

Comprovante de Endereco<br>Comprovante de Renda

The command JSON_TABLE dont work because the version of server is: 5.7.12

Milton Neto
  • 11
  • 2

1 Answers1

1
SELECT test.id, GROUP_CONCAT(jsontable.motivoStr SEPARATOR '<br>') output
FROM test
CROSS JOIN JSON_TABLE(test.jsonvalue,
                      '$[*]' COLUMNS (motivoStr VARCHAR(255) PATH '$.motivoStr')) jsontable
GROUP BY test.id;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7e0d28bc1d5301d3138ed1cd0d5fa051

I forgot to mention the version, in this case it's the 5.7 – Milton Neto

What is maximal amount of objects per value (JSON array)? – Akina

in this case is five – Milton Neto

The solution applicable to MySQL 5.7+ and up to 5 objects per array:

SELECT test.id, 
       GROUP_CONCAT(JSON_EXTRACT(test.jsonvalue, 
                                 CONCAT('$[', 
                                        numbers.num, 
                                        '].motivoStr'))
                    SEPARATOR '<br>') output
FROM test
CROSS JOIN ( SELECT 0 num UNION
             SELECT 1 UNION
             SELECT 2 UNION
             SELECT 3 UNION
             SELECT 4 ) numbers
GROUP BY test.id;

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=2f9ab2d95ffe6a58a96dffb0cde45511

Akina
  • 39,301
  • 5
  • 14
  • 25