'Mysql get all string matches in single row

field_id item_id value
-- ----
21  4139 {"repeat-test0":{"main":"test"},"repeat-test1":{"main":"test2"},"repeat-test2":{"main":"test3"}}
21  4140  {"repeat-test0":{"main":"testx"},"repeat-testx1":{"main":"testx2"},"repeat-test2":{"main":"test3"}}

I am ultimately trying to get the output to be:

field_id item_id value
-- ----
21  4139 test,test2,test3
21  4140 testx,testx2,testx3

I need something that will iterate through the different "repeat-testx' to get all of the values.

I have tried substring_index (below) - this can work - but I do not know how many of the repeats may exist in a single row.

Here is my current query:

SELECT DISTINCT
    substring_index( substring_index( `vdc_fields_values`.`value`, '"repeat-test0":{"main":"',-( 1 )), '"}', 1 ) AS `id0`,
    substring_index( substring_index( `vdc_fields_values`.`value`, '"repeat-test1":{"main":"',-( 1 )), '"}', 1 ) AS `id1`,
    substring_index( substring_index( `vdc_fields_values`.`value`, '"repeat-test2":{"main":"',-( 1 )), '"}', 1 ) AS `id2`,
    substring_index( substring_index( `vdc_fields_values`.`value`, '"repeat-test3":{"main":"',-( 1 )), '"}', 1 ) AS `id3`
FROM
    `vdc_fields_values` 
WHERE
    (
    `vdc_fields_values`.`field_id` = 21
    AND `vdc_fields_values`.`item_id` =4139 )

Output is:

id0 id1 id2 id3
-- ---- --- ---
test  test2 test3 {"repeat-test0":{"main":"test
mysql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions