Search code examples
mysqlsqlmysql-json

sql concatenate values of an array of jsons

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.

Solution

  • 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

    See fiddle