试图理解以json格式编写的数据库。我设法将主表转换为 csv 格式，但是表本身中有一些列是用 json 格式编写的。
有问题的表格描述了足球比赛，我遇到问题的属性提供了两支球队的详细信息(例如阵容、进球数等(。该属性提供两个主键/值对，键值是参加比赛的球队的 id，因此键在行与行之间变化。
我设法通过在如下所示的查询中手动编写 id 来访问一些信息(使用 team_id 1609(：
SELECT json_extract(teams_data, "$.1609") AS trial
FROM Matches_England
(有问题的列称为teams_data，有问题的表称为Matches_England(
然而，这显然不适用于每场比赛。
我正在寻找一种方法来访问 SQLite 中每一行的这两个值。
这是相关行的示例：
{**"1609"**: {"scoreET": 0, "coachId": 7845, "side": "home", "teamId": 1609, "score": 4, "scoreP": 0, "hasFormation": 1, "formation": {"bench": [{"playerId": 20612, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 25662, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 7864, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 230020, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 26010, "ownGoals": "0", "redCards": "0", "goals": "1", "yellowCards": "0"}, {"playerId": 7879, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 7870, "ownGoals": "0", "redCards": "0", "goals": "1", "yellowCards": "0"}], "lineup": [{"playerId": 370224, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 120339, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 7945, "ownGoals": "0", "redCards": "0", "goals": "1", "yellowCards": "0"}, {"playerId": 14869, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 25413, "ownGoals": "0", "redCards": "0", "goals": "1", "yellowCards": "0"}, {"playerId": 7868, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 3560, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 167145, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 3319, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 7882, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 49876, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}], "substitutions": [{"playerIn": 26010, "playerOut": 370224, "minute": 67}, {"playerIn": 7870, "playerOut": 120339, "minute": 67}, {"playerIn": 7879, "playerOut": 7945, "minute": 75}]}, "scoreHT": 2}, **"1631"**: {"scoreET": 0, "coachId": 333782, "side": "away", "teamId": 1631, "score": 3, "scoreP": 0, "hasFormation": 1, "formation": {"bench": [{"playerId": 119630, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 8498, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 350976, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 8066, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 285508, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 217078, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 283142, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}], "lineup": [{"playerId": 14763, "ownGoals": "0", "redCards": "0", "goals": "1", "yellowCards": "0"}, {"playerId": 192748, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 8013, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 8480, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 8653, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 149019, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 8488, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "94"}, {"playerId": 14853, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 265366, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}, {"playerId": 12829, "ownGoals": "0", "redCards": "0", "goals": "2", "yellowCards": "0"}, {"playerId": 26150, "ownGoals": "0", "redCards": "0", "goals": "0", "yellowCards": "0"}], "substitutions": [{"playerIn": 217078, "playerOut": 14763, "minute": 72}, {"playerIn": 285508, "playerOut": 192748, "minute": 82}, {"playerIn": 283142, "playerOut": 8013, "minute": 88}]}, "scoreHT": 2}}
在这种情况下，参加比赛的两个球队ID是1631和1609。如果我能够找到一种方法将这两个键/值对分成两个单独的列，只包括两个单独的值，那就足够了(因为团队 ID 再次嵌套在原始值中，所以我仍然可以从那里获取它(。
使用json_each将每个键/值对转换为一行。
select *
from json_each('{"123": {"a": 23, "b": 42}, "456": {"a": 5, "b": 7}}');
key value type atom id parent fullkey path
--- --------------- ------ ---- -- ------ ------- ----
123 {"a":23,"b":42} object 2 $.123 $
456 {"a":5,"b":7} object 8 $.456 $
然后使用值列。
select json_extract(things.value, '$.a') as a
from (
select * from json_each('{"123": {"a": 23, "b": 42}, "456": {"a": 5, "b": 7}}')
) things;
a
--
23
5
还有更直接地处理嵌套数据json_tree。
这显然很尴尬，而且会很慢，这都是字符串处理。如果可能的话，请执行此操作一次，并将数据规范化为表和行。