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

CodePudding user response：

You should save the data normalized. JSONm will be always a bother

Mysql 5.7 solution

CREATE TABLE A (val TEXT)

INSERT INTO A Values ('{"repeat-test0":{"main":"test"},"repeat-test1":{"main":"test2"},"repeat-test2":{"main":"test3"}}'),
('{"repeat-test0":{"main":"testx"},"repeat-testx1":{"main":"testx2"},"repeat-test2":{"main":"test3"}}')

SELECT REPLACE(REPLACE(REPLACE(JSON_EXTRACT(val, '$.*.main'),'[',''),'"',''),']','') FROM A

| REPLACE(REPLACE(REPLACE(JSON_EXTRACT(val, '$.*.main'),'[',''),'"',''),']','') |
| :---------------------------------------------------------------------------- |
| test, test2, test3                                                            |
| testx, test3, testx2                                                          |

db<>fiddle here

Mysql 8 Solution

CREATE TABLE A (val TEXT)

INSERT INTO A Values ('{"repeat-test0":{"main":"test"},"repeat-test1":{"main":"test2"},"repeat-test2":{"main":"test3"}}'),
('{"repeat-test0":{"main":"testx"},"repeat-testx1":{"main":"testx2"},"repeat-test2":{"main":"test3"}}')

SELECT REGEXP_REPLACE(JSON_EXTRACT(val, '$.*.main'),'[\\[\\]"]','') FROM A

| REGEXP_REPLACE(JSON_EXTRACT(val, '$.*.main'),'[\\[\\]"]','') |
| :----------------------------------------------------------- |
| test, test2, test3                                           |
| testx, test3, testx2                                         |

db<>fiddle here