sql - Postgres 查询包含某些内容的 JSON 数组

Question

Postgres 有这种 JSON 数据类型，我想知道如何查询 JSON 数组中的数据？我正在使用 Postgres 9.3.1

我已经插入到具有这两个字段名称的 PUBLISHER 表中:字符串和数据:json

INSERT INTO PUBLISHER (name, data) VALUES ('PUB1', [{"code":"PA1","author":"James","type":"Novel"},{"code":"PA2","author":"John","type":"Science"}] 

INSERT INTO PUBLISHER (name, data) VALUES ('PUB2', [{"code":"PA1","author":"Dickens","type":"Novel"},{"code":"PA2","author":"Tom","type":"Comic"}] 

我想查询并列出“小说”类型的作者。在这种情况下，输出应该是“James”和“Tom”。

类似这样的查询:

select name, authorfromdata from publisher where data->type is "Novel"

Answer 1

您可以使用 json_array_elements 函数从数组生成 SETOF json:

SELECT name, json_array_elements(data) AS author
FROM publisher

有了它，您可以将它用作子查询，这样您就可以过滤您想要的内容，例如:

SELECT DISTINCT author->>'author'
FROM (
    SELECT name, json_array_elements(data) AS author
    FROM publisher
) t
WHERE t.author->>'type' = 'Novel';

请注意，如果您在该表中有很多行，则此类查询的性能(至少对于当前版本 9.3)将非常糟糕。我建议您将数据规范化为表格。