我们可以有许多请求来将json存储到数据库中。有时我们需要将json键集转换为表行。如何在mysql表中提取完整的json?
i、 e.具有以下json和要求是将每个集合存储为表中的一行。
'{
"log": [
{
"datetime": "2017-10-25 07:19:55",
"Activity": "Activity 1"
},
{
"datetime": "2017-10-25 07:20:05",
"Activity": "Activity 2"
},
{
"datetime": "2017-10-25 07:20:31",
"Activity": "Activity 3"
},
{
"datetime": "2017-10-25 07:20:31",
"Activity": "Activity 4"
},
{
"datetime": "2017-10-25 07:21:03",
"Activity": "Activity 5"
},
{
"datetime": "2017-10-25 13:56:42",
"Activity": "Activity 6"
},
{
"datetime": "2017-10-25 13:56:53",
"Activity": "Activity 7"
},
{
"datetime": "2017-10-25 13:57:03",
"Activity": "Activity 8"
},
{
"datetime": "2017-10-25 13:57:04",
"Activity": "Activity 9"
},
{ "datetime": "2017-10-25 13:57:53",
"Activity": "Activity 10"
},
{
"datetime": "2017-10-25 13:57:59",
"Activity": "Activity 11"
},
{
"datetime": "2017-10-25 13:58:01",
"Activity": "Activity 12"
},
{
"datetime": "2017-10-25 13:58:01",
"Activity": "Activity 13"
}
]
} '
2条答案
按热度按时间bjp0bcyl1#
json可以存储在具有不同数据类型的数据库中。i、 例如json、vacrhar、文本等。对于导出报表或过滤json元素值,我们应该有不同的解决方案来完成。
创建一个表来在mysql表中存储json。
将示例数据插入表中。json字段数据插入到json数据类型中,但也可以是varchar或text类型。
查询以将json导出为json长度值为的json数组
WITH RECURSIVE CTE_json (id, array_datetime, array_message,seq) AS
(
SELECT id,
JSON_EXTRACT(JSON_EXTRACT(activity_log, '$.datetime'), CONCAT('$[', 0, ']')),
JSON_EXTRACT(JSON_EXTRACT(activity_log, '$.Activity'), CONCAT('$[', 0, ']')),
0 as seq
FROM test_Json
UNION ALL
SELECT TJ.id,
JSON_EXTRACT(JSON_EXTRACT(TJ.activity_log, '$.datetime'), CONCAT('$[', rc.seq +1, ']')) as array_datetime,
JSON_EXTRACT(JSON_EXTRACT(TJ.activity_log, '$.Activity'), CONCAT('$[', rc.seq +1, ']')) as array_message,
rc.seq +1 AS seq
FROM CTE_json rc
INNER JOIN test_Json TJ ON rc.id = TJ.id
WHERE rc.seq+1 < JSON_LENGTH(JSON_EXTRACT(TJ.activity_log, '$**.datetime'))
)
SELECT *
FROM CTE_json cj
ORDER BY id, seq;
ogsagwnx2#
mysql 8.0有一个json表函数: