I know about the possiblity of duplicity of question but i don't found anything to help me on my situation about it.
I have this json on my mysql column:
[
{
"ddi": "55",
"routing_id": "7",
"price": {
"mt": 0.0285,
"mo": 0.0285
}
},
{
"ddi": "598",
"routing_id": "10",
"price": {
"mt": 0.06,
"mo": 0.06
}
}
]
When i do the following query:
SELECT JSON_EXTRACT(my_column,'$[*].ddi') as ddi FROM my_table
I get the result:
["55", "598"]
My question is: There is a way to return this data on rows instead a json array? Like:
Ddi
55
598
You can use JSON_TABLE for the same
SELECT get_ddi.*
FROM my_table,
JSON_TABLE(my_column, '$[*]' COLUMNS (
my_column VARCHAR(40) PATH '$.ddi')
) get_ddi;
**Schema (MySQL v8.0)**
DROP TABLE IF EXISTS `my_table`;
CREATE TABLE IF NOT EXISTS `my_table` (
`id` SERIAL,
`my_column` JSON
);
INSERT INTO `my_table` (`id`, `my_column`)
VALUES
(1, '[
{
"ddi": "55",
"routing_id": "7",
"price": {
"mt": 0.0285,
"mo": 0.0285
}
},
{
"ddi": "598",
"routing_id": "10",
"price": {
"mt": 0.06,
"mo": 0.06
}
}
]');
**Query #1**
SELECT get_ddi.*
FROM my_table,
JSON_TABLE(my_column, '$[*]' COLUMNS (
my_column VARCHAR(40) PATH '$.ddi')
) get_ddi;
**Output**
| my_column |
| --------- |
| 55 |
| 598 |
As per @Guilherme Mascarenhas comments below, the solution needed was for MariaDb. As of version 10.2.31, JSON_TABLE function doesn't exist for MariaDB.
A hacky solution could be to use appropriate mariadb sequence table(Depending on the number of rows). JSON_UNQUOTE removes the quote from the extracted value. seq is used as index to get the specified ddi value from the array.
SELECT
JSON_UNQUOTE(JSON_EXTRACT(t.my_column, CONCAT('$[', seq_0_to_100.seq, '].ddi'))) AS getddi
FROM my_table t
JOIN seq_0_to_100
HAVING getddi IS NOT NULL;
**Output**
| my_column |
| --------- |
| 55 |
| 598 |