sql concatenate values of an array of jsons

Question

In mysql, I have a table with a json column of example data:

{
    "fruits": [
        {"id": 1, "name": "apple", "amount": 3},
        {"id": 3, "name": "banana", "amount": 5}
    ]
}

how to i get a column of concatenated values of fruit ids?

expected result value: "1,3"

I tried:
select json_value(col, '$.fruits[*].id') as ids from table
but resulted in a column of null values.

Out of curiosity, why are you using JSON for this data? Would it be simpler if you stored the data in a second table `fruits` with columns `id`, `name`, `amount`? — Bill Karwin, Mar 16 '23 at 03:57
@BillKarwin I need multiple arrays of ordered `fruits`(100+), and making each one into a table would make it harder to access/manage them at once. — 이준서, Mar 16 '23 at 05:12
also, turning the array into a table and using incremented ids to order the elements would make it harder to manually add or remove elements — 이준서, Mar 16 '23 at 05:23

score 1 · Accepted Answer · answered Mar 16 '23 at 02:54

1

You can use json_table:

select group_concat(t1.val) from tbl t 
cross join json_table(t.js, '$.fruits[*]' columns(val text path '$.id')) t1

answered Mar 16 '23 at 02:54

Ajax1234

1 Answers1