基于JsonB的PostgreSQL查询，用于在JSON数组中基于对象属性查找对象。

Question

我已经在StackOverflow上看到了一些关于这个问题的回答，但是它们对我都没有起作用。

{
    "data": {
        "list": [
            {"id": "2ac5bf6f-bc4a-49e8-8f9f-bc518a839981", "type": "type1"},
            {"id": "d15ac090-11ce-4c0c-a05d-d4238f01e8b0", "type": "type3"},
            {"id": "b98958fa-87c4-4dcc-aa84-beaf2b32c5c0", "type": "type1"},
            {"id": "854f4d2a-f37c-42cb-9a1f-17a15454a314", "type": "type2"},
            {"id": "555816da-4547-4a82-9e7e-1e92515bd82b", "type": "type2"},
            {"id": "0f7f4ced-61c2-45da-b15c-0e12058f66a7", "type": "type4"}

        ]
    }
}

这个Json被存储在一个叫做“questions”的字段中，现在我想查询这个表格中某个id在列表中的对象。比如说，如果我有一个id为555816da-4547-4a82-9e7e-1e92515bd82b，我希望返回：

{"id": "555816da-4547-4a82-9e7e-1e92515bd82b", "type": "type2"} 

我看到互联网上提供的解决方案（主要在这里），但没有起作用的方案如下:

SELECT questions->'data'->'list' 
FROM assignments 
WHERE id='81asd6230-126d-4bc8-9745-c4333338115c' 
AND questions->'data'->'list' @> '[{"id":"854f4d2a-f37c-42cb-9a1f-17a15454a314"}]';

我在多个不同的答复中看到了这个解决方案，但它并没有缩小数组范围，每次都返回完整的内容。where子句中的第一个id是我想要的特定作业对象的id，在这里基本上是无关紧要的。

SELECT questions->'data'->'list' 
FROM assignments 
WHERE id='81asd6230-126d-4bc8-9745-c4333338115c' 
AND questions->'data'->'list' @> '[{"id":"854f4d2a-f37c-42cb-9a1f-17a15454a314"}]';

这并不返回任何内容。

有没有人有关于如何轻松实现这个的想法？

- user1032369

Answer 1

你可以使用函数jsonb_array_elements(jsonb)来选择 JSON 数组的所有元素：

select jsonb_array_elements(questions->'data'->'list') elem
from assignments
where id='81asd6230-126d-4bc8-9745-c4333338115c'

                              elem
-----------------------------------------------------------------
 {"id": "2ac5bf6f-bc4a-49e8-8f9f-bc518a839981", "type": "type1"}
 {"id": "d15ac090-11ce-4c0c-a05d-d4238f01e8b0", "type": "type3"}
 {"id": "b98958fa-87c4-4dcc-aa84-beaf2b32c5c0", "type": "type1"}
 {"id": "854f4d2a-f37c-42cb-9a1f-17a15454a314", "type": "type2"}
 {"id": "555816da-4547-4a82-9e7e-1e92515bd82b", "type": "type2"}
 {"id": "0f7f4ced-61c2-45da-b15c-0e12058f66a7", "type": "type4"}
(6 rows)

如果想选择具有特定id的元素，请使用上述查询：

select elem
from (
    select jsonb_array_elements(questions->'data'->'list') elem
    from assignments
    where id='81asd6230-126d-4bc8-9745-c4333338115c'
    ) sub
where elem->>'id' = '854f4d2a-f37c-42cb-9a1f-17a15454a314'

                              elem
-----------------------------------------------------------------
 {"id": "854f4d2a-f37c-42cb-9a1f-17a15454a314", "type": "type2"}
(1 row)

- klin

---

很棒，这个可以用，如果我想在类似的情况下进行更新，该怎么做呢？比如我有一个问题的ID，并想要更新它的类型属性。 - user1032369

在Postgres 9.4中没有修改jsonb元素的功能。您可以整体更新jsonb值。请参见相关答案：如何修改新的PostgreSQL JSON数据类型内部的字段？和在PostgreSQL 9.4中更新特定的JSON数组元素。 - klin

在 Postgres 9.5 中，您将能够使用函数 jsonb_set()。 - klin