假设我们的数据库中有几个主表(a、b 和 c),然后另一个 (x) 存储一个复杂的、仅半可预测的 JSONb 对象,该对象引用了所有主表。就我而言,JSONb 看起来像这样:
{
entries: [
[
[
{table: 'a', id: '1'},
{table: 'b', id: '4', entries: [
{table: 'a', id: '3'},
{table: 'a', id: '1'}
...
]},
{table: 'c', id: '5', entries: [
{table: 'a', id: '2'},
{table: 'b', id: '4', entries: [
{table: 'a', id: '1'},
{table: 'a', id: '6'},
...
]},
...
]},
...
],
...
],
...
]
}
从表中选择记录时,x
我们希望按其他表的属性过滤结果 - 例如。仅x
包含a
具有包含特定枚举值的字段的记录的记录。
使用此 JSONb 数据结构在单个查询中执行此操作是否可能或完全有效?看起来这需要对 ID 进行一些认真的聚合,并且为每个查询执行此操作似乎需要大量工作。
我正在考虑的替代方案是保持 JSONb 字段不变,但也创建连接表(我可能在那里的名称错误)来跟踪所有表记录x
依赖项。因此,您将拥有表x_a
、x_b
、等,并且只为记录中弹出的每个、、 或IDx_c
存储一个唯一的连接记录。这样,在编写查询时,可以使用简单且非 JSON 的方法来执行常规连接过滤器。a
b
c
x
作为初级到中级 SQL 程序员,这似乎至少会产生更具可读性的代码,但是我不确定这是否违反“仅输入数据一次”规则。
欢迎任何和所有的意见,包括阅读有关如何做出这些决定的材料。
语言是PostgresQl
是的,就是这样。您的 JSONb 字段是非规范化的,如果您尝试从中连接表,则性能会很差。可能无法以可控制的方式执行此操作,并且需要在每次加入 JSON 时拆箱并扫描整个表。
您提到的“连接”表称为桥接表或链接表。它们也可能有助于消除 JSON 字段的重复数据。它们将在其关键字段上可索引,并且在连接时应该具有高性能。一般来说,JSON 实际上是一种非常糟糕的存储需要操作的数据的方式。最佳实践是仅将其用于仅写入和读取而不是操作的数据,否则该数据无法轻松标准化。
您不想在两个地方维护相同的数据,如果可能的话,保持正确。但为什么还要继续使用 JSON 列呢?消费者不应该关心保存数据的对象。如果是这样,您只需编写一个查询即可从表中创建 JSON 结构化结果集。您甚至可以将此查询保存到视图对象中,以便您可以根据需要轻松引用它。这样做可以让您不必在两个地方维护数据本身,否则可能会导致数据完整性和管理问题。