3

I have a table 

X

 - id
 - name (eg: 'testing')
 - array_column_name (eg: ["value1","value2"])

I want a query that will return the following output.

_____________________
| column1 | column2 |
| testing | value1  |
| testing | value2  |
|_________|_________|

Essentially what I need is to convert the array to rows.

I tried using 

SELECT name, JSON_EXTRACT(@'array_column_name', '*') FROM X;

But I am not sure what to put as the second parameter of the JSON_EXTRACT since my columns contain a plain array instead array of objects.

GGadde
  • 391
  • 1
  • 14

1 Answers1

0

If you are using MySQL >= 8.0.4, you can use JSON_TABLE, otherwise, to achieve what you need will be somewhat less simple:

SELECT
  `X`.`name` `column1`,
  `der`.`column2`
FROM
  `X`,
  JSON_TABLE(
    `array_column_name`,
    '$[*]'
    COLUMNS(
      `column2` VARCHAR(255) PATH '$'
    )
  ) `der`;

See db-fiddle.

wchiquito
  • 16,177
  • 2
  • 34
  • 45