大家好,我有一个表,其中的一个字段是json,格式如下:
{
"monday": [{"end": "18:00:00", "start": "09:00:00"}, {"end": "21:00:00", "start": "19:00:01"}],
"sunday": [{"end": "15:00:00", "start": "14:00:00"}],
"saturday": [{"end": "18:00:00", "start": "09:00:00"}]
}
我想知道是否可以在SELECT查询中返回所有键及其数组项,如下所示:
day | date_start | date_end |
---|---|---|
monday | 09:00:00 | 18:00:00 |
monday | 19:00:01 | 21:00:00 |
sunday | 14:00:00 | 15:00:00 |
saturday | 09:00:00 | 18:00:00 |
我试过这样做:
SELECT j.*
FROM tabela t
INNER JOIN JSON_TABLE(
t.field_json,
'$.*[*]'
COLUMNS(
`date_start` TIME PATH '$.start',
`date_end` TIME PATH '$.end',
`day` VARCHAR(10) PATH '$.*'
)
) AS j
WHERE t.id=100
但结果并不是我想要的:
day | date_start | date_end |
---|---|---|
NULL | 09:00:00 | 18:00:00 |
NULL | 19:00:01 | 21:00:00 |
NULL | 14:00:00 | 15:00:00 |
NULL | 09:00:00 | 18:00:00 |
Db:https://dbfiddle.uk/9rXd_VL2
有什么建议吗?