json - 嵌入式 json 对象的唯一索引

Question

我现在正在测试 Postgresql 9.4 beta2。我想知道是否可以在嵌入式 json 对象上创建唯一索引？

我创建了一个表名products:

CREATE TABLE products (oid serial primary key, data jsonb)

现在，我尝试将 json 对象插入到数据列中。

{
    "id": "12345",
    "bags": [
        {
            "sku": "abc123",
            "price": 0,
        },
        {
            "sku": "abc123",
            "price": 0,
        }
    ]
}

但是，我希望包的 sku 是唯一的。这意味着 json 无法插入到产品表中，因为 sku 在这种情况下不是唯一的。

我尝试创建如下所示的唯一索引，但失败了。

CREATE UNIQUE INDEX product_sku_index ON products( (data->'bags'->'sku') )

有什么建议吗？

Answer 1

由于多种原因，您尝试在表达式上创建UNIQUE INDEX 注定会失败。

在产品上创建唯一索引 product_sku_index((data->'bags'->'sku'))

第一个也是最微不足道的是......
data->'bags'->'sku'
不引用任何。您可以使用

引用数组的 第一个元素

data->'bags'->0->>'sku'

或更短:

data#>>'{bags,0,sku}'

但是那个表达式只返回数组的第一个值。
您的定义:“我希望袋子的 sku 是独一无二的” .. 不清楚。您希望 sku 的值是唯一的吗？在 data 列中的一个 JSON 对象或所有 json 对象之间？或者您想将数组限制为具有 sku 的单个元素？

无论哪种方式，这些目标都不能通过简单的 UNIQUE 索引来实现。

可能的解决方案

如果您希望 sku values 在 data->'bags' 中的所有 json 数组中是唯一的，有一种方法。取消嵌套数组并将所有单独的 sku 值写入具有唯一(或 PK)约束的简单辅助表中的分隔行:

CREATE TABLE prod_sku(sku text PRIMARY KEY);  -- PK enforces uniqueness

此表可能对其他用途有用。
这是一个完整的代码示例，用于解决与普通 Postgres 数组非常相似的问题:

• Can PostgreSQL have a uniqueness constraint on array elements?

只采用取消嵌套技术。而不是:

DELETE FROM hostname h
USING  unnest(OLD.hostnames) d(x)
WHERE  h.hostname = d.x;

...

INSERT INTO hostname(hostname)
SELECT h
FROM   unnest(NEW.hostnames) h;

使用:

DELETE FROM prod_sku p
USING  jsonb_array_elements(NEW.data->'bags') d(x)
WHERE  p.sku = d.x->>'sku';

...

INSERT INTO prod_sku(sku)
SELECT b->>'sku'
FROM   jsonb_array_elements(NEW.data->'bags') b

那个的详细信息:

• PostgreSQL joining using JSONB