postgresql 如何将值的文本数组与包含需要SUM的数据的嵌套JSONB对象进行比较?

z31licg0  于 2023-05-28  发布在  PostgreSQL
关注(0)|答案(1)|浏览(123)

我正在为一个游戏在现有的数据库上做一些查询/计算,我对postgres和json数据访问不是很熟悉。即使是绿色的,我也意识到这些数据并没有以任何接近最佳格式的方式存储。我对这些数据只有读访问权,优化或重新排序不是我的。
PostgreSQL 15.2
作为一个背景:
游戏是一个回合制游戏,玩家在两个团队中的一个,并在一个网格上设置“射击”,战舰风格。
我有一个users表,其中包含'uid'(text)和'games'(text[])列。这些“游戏”id表示用户(uid)所关联的未完成和已完成的游戏。
| uid|游戏|
| - -----|- -----|
| 一二三四|[12、14、17]|
| 四三一二|[56、57、59]|
我还有一个finished_games表,其中包含'game_id'(整数)和'playbook'(jsonb)列。有许多游戏ID,每个表示“已完成”或已结算的游戏,该游戏现在是静态的并且完全结束。
| 游戏ID|剧本|
| - -----|- -----|
| 十五|jsonbdata|
| 十四|jsonbdata|
用户的“games”数组是用户曾经关联的所有game_id的列表。有些已经完成,有些正在进行。
下面是一个存储在“playbook”下的json数据的例子:

{
  "seed": "blah",
  "players": [
    {
      "uid": "0x6blah",
      "username": "dianaprince",
      "game_plan": {
        "attack": [
          {
            "value": "C5"
          },
          {
            "value": "A2"
          },
          {
            "value": "B2"
          },
          {
            "value": "C8"
          }
        ],
        "defend": [
          {
            "value": "C7"
          }
        ]
      }
    },
    {
      "uid": "0xBblah",
      "username": "brucewayne",
      "game_plan": {
        "attack": [
          {
            "value": "B6"
          },
          {
            "value": "C7"
          },
          {
            "value": "A9"
          },
          {
            "value": "C9"
          }
        ],
        "defend": [
          {
            "value": "A4"
          }
        ]
      }
    }
  ],
  "gameplay": [
    {
      "seed": "blah",
      "team1": [
        {
          "uid": "0x6blah",
          "dead": false,
          "kills": 0
        }
      ],
      "team2": [
        {
          "uid": "0xBblah",
          "dead": false,
          "kills": 0
        }
      ]
    },
    {
      "seed": "blah",
      "team1": [
        {
          "uid": "0x6blah",
          "dead": false,
          "kills": 0
        }
      ],
      "team2": [
        {
          "uid": "0xBblah",
          "dead": false,
          "kills": 0
        }
      ]
    },
    {
      "team1": [
        {
          "uid": "0x6blah",
          "dead": true,
          "kills": 0
        }
      ],
      "team2": [
        {
          "uid": "0xBblah",
          "dead": false,
          "kills": 1
        }
      ]
    }
  ]
}

正如您所看到的,“kills”是按uid存储的(回合的值为n;换句话说,它不是游戏的运行总数,而是每个回合),然后该数据是每个团队(两个可能的团队),然后该数据是每个回合,然后在“游戏性”下。
我需要创建一个查询,让我所有的“uid的”用户,然后给我一个总数的“完成的游戏”,用户参加了以及一些“总杀死在所有完成的游戏”。
如果我只知道如何正确地做其中一个,我想我可以弄清楚另一个。这只是比我目前的技能水平高一点,我需要学习。
假设'users u'和'finished_games fg'...
我在www.example.com和fg.playbook之间的重叠操作符上尝试了users和finished_games之间的各种JOIN的变体u.games(以及一百万种不同的查询json的方法,这就是我迷路的地方)。我还尝试了一个巨大的子查询链,由于我自己的经验不足和逻辑错误或对如何正确深入json数据的误解,得到了错误的结果。
我的最后一次尝试是使用jsonb_path_query解包fg.playbook,但是在JOIN条件中不允许使用返回集合的函数。我的查询结构完全不正确。
我所期望的结果集应该是这样的:
| uid|完成击杀|游戏_参与|
| - -----|- -----|- -----|
| 细胞1|二号牢房|3号牢房|
| 4号牢房|5号牢房|6号牢房|
其中uid是文本uid,finished_kills包含一个int,该int具有在所有finished_games内针对该uid的击杀数,并且其中games_participated包含一个int,该int具有仅来自finished_games表的与uid相关联的游戏数(相对于u.games所有游戏的“www.example.com”列表,无论它们是否完成)。

sg2wtvxw

sg2wtvxw1#

这是一个非常简单和直接的例子,可以说明为什么将外键存储在数组中并将表格数据存储在嵌套的JSON对象中是一个好主意:

WITH k AS (
   SELECT f.game_id,
          c.u ->> 0 AS uid,
          count(*) AS kills
   FROM finished_games AS f
      CROSS JOIN LATERAL jsonb_path_query(
                            f.playbook,
                            'strict $.**[*] ? (@.kills == 1).uid'
                         ) AS c(u)
   GROUP BY f.game_id, c.u ->> 0
)
SELECT u.uid, k.kills
FROM users AS u
   CROSS JOIN LATERAL unnest(u.games) AS g(game_id)
   JOIN k
      ON u.uid = k.uid
         AND g.game_id::integer = k.game_id;

公共表表达式提取所有对象的uid,其中kills为1,并将其与finished_games连接,主查询从users表中提取所有game_id,并与CTE连接。JSONPATH的强大功能使之成为可能。

相关问题