包含某些内容的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）将非常糟糕。我建议您将数据标准化到表中。